Re: Question on Extents

From: Peter Sharman <psharman_at_us.oracle.com>
Date: Mon, 01 Mar 1999 11:10:32 -0800
Message-ID: <36DAE628.29064055@us.oracle.com>

Jim

I couldn't let this one go past without comment - see below.

Jim Leavitt wrote:

> Oracle explained that they were not so concerned about the number of
> extents, and in fact recommended that next extent size could be set to some
> small value (say 16K) and that extent size used for every table in the
> tablespace. Then if an extent was freed in one table it became available for
> all tables (because they all had the same next extent size).

No, no, no, no, no (is that enough no's?). Whoever told you this has never worked with big tables. The idea is sound but needs further work. Sort your tables into a small group of sizing clauses (small, medium, big and humongous, where what is small etc. is ite dependent). Then put similar size objects in the same tablespace (or multiples thereof to spread the I/O over spindles). Put the same initial and next values on each tablespace. A table that is 50 Gb in size should never have a next extent size of 16K.

> You would then
> set the max extents to unlimited for all tables in the tablespace. Further:
> one should not be concerned about hundreds of extents, but rather thousands!
> I'm sure all this applied to 7 and above.

Again, not something I'd like to see. It's better to have some level of control, rather than possibly letting rogue processes blow out your space. In fact, we have some clients who did something like this, and they now have a problem with the time it takes to drop a table that is in over 130,000 extents being too long for the window they have available to do it in.

> I haven't tried this, but it's interesting. I was originally told that
> anything over 30 extents needed to be cared for. I was told by someone in
> OWWS that creating tablespaces and tables with unlimited extents was not
> only a good idea, but was actually encouraged.

Not by anyone I know!

> Jim Leavitt

Regards

Pete

Peter Sharman                              Email: psharman_at_us.oracle.com
WISE Course Development Manager            Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education               (650)607 0109 (local)

San Francisco

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Mon Mar 01 1999 - 13:10:32 CST