Re: db file sequential read wait

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Thu, 13 Nov 2003 02:45:18 GMT
Message-ID: <3FB2F037.70600@nospam_netscape.net>

Bosco Ng wrote:

> mngong_at_yahoo.com (michael ngong) wrote in message news:<ecf365d5.0311120824.271dfe25_at_posting.google.com>...
> 

>>boscoklng_at_hotmail.com (Bosco Ng) wrote in message news:<5b606863.0311120003.41cda004_at_posting.google.com>...
>>
>>>Hi, I am trying to tune a system that has the following top 5 wait
>>>events in statspack report (9.2.0.3 on AIX, 1G buffer cache, 120M
>>>shared pool)
>>>
>>>Top 5 Timed Events
>>>~~~~~~~~~~~~~~~~~~
>>>% Total
>>>Event Waits Time (s)
>>>Ela Time
>>>-------------------------------------------- ------------ -----------
>>>--------
>>>db file sequential read 1,388,390 9,767
>>> 50.21
>>>CPU time 8,186
>>> 42.08
>>>db file parallel write 17,703 1,195
>>> 6.14
>>>latch free 37,832 116
>>> .60
>>>log file sync 1,661 56
>>> .29
>>>
>>>And what's making mad is that every insert / update / delete to some
>>>particular tables (single row / few rows update/insert/delete they
>>>are) has a big difference between CPU time and Elapsed time:
>>>(timed_statstics turned on):
>>>
>>> CPU Elapsd
>>> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
>>>--------------- ------------ -------------- ------ -------- ---------
>>> 7,532,522 134,111 56.2 4.4 180.03 807.96
>>>Module: SQL*Plus
>>>INSERT INTO TABLE_A ( RDG_DATE, XXXXXXXXX,
>>> INVALID_FLAG, VALIDATION_BY_PASS, XXXX,
>>> CAPTURE_DATETIME, UPDATE_DATETIME, CREATED_BY,
>>>
>>> 6,531,957 48,399 135.0 3.8 122.03 1111.75
>>>Module: SQL*Plus
>>>DELETE FROM Table_B WHERE SID IN (
>>> SELECT bs.SID FROM Table_C bs, Table_D sa
>>> WHERE bs.Svc_Agmt_SID = sa.SID AND bs.Bill_SID IS NUL
>>>L AND sa.Acct_Num = :b1 )
>>>
>>>
>>>These 2 are just examples. Can anyone suggest what direction should I
>>>look into?
>>>
>>>Thanks a lot
>>>Bosco
>>
>>
>>Did you analyze the table and is the table properly indexed
>>
>>Michael Tubuo Ngong

> 
> 
> 
> Yes.  No doubt about this.

First of all, you might want to work towards reducing the number of buffer gets required by the above SQL statements. Things you can consider are making your indexes more selective, possibly by using composite indexes (e.g. the delete statement above can make use of a composite index on (sa.Acct_Num, sa.SID) and one on (bs.Bill_SID, bs.Svc_Agmt_SID, bs.SID)), rather than an index on each individual column. Better yet, have all the columns you're selecting contained in the index as well so you don't need to visit the table at all. You can also check to see if the indexes are fragmented, and if so, rebuild the indexes to minimize the number of blocks you need to fetch for each index range scan.

Given that you have a 1GB buffer cache, for the SQL statements that you listed with high # logical I/O's, did you get a high buffer cache hit ratio? If yes, then I would look at whether reading from the buffer cache was taking longer than it should, for example, do you have enough physical memory to avoid page faults, are you running other processes on the same machine that would consume a fair amount of memory, is the buffer cache using pinned shared memory on AIX etc. If buffer cache hit ratio is not reasonably high, then I would look at the File I/O, Tablespace I/O, and Segment I/O sections of the statspack report, figure out the hot spots, and try to place those datafiles on faster devices.

I also agree with previous suggestions that you should look at particular sessions using extended SQL trace and figure out exactly where the elapsed time is spent on each query.

Good luck.

• Dave

Received on Wed Nov 12 2003 - 20:45:18 CST