What does the 'etex' package do, exactly?
Update
Recent LaTeX kernels have incorporated the greater part of etex, so it's quite unlikely that one needs to load it nowadays.
Original answer
TeX, as designed by Knuth, has various registers addressable with an eight bit number (that is, from 0 to 255). Registers are of type
\count\dimen\skip\muskip\toks\insert\box
Let's consider the \dimen registers, for the others the allocation mechanism is similar. Each register is addressable by its number, for instance
\dimen34=42pt
\kern\dimen34
are legal example of setting a \dimen register or retrieving its value. However, calling registers by number is a problem, because packages couldn't cooperate with each other. So all formats provide an allocation mechanism: one says
\newdimen\foo
and TeX sets up things so that \foo is the same as calling \dimen<n> where <n> represents a number that we don't need to know. Note that \newdimen is not a documented LaTeX command, but it is documented in source2e and is very useful when writing packages.
TeX/LaTeX keeps track of the most recent allocated number for each register type; since the maximum number is 255, when a \newdimen command hits the limit, the dreaded
No room for a new \dimen
error message appears. There is a slight complication because allocation of \insert registers starts from 254 down and each \newinsert also allocates the \count, \dimen, \skip and \box registers with the same number, so the upper limit is usually something less than 255, but it's not the main problem.
In case we hit the limit, there is nothing to do: we are forced to load less packages or do nasty tricks that will almost certainly bite us later on.
In order to solve this problem that became apparent several years ago when PicTeX was released, a different implementation of TeX was prepared that defines 32768 registers of each type. This is part of e-TeX, that is incorporated in pdftex, the engine used when we run LaTeX on a document.
However, the kernel of LaTeX has not been modified, because it's basically frozen apart from bug fixes. Changing the allocation mechanism might break documents or packages that have been written under the assumption that only 256 registers of each type are available.
The etex package modifies the allocation mechanism; when \newdimen would issue the No room for a new \dimen error message, it insteads jumps beyond 255 and allocates the register number 256 and goes forward from there.
Here is a simulation, where we assume that \newdimen\foo allocates the last available eight bit slot and \newdimen\baz needs to go up to fifteen bits; in the log file you'd find
\foo=\dimen233
Normal \dimen register pool exhausted, switching to extended pool.
\baz=\dimen256
provided you have \usepackage{etex} in your preamble.
Why 233 is the last? Because LaTeX allocates \insert classes from 254 to 234; they are connected with marginal notes, footnotes, figures and tables.
classic TeX has 256 registers (eg count and dimen registers as allocated by \newcounter and \newlength in LaTeX.
For some years LaTeX formats have used the extended etex (or pdf(e)tex engines rather than classic TeX, so actually there are 32768 registers available. However for compatibility reasons (or stubbornness, or apathy, depending on your point of view) the default latex macros that allocate a name to a internal register number have not been updated so still only allow numbers 0-255. So if you load etex package (or use lualatex and xelatex formats as currently constructed, which use similarly extended allocation) the macros are changed to use a different range.
Note that etex is active by default, you can use \numexpr or a count register such as \count1000, it is just that \newcount does not "know" that you may do that, so does not allocate any more than 255.
UPDATE
LaTeX releases from 2015 onwards do detect if they are running on etex (or xetex or luatex) and use the full extended register range by default.
Thus the etex package should not normally be used with new documents.
Run texdoc etex and you'll get the documentation of eTeX. Two of the nice new features are \dimexpr and \numexpr. Instead of 256 available counters, dimensions, etc. there are now 2^{15}.
ε-TEX increases the number of TEX’s count, dimen, skip, muskip, box, and token registers from 256 to 32768.
eTeX is the default engine for all programs except of the original TeX. To activate the eTeX extensions one has to include \usepackage{etex}