| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: indexes reserve too much extents
Well,
I guess i have to say thanks to all of you who tried to supply me with an answer to my question, but it didn't really help me any further.
I see a couple of people are questionning about the number of indices on the
table :
Let's say the application is somewhere in the middle of OLTP and a
warehouse.
The table I was talking about, the one with the 30 indices, has about
5,600,000 records at this time. Another 20,000 records are added each day.
I have 21 foreign keys. Knowing that Oracle doesn't put indices on the FK's,
we did it ourselves.
Then we have 1 PK and 8 indices for perfomance reasons.
21 FK + 1 PK + 8 extra = 30
I could drop the FK indices, but that would really make our referential
integrity suffer ...
Why we created tablespaces with this odd extent size ? Well, that's kind of a long story ...
We have always been using Sybase Sql Anywhere and MS Sql Server 2K for our
customers. Since we are having bigger customers, and larger databases, our
database is suffering from performance. Not when inserting data, but when
issueing queries to the database. Since Oracle handles bigger databases much
better, we started using Oracle 8i.
Let's say it not exactly an Oracle DBA, and I learned oracle by myself, with
just a book ...
The database structure is the same in Sybase Sql Anywhere, Sql Server and
Oracle.
When I started the conversion of the database, I had only 2 user defined
tablespaces :
DATA & INDEX. Both were dictionnary managed tablespaces.
That didn't gave us the best performance since we have multiple tables where
a lot of inserts 10,000 -> 30,000 occur.
I have been looking around a bit on the internet, and found multiple site
where DBA's state that a segment should NEVER have more then 1024 extents.
Also see Oracle PDF doc :
http://otn.oracle.com/deploy/availability/pdf/9i_reorg_comp.pdf
page 4
http://otn.oracle.com/deploy/availability/pdf/defrag.pdf
page 3 & 4
Then I went splitting up my tables into three categories :
- static data - dynamic data, with few inserts - dynamic data with a lot of inserts
After doing a bit of a bit of math, I found that the extent sizes I'm using (128K, 4M, 128M) were very well suited for our database. If I would be using automatic allocation, I would eventually also end up with large extent sizes because of the number of inserts ...
Now, back to my problem with the size allocated to the indices.
When I take one particular index, which has 65,536 blocks (or 8 extents), I
see :
extent size = 64M / 8192 blocks
USED_BLOCKS = 65536
My question :
Why have 8 extents been assigned, while 3 would have done the job ?
Now USED_BLOCKS = BTREE_SPACE * 3
Is there a way of freeing the not used extents ?
How is BTREE_SPACE related to USED_BLOCKS ? Will I ever have a situation
where BTREE_SPACE = USED_BLOCKS ?
-- Greetz, Frederic Hoornaert (Plz remove .nospam from my email address when replying to this message) "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:4096ca6e$0$25012$afc38c87_at_news.optusnet.com.au...Received on Tue May 04 2004 - 02:54:53 CDT
> Frederic Hoornaert wrote:
> > Hello,
> >
> [snip]
>
> As others have said, 30 indexes for a table is getting a bit silly
> (sounds like SAP to me).
>
> > When I take a close look at the data of INDEX_STATS after a VALIDATE
> > INDEX <index_name>, i see that
> > 16 indices have each 1 extent of 64 Mb
>
> Which sort of suggests they were built into the wrong tablespace.
> Locally Managed Tablespace will not sweat with a couple of hundred
> extents, so you might be better off rebuilding these indexes into the 2M
> tablespace (or even the 64K tablespace -why you picked those extent
> sizes is a puzzle. Autoallocated tablespace uses 64K, 1M, 8M, 64M and
> 256M extents. If it's good enough for Oracle, I'd suggest you should
> have done likewise).
>
> > 1 index has 5 extents = ( 64Mb * 5 ) = 320 Mb
> > 13 indices have each 8 extents = (64 Mb * 8) = 512 Mb.
>
> Again, all very low numbers of extents, and all could therefore be
> rebuilt into the smaller extent-sized tablespace without drama. With
> smaller 'granularity' of extent sizes, you'll probably save some space.
>
> > For the indices that all use 8 extents, something seems to be wrong
> > because for these indices I see that they all use between 50 and 150 Mb
> > (value of index_stats.used_space)
> >
> > Largest index in tablespace :
> > extent size : 64 Mb / (8192 byte / block) => 8192 blocks
> > used blocks : 65536 / (8192 blocks / extent) => 8 extents
> > btree_space : 177.058.656 byte
> > 177.058.656 / (8192*8192) = 2,6
> >
> > Why are these indexes using 8 extents where is space enough in 3 extents
?
> > For this table, I have 8.388.608.000 bytes assigned where only
> > 1.635.157.943 bytes are needed.
> >
> > This is the situation after the creation of an empty database, and data
> > import with SQL Loader. No manual manipulation had been done yet.
>
> For the particular weirdness of multi-extent allocation when only a few
> would have done, I might suspect that parallelism has been involved
> somewhere along the line. If a serial rebuild reclaims the space, that
> might answer the question.
>
> But that's really not your real problem. Too many indexes, and
> tablespaces with slightly odd uniform extent sizes are.
>
> Regards
> HJR
 |
 |