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

oh yeah - it is indeed a package - does my head in just reading through it. 
There is tons of other sql generated when this report runs - but the sql I sent 
earlier is the mother...the queen bee if you will of statements in the 300mb 
trace file. 

I've not actually checked how it behaves without the calls to the package - Are 
you thinking recursive issues? Or have tried to much to read into the package 
code.




________________________________
From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
To: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
Cc: Oracle-L@xxxxxxxxxxxxx
Sent: Tue, June 15, 2010 1:15:34 AM
Subject: Re: db file sequential/scattered read - physical or logical io or  
both?


Hey
 
is PKG_ADMIN_ACTIVITY_DETAIL a package with cursors and other helpfully 
confusing stuff in it :) . Or put another way, does the exact same select 
without the calls to the package behave nicely? 
 
Niall


On Mon, Jun 14, 2010 at 8:33 PM, Oracle Dba Wannabe 
<oracledbawannabe@xxxxxxxxx> wrote:


>
>Hi Niall,
>Well you did ask for it - and I've attached it :). Looking through the plan it 
>appears to be, at first sight, an issue will a full table scan and partition 
>range scan through all partitions - no elimination takes place here - this is 
>also where the bulk of the rows appear to come from.
>
>I hear you on the less reads per row - I've seen tremendous benefit by tuning 
>a query that already executes in about a second. Why would I focus on a query 
>with a response time like that. The issue was that it only had to return one 
>row, but in doing so touched/read thousands of blocks. The other issue was the 
>query was executed over and over - also thousands of times within 30 minute 
>intervals. It was a business critical function - there was no way to avoid 
>this (reduce its execution). Eventually got it down to a few i/os to obtain 
>that same one row. The result - more predictable performance in the business 
>function and a massive drop in overall cpu utilization...yeah well, I thought 
>I'd just mention that.
>
>
>
>
________________________________
 From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
>To: oracledbawannabe@xxxxxxxxx
>Cc: Oracle-L@xxxxxxxxxxxxx
>Sent: Mon, June 14, 2010 9:44:22 PM
>
>Subject: Re: db file sequential/scattered read - physical or logical io or 
>both?
> 
>
>
>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 ...
>>
>


-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: