Re: Oracle Performance -- Possible Disk Bottleneck

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 9 Jun 2007 07:44:23 +0100
Message-ID: <WIydnbZ4KI2j0ffbnZ2dnUVZ8smonZ2d@bt.com>

<beth.stover_at_gmail.com> wrote in message news:1181259552.918564.300180_at_q66g2000hsg.googlegroups.com...
> I'm digging into the performance article tonight. Thanks so much for
> the feedback.
>
> Here is the statspack. I hate posting something so large, so I hope
> it's ok. If someone is kind enough to take a peak, then it is
> appreciated.
>
>>
>> - Show quoted text -
>
>

2,700 IOPS is not possible for 6 disks. Even with the fastest drives I wouldn't expect to see steady state random I/Os averaging much better than 120 IOPS.

However, the 2,710 /sec in Oracle's stats are "blocks read", not "read requests", so the number of I/O requests could have been significantly less. Note that you had 166,893 multiblock read requests (from Oracle's perspective).

physical reads                   4873242     10047.92    47543.82 
2710.37
table scan blocks gotten         2769428      5710.16   27018.81

1540.28

Playing with numbers (ballpark figures)

Assume your 4,873242 blocks was

        3,742,459 single block requests plus
        166,893 multiblock requests

db file sequential read                3742459  216409           .06
db file scattered read                  166893    13092           .08

Your average multiblock request size was

(4,873,242 - 3,742,459)/166,893 = 6.59 blocks.

This calculation is fairly consistent with the prefetched blocks counts. (Most prefetched blocks tend to be from tablescans).

prefetched blocks                 958405      1976.09     9350.29

533.04

Another calculation

Total read requests = 3,742,459 + 166,893 = 3,909,352 Requests per second = 2,175

        Still too high for your 6 disks, so some of your reads
        were probably coming from a filesystem or disk cache

But the critical indicator, really, is the average read time:

6 centiseconds for a single block
8 centiseconds for a multiblock

Your disks should typically be showing less than 0.8 centisecond for a single block, and less than1.4 for a multiblock.

The disk times are consistent with the buffer busy wait times - if two people want to read the same block off disc (and this can happen quite easily with concurrent tablescans) one ends up in a buffer busy wait for the other to complete the read.

buffer busy waits                        41906       4122            .1

The BBW times you have agree with the 'slow disks' - but suggest that there is a variation in the problem, i.e. peaks of activity which really slow things down from time to time.

Look for:

queries with inappropriate tablescans

queries using low-precision indexes that visit large numbers of blocks in tables.

queries against tables with lots of chained rows as these may be contributing extra physical reads.

spare memory to increase the size of your buffer cache in case this helps to reduce the number of index block re-reads of indexes in the OASIS_INDEX tablespace.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Sat Jun 09 2007 - 01:44:23 CDT