Re: db file sequential/scattered read - physical or logical io or both?
- From: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Mon, 14 Jun 2010 13:22:41 -0700 (PDT)
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:
