From: Bryan Lenihan <bryanle@lenisoft.com>
Subject: Re: Extents: Less or More?
Date: 1998/02/02
Message-ID: <6b3n28$nsi@bgtnsc02.worldnet.att.net>#1/1
Content-Transfer-Encoding: 7bit
References: <34d4ded2.0@news1.ibm.net>
To: "Brian S. Hummer" <hummerb@usa.net>
Content-Type: text/plain; charset=us-ascii
Organization: AT&T WorldNet Services
Mime-Version: 1.0
Newsgroups: comp.databases.oracle.server



Brian,

I have detemend in my testing that the answer to your questions is dependant on
the requests that you are asking of the database.  I am one who does like to
compress the extents.  My database does a large number of table scans, and I
have both the database and the drive arrays tuned.  The benifit of compressing
your extents is the fact that all the data is contigous segments, and the I/O
can be read accordingly.   This is also dependant on the drive configuration.
For example if you are running on a RAID 1/0 - then the contiguous segment will
be helpful.  If you are using mirrored disked and have to manually strip the
data accross several disk for I/O spreading, then obviously you will need to
create more than 1 extents.

The question of the number of extents that are bad - I suggest to you to take a
base line time on your databases. As the extents grow, monitor the query times
(using the base lines) and determine if your database is needing of a
compression.  Once again it determines what type of I/O you are using and the
transaction types.

The same goes with indexes - you have to determine if the I/O is going to bounce
around anyways, or are you going to be reading in sequential manners.

The multiblock_read_count (or close to that) is used in conjuction with the
rules of your contigousness.

I have seen performance gains compressing extents on my system.

I hope that I was clear on this.

Thanks,
Bryan Lenihan

Brian S. Hummer wrote:

> I'm confused about managing the number of extents for an object. The rule of
> thumb I've always believed that the fewer the number of extents (preferrably
> one) an object has the better. But recently, while taking the DBA course at
> an Oracle Education center, I was lead to believe that the number of extents
> for an object doesn't really matter.
>
> I can see the logic for this as most I/O is random and blocks are read and
> written, not extents. I've even heard that having too few extents can be a
> problem for Parallel Query. Is this true? The only real benefit I can see in
> having fewer extents is that there are more contiguous blocks which would
> benefit I/O for full table scans.
>
> I could spend a great deal of time resizing segments to keep their number
> down, but I could certainly spend the time on other tasks and still squeeze
> in that 60-hour week. Any suggestions?
>
> Thanks,
> Brian
>
> Ps. Running Oracle 7.3.2 (DEC OpenVMS) and Oracle 8.0.3 (Windows NT)