Re: db file sequential/scattered read - physical or logical io or both?
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracledbawannabe@xxxxxxxxx
- Date: Mon, 14 Jun 2010 17:44:22 +0100
A few comments. First it looks like your tkprof is suggesting that you read
43m rows to return 700k or so. I'd therefore be quite interested in seeing
what the execution plan was, 50 reads per row isn't horrible, but imagine
what might happen if it could be 1 read per row or fewer.
Niall Litchfield
On Jun 14, 2010 1:41 PM, "Oracle Dba Wannabe" <oracledbawannabe@xxxxxxxxx>
wrote:
Hi All,
Thanks for the responses. The report itself is actually a problem - to
resolve it I understand I need to address the number of i./o's issued by it.
That said, I posed this question because I just wanted to try and understand
how oracle issues reads. The reason the naming of the wait events confused
me - was I've also understood them to mean physical reads. What didn't make
sense to me was why after the report completed and I submitted it again
there were still wait events for reads issued in awr - I would have thought
the data was cached. I had traced each run of the session, but had just not
looked into the trace file yet (my mistake) and was only looking at the awr
file. Looking at the trace I see the following:
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 90966 2752.59 2690.45 0 43989168 0
727721
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 90968 2752.59 2690.46 0 43989168 0
727721
Which appears to indicate zero disk reads. However AWR, shows the following:
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class db file
sequential read 1,191,745 1,368 1 45.3 User I/O
There's nothing else running on this system other than this SQL (it was a
weekend afterall). So although my trace file shows no physical read events -
how do I account for the 1.1 million wait events - The load profile on awr
looks like this:
Per Second Per Transaction
Logical reads: 15,445.20
19,592.96 Block changes: 38.26
48.54 Physical reads: 658.30
835.08
So are the db file sequential reads a result of waits from reads at the
storage cache? the file system cache (have filemin_cache at 3% and
filemax_cache 10% total memory =120GB), the oracle buffer cache or all
three? Or is it just the first 2 and does not include the buffer cache
requests as a call for data in the buffer cache does not translate to a read
wait event?
A bit more detail on this system: The server is a 2 Node RAC (10.2.0.4) on
HP-UX 11.31, Super Dome, the storage is XP 24k, the db cache size is approx
60GB on each node.
Thanks once again.
------------------------------
*From:* Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
*To:* oracledbawannabe@xxxxxxxxx
*Cc:* oracle-l@xxxxxxxxxxxxx
*Sent:* Mon, June 14, 2010 11:23:24 AM
*Subject:* Re: db file sequential/scattered read - physical or logical io or
both?
When you run the report via SQL*Plus with autotrace on, (or look at the
session stats) what is the ...
Other related posts:
