Re: Is there a performance limit of extents?

AHhhhhh - The great 'do mnny extents impact performance issue' appears once again.

My 2 cents:

I set up a db and ran many many many inserts/updates/deletes/table scans, range scans, rowid lookup, fast full index scans, table joins, etc... using a variety of extent sizes. 1,100,250,500,1000,5000,10000 on both tables and indexes. (I went through all the hoops to make sure that i was not seeing row/block fragmentation, caching, etc.. but truely extent differences)

Observations:

• Over 500 extents and you will start to se a slight performance hit. 1-2%
• Over 1000 extents and the recursive SQL starts to take a bigger bite and you see 3-6% hit
• 5000 - 10000 and you get a 15-40% hit.
• over 10000 and you need to attend DBA 101 school again and you can get anything from 50-200% performance hit
• The impact of many extents on indexes is more significant than on tables.
• All these times are of course subject to cahnge based on your DB and activity against it.

Things to help minimize this:
- Use SAF and uniform extent sizes. This can be done procedurally, but
8i has a feature to help enforce this.
- Use locally managed table spaces. This way the system tablespace is
not a bottle next. ( i have not tested this impact of this but according to Oracle it should be good)

Milsapp has a very good whitepaper on this topic

Conclusions
- Yes many extents can impact performance, but spending your time tuning
your code, memory and disk I/O, rollbacks, sorts, etc.. will be a lot more productive.
- Rebuild your indexes regulary. Not for extent issues, but for
leveling.
- Reorgs of tables should be the exception not the rule. Under 8i, there
is a nice new command like index rebuild for tables which makes this less risky and easier, but still......

Hope this helps...........

--
Doug Coan
Oracle Certified Professional DBA


Sent via Deja.com http://www.deja.com/
Before you buy.