Re: Oracle Performance -- Possible Disk Bottleneck

On Jun 8, 11:44 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <beth.sto..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Thanks so much to everyone who replied. It seems apparant that we do have a disk bottleneck here. The question is why are there so many reads on the database.

I disabled AV scanning, and this didn't improve things -- reads are still way high. They peak at 8000 Reads/sec during busy times.

I went through the Metalink article, and we are beginning to identify some "hotspots." The DBA was aware of a problem with a particular table, and this showed up at the top of the list, so we'll start looking there.

"Choose" mode will be implemented in new version of the application which will be installed later this summer. Optimization modes have caused problems before, so hopefully this will fix those issues.

There are plans to upgrade to 10g in the not-so-near future. The vendor is testing that, and we're doing testing of our own.

We're also looking at giving the LUN more spindles. We'd like to avoid doing this if possible because of the cost.

Thanks again for your help. I've been very impressed with this group and its willingness to help out. Received on Thu Jun 21 2007 - 11:22:37 CDT