Re: db file sequential/scattered read - physical or logical io or both?
- From: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Mon, 14 Jun 2010 05:36:54 -0700 (PDT)
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 physical reads & consistant gets
numbers? Likely the filesystem cache is providing the "lift" in
performance.
On Sun, Jun 13, 2010 at 3:31 PM, Oracle Dba Wannabe
<oracledbawannabe@xxxxxxxxx> wrote:
> So I have an idle system, where I run a sql report. The report takes an hour
> to run. I look at the awr report (30 min interval), and see the io wait
> events for sequential and scattered reads. The first has an avg wait time of
> 7ms the second 10ms. These waits as I understand it are physical io requests
> - correct? The p1 and p2 parameters point to file and block numbers so I
> guess that makes sense. Anyway I rerun the same report, look at the new awr
> and now see the same wait events, only with much smaller wait times. Which
> means data was read from cache - if that's the case why are the same wait
> events issued? it seems a bit confusing that way.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
