Re: Extents: Less or More?

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)
Received on Mon Feb 02 1998 - 00:00:00 CST