Re: DBA_HIST_SQLSTAT

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 20 Nov 2012 14:06:26 -0800 (PST)
Message-ID: <1353449186.93320.YahooMailNeo_at_web121603.mail.ne1.yahoo.com>

 
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_at_parallon.net" <Christopher.Taylor2_at_parallon.net> To: Jed_Walker_at_cable.comcast.com; andy_at_oracledepot.com; oratune_at_yahoo.com Cc: oracle-l_at_freelists.org
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_at_cable.comcast.com] Sent: Tuesday, November 20, 2012 2:53 PM To: andy_at_oracledepot.com; oratune_at_yahoo.com Cc: Taylor Christopher - Nashville; oracle-l_at_freelists.org 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andy Klock Sent: Tuesday, November 20, 2012 12:25 PM To: oratune_at_yahoo.com
Cc: Christopher.Taylor2_at_parallon.net; oracle-l_at_freelists.org Subject: Re: DBA_HIST_SQLSTAT

On Tue, Nov 20, 2012 at 12:24 PM, David Fitzjarrell <oratune_at_yahoo.com>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.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 20 2012 - 23:06:26 CET