Re: DBA_HIST_SQLSTAT
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>, "Jed_Walker@xxxxxxxxxxxxxxxxx" <Jed_Walker@xxxxxxxxxxxxxxxxx>, "andy@xxxxxxxxxxxxxxx" <andy@xxxxxxxxxxxxxxx>
- Date: Tue, 20 Nov 2012 14:06:26 -0800 (PST)
Different literals will generate a different sql_id; queries using bind
variables can generate multiple child cursors when the bind data changes.
David Fitzjarrell
________________________________
From: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>
To: Jed_Walker@xxxxxxxxxxxxxxxxx; andy@xxxxxxxxxxxxxxx; oratune@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, November 20, 2012 1:56 PM
Subject: RE: DBA_HIST_SQLSTAT
I believe child cursors are different versions of the same statement - I
believe this comes into play with literals.
So that if a SQL statement varies by a literal in the Predicate for example, I
believe it becomes a new child - since its the not exact same SQL but is
effectively the same. I may be mistaken on that (or my wording/understanding
of it).
Chris
-----Original Message-----
From: Walker, Jed S [mailto:Jed_Walker@xxxxxxxxxxxxxxxxx]
Sent: Tuesday, November 20, 2012 2:53 PM
To: andy@xxxxxxxxxxxxxxx; oratune@xxxxxxxxx
Cc: Taylor Christopher - Nashville; oracle-l@xxxxxxxxxxxxx
Subject: RE: DBA_HIST_SQLSTAT
I've been looking at this now and am wondering about VERSION_COUNT. The
definition is "Number of children associated with the cursor". What exactly is
meant by children?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Andy Klock
Sent: Tuesday, November 20, 2012 12:25 PM
To: oratune@xxxxxxxxx
Cc: Christopher.Taylor2@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: DBA_HIST_SQLSTAT
On Tue, Nov 20, 2012 at 12:24 PM, David Fitzjarrell <oratune@xxxxxxxxx>wrote:
> They don't, actually. The executions_total is the total executions
> since the database was started, the elapsed_time_total is the total since the
> db
> was started and the deltas are for each snapshot and don't roll up. I
> came into a shop where a script was running to generate execution
> time reports for long-running queries but it didn't take into account
> that the deltas don't roll up. I rewrote it to provide total
> execution times across snapshots for a given sql_id:
>
My experience is hit and miss when making calculations with executions_total
and executions_delta. The reason being these numbers aren't cumulative to the
start of the instance but rather since the time they've been in the library
cache. (same as v$sqlstats). So, sometimes for long running queries (that span
across multiple snapshots) it's possible that they will never show an execution
in AWR.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
