Hi Kellyn,
I did actually have a 10046 trace enabled for this particular run -
there are no sequential or scattered reads issued in the raw trace
file - at all. I'll explain what I did - something I left out
earlier (my bad):
1a. Bounce both RAC instances
1b. Run the report on one node of RAC
1c. Run the same report on the second node of RAC
1d. Yes, run the same report again on the second node of RAC - the
tkprof output shown below is a result of that.
(did all that node changing stuff above, to see what kind of events
are issued in a RAC environment)
Why does my AWR show db file sequential read waits - when the raw
trace file, shows no i.o related wait events (disk below in the
tkprof output also shows zero) - Well I thought this pretty much was
an idle system - I thought I was alone turns out I'm not, I had a
look down the report and came across the following:
In the SQL Ordered by Gets:
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed
Time (s) SQL Id SQL Module SQL Text
14,518,479 0 63.74 285.56 1758.08 6mcpb06rctk0x DBMS_SCHEDULER
call dbms_space.auto_space_adv...
In the SQL Ordered by Reads
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed
Time (s) SQL Id SQL Module SQL Text
1,094,006 0 99.99 285.56 1758.08 6mcpb06rctk0x DBMS_SCHEDULER call
dbms_space.auto_space_adv...
Somethings wrong with this picture isnt there?
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/
--
http://www.freelists.org/webpage/oracle-l