>
> Ari,
>
> I'm a non-systems person who knows just enough to be dangerous. Our
> department used table spaces so extensively and had such a need to be
> able to maintain our own records that the systems department set us up
> with our own user ID. They segregated our tables in such a way that we
> are in control of them along with a large set of table spaces. Over the
> past few years our company has seen high turnover in the systems
> department and information that I received in the past regarding extents
> and performance is being contradicted by our current DBA administrator.
>
>
> For the most part all of our tables are built with the following
> structure.
> PCTFREE 10 TABLESPACE TS_QTVLPT
> STORAGE ( INITIAL 96879K NEXT 9687K MINEXTENTS 1
> PCTINCREASE 0 MAXEXTENTS 121 )
>
> The PCTFREE is always 10, storage NEXT is always 10% of INITIAL,
> MINEXTENTS = 1, PCTINCREASE = 0, and MAXEXTENTS = 121. The indexes are
> built the same with the exception of the removal of MINEXTENTS 1.
>
> What I have been told in the past is that if a table goes into multiple
> extents, the performance suffers. The table should be rebuilt and
> defragmented. We were told to watch our tables and indexes and if they
> go into more than 2 extents to let the systems department know. We have
> some fairly extensive selects and inserts in our programs and some very
> large tables. It did seem that our programs would run more efficiently
> when rebuilt and no longer in extents. We currently have some tables in
> 13 extents and indexes in 23 extents. I have been told by our DBA
> administrator that extents do NOT affect performance and therefore he
> isn't willing to rebuild them. What is the right answer?
>
> If in fact the number of extents does affect performance, could you
> point me to some documentation so I can convince our DBA administrator
> of the fact.
>
> Thanks,
> Kathryn
> kcox@rgare.com
Extents does affect performance. The industry buzz is that anything more than
four will affect performance. This is documented in dozens of Oracle books,
including Oracle documentation itself. If you are on UNIX, you should have
received a "Performance Tuning for Oracle on UNIX" where this is mentioned.
Also get "Special Edition Using Oracle8" that explains on P.802-804 why
fragmentation and multiple extents are bad for performance.
For example, "Although fragmentation is usually considered primarily a space
issue, like all things in a database system, it has its effects on performance.
Whether directly or indirectly, extent fragmentation triggers unneccessary
dynamic extension, which you do not want occurring unnecessarily, from the
standpoint of performance. If, in the lifetime of a tablespace, segment
creation and dropping has created a swiss cheese effect, it detracts from
performance by necessitating I/O's from multiple extents....."
"...the PQO configuration becomes slower and slower because it was initially
configured based on load-balancing the extents. In addition, what was
initially a set of sequential reads from contiguous disk locations are now
groups of sequential reads from various (random) disk locations, requiring
more seek activity!"
This book can be found at a discount through my web page
(www.arikaplan.com), as I am a co-author.
Hope this helps. The best way to convince him (and yourselves) is to do timing
statistics for the large extent table, then defragment it and see the
difference. From your DBA's stand, it seems he/she may be unwilling to do
this.
Best of luck,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 160+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page