Bill
I think your confusion over the index scans is because David left out one key
word - "unnecessary". If you change his sentence to read " If your application
is doing a large percentage of unnecessary full table scans, you should be able
to get quite a considerable performance boost by doing some indexing.", it makes
sense.
Secondly, the multiple extents thing is one that you see every so often as a
performance problem, and it's not. Here's the detailed reasoning, direct from
the keyboard of Cary Millsap, the guy that wrote the OFA and heads up Oracle's
System Performance Group:
The issue of multiple extents in Oracle never was a problem. There are a couple
of theories on why people used to think they were a problem (including, e.g.,
that evidently multiple extents was a problem in the mainframe world, where a
lot of early Oracle adopters came from). The two hypotheses in which I believe
the most strongly are:
- On many databases, you can run export with compress=y and see a measurable
performance improvement in the resulting imported database. The serious flaw
with using this data point as a "proof" is that you can use compress=n and
achieve identical performance gains. The performance gains come not from
reduction in the number of extents in database segments, but from the block
packing and row fragmentation removal implicitly in any import (or any
delete/insert cycle, for that matter). This kind of error of misinterpretation
of experimental results is called multicollinearity. It's attribution of an
effect to the wrong cause because two linked causes were never properly
experimentally isolated.
- It is possible to create a test "proving" that multiple extents are bad for
performance, but to do so, you need to make several assumptions that generally
don't hold true in reality:
- Assume an operational dependence upon full-table scan access paths or
frequent use of DDL. For blocks read via direct block addressing, the number of
extents comprising a segment is totally irrelevant (such as any undo [rollback
segment] block read, any index block read, or any data block read executed
through an index or hashing function). And naturally, if you want to test the
performance of the drop DDL statement, it's going to be faster for a 1-extent
segment than for a 100-extent segment (simply because a 1-extent segment has
only 1 row in uet$, while a 100-extent segment occupies 100 times
as many uet$ rows in the data dictionary). Assuming operational dependence upon
full-scan access paths or repeated DDL execution is a poor analysis, because
repeated use of either of these techniques in an application is generally a sign
of an incompetently conceived application design. If this is the application's
problem, then the most efficient solution is not to put each of your segments
into one big extent, it's to fix the design of the application.
- Assume the use of pathologically many pathologically small extents. Okay, if
you full-scan a table with a hundred 2KB extents, you may notice a performance
difference relative to what would happen if you only had one extent. However, a
person who's this poor at executing a remedial space management discipline
deserves whatever performance plague he gets. Plague, however, isn't really the
right word. There'll be incremental strain on the system's average disk head
seek times (maybe a couple milliseconds per I/O at worst?), and incremental
strain on the shared pool where the extent map will be cached (the parts of the
cache which will need to be dedicated to storing uet$ rows).
- Assume the use of a single-user system. If there's no I/O queueing, then it's
possible that with a good microscope you'll be able to detect incremental
degradation in disk head seek times if a full-scanned table has multiple
extents. But probably not. Most textbook authors test on single-user systems.
- Assume that there's no O/S fragmentation. On UNIX filesystems, there's
filesystem fragmentation management that takes place below the Oracle database
software layer. Sometimes you'll find that a person bases a conclusion about
Oracle fragmentation on a test executed on a UNIX filesystem into which there's
been no investment into using rationally chosen UNIX extent sizes. So you'll
get a guy squawking about some result based on a UNIX filesystem implementation
in which "it's proven" that a single extent thing outperformed a multiple extent
thing, but underneath it all, the UNIX filesystem manager is swapping 2KB
extents all over the place, making the test irrelevant. (You can eliminate this
variable, by the way, by either investing into an adequate technician to
configure your filesystems to hold large files, or you can use raw devices.) I
don't know about NT filesystems, but if they're anything like a cross between
VAX/VMS and DOS, then they fragment themselves and require human intervention to
do diagnosis and repair. (Mind you, probably the only thing stupider than
crossing VAX/VMS with DOS would be those "Snackin' Cabbage Patch" dolls they had
last Christmas that ate all those kids' fingers and hair and stuff.)
I've just about unloaded everything I have on the topic. I can summarize the
key bits of understanding as follows:
- Multiple extents don't impact the performance of direct-access data AT ALL.
Since a principal goal of any good application designer is to maximize use of
direct access paths (indexes, hashes), and because Oracle Server itself does a
good job of seeking these paths for its internal operations (undo reads, most db
writes, etc.), multiple extents don't impact the performance of most
applications at all.
- Many people who believe multiple extents are bad believe so because they have
been influenced by a poorly designed experiment.
HTH.
Pete
Bill Buchan wrote:
> Hi David,
>
> Thanks for your reply. Unfortunately I'm a bit confused now! What's
> bothering me is:
>
> 1. How can indexing speed up full table scans? I thought that Oracle would
> only use the index to access data if accessing a small % of rows are to be
> fetched.
>
> 2. In the Storage Management document it says that the performance is not
> sensitive to the number of extents. If I have 3 tables A, B, C which all
> have 3 extents organised as ABCABCABC. Surely there will be more
> (expensive) disk head movement to read A than if it were a sequential read
> of a single extent ie. AAABBBCCC. Are the blocks not organised on disk as
> it appears logically in the tablespace - in this case three "groups" of
> blocks to be read?
>
> Sorry if I'm misunderstanding!
>
> Thanks,
>
> - Bill.
>
> David Sisk <davesisk_at_ipass.net> wrote in message
> news:UQqs3.801$tp2.630_at_news.ipass.net...
> > Hi Bill:
> >
> > Contrary to some beliefs, the number of extents in a segment is not
> > necessarily equivalent to the level of fragmentation of the segment. See
> > the article on the site below under Performance -> Storage.
> >
> > If your application is doing a large percentage of full table scans, you
> > should be able to get quite a considerable performance boost by doing some
> > indexing. (We're talking 5, 10, 20, sometimes even 100-200 times faster
> for
> > individual queries.) AND access by index if immune to table
> fragmentation
> > (if you define "fragmentation" as scattered empty space within a segment,
> > which, IMHO, is the correct definition).
> >
> > Good luck,
> >
> > --
> > David C. Sisk
> > Need tech info on Oracle? Visit The Unofficial ORACLE on NT site at
> > http://www.ipass.net/~davesisk/oont.htm
> > Like original music? Listen to song samples and buy a CD at
> > http://www.mp3.com/disparityofcult
> >
> >
> > Bill Buchan wrote in message <7osb59$ss0$1_at_phys-ma.sol.co.uk>...
> > >Hi,
> > >
> > >I have an Oracle 8.0.4 database on WinNT. The datafiles are all on
> RAID-5.
> > >Some of my segments have got seriously fragmented with dozens of extents
> > all
> > >over their tablespace. Is it still worth defragmenting these given that
> > the
> > >hardware will stripe this data all over the place anyway?
> > >
> > >I have a lot of queries that do full scans of the fragmented segments.
> > >
> > >Any advice would be much appreciated.
> > >
> > >Thanks,
> > >
> > >- Bill.
> > >
> > >
> > >
> >
> >
--
Regards
Pete
Received on Fri Aug 13 1999 - 11:16:00 CDT
