Re: db file sequential/scattered read - physical or logical io or both?
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
- Date: Mon, 14 Jun 2010 21:15:34 +0100
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:
