Re: db file sequential/scattered read - physical or logical io or both?
Date: Tue, 15 Jun 2010 07:09:24 +0200
Message-Id: <8B8E7B61-84FA-4C1F-9798-A149A5136DDB_at_gmail.com>
have you enabled tracing for the whole instance (or both) or just for your user of interrest?
you are never alone on an instance, especially in recent versions :-)
regards,
Martin
Am 14.06.2010 um 22:17 schrieb Oracle Dba Wannabe:
> 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_at_structureddata.org>
> To: oracledbawannabe_at_yahoo.com
> Cc: oracle-l_at_freelists.org
> 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_at_yahoo.com> 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
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 15 2010 - 00:09:24 CDT