Re: db file sequential/scattered read - physical or logical io or both?

  • From: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
  • To: Kellyn Pedersen <kjped1313@xxxxxxxxx>
  • Date: Mon, 14 Jun 2010 13:17:29 -0700 (PDT)

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?




________________________________
From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
To: Oracle-L@xxxxxxxxxxxxx; oracledbawannabe@xxxxxxxxx
Sent: Tue, June 15, 2010 12:46:16 AM
Subject: Re: db file sequential/scattered read - physical or logical io or  
both?


You should also think about tracing this and find out if the db file sequential 
read waits are on a dictionary object.  The waits could be a bit misleading on 
what it is it's actually *waiting* for... :)


Kellyn Pedersen
Sr. Database Administrator
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
 
"Go away before I replace you with a very small and efficient shell script..."

--- On Mon, 6/14/10, Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx> wrote:


>From: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
>Subject: Re: db file sequential/scattered read - physical or logical io or 
>both?
>To: Oracle-L@xxxxxxxxxxxxx
>Date: Monday, June 14, 2010, 6:36 AM
>
>
> >
>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: