Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Performance -- Possible Disk Bottleneck

Re: Oracle Performance -- Possible Disk Bottleneck

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Fri, 22 Jun 2007 02:59:51 GMT
Message-ID: <HWGei.7186$s57.5914@newsfe07.phx>


beth.stover_at_gmail.com wrote in
news:1182442957.517406.310760_at_g37g2000prf.googlegroups.com:

> 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.
> 
> 
> 

Start by reading the following URL:
http://www.perfvision.com/papers/Sampling.pdf You get get a copy of the Active Session History (ASH) code here: http://www.perfvision.com/ash.php

HTH & YMMV! Received on Thu Jun 21 2007 - 21:59:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US