RE: DBA_HIST_SQLSTAT
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <oratune@xxxxxxxxx>, <Jed_Walker@xxxxxxxxxxxxxxxxx>, <andy@xxxxxxxxxxxxxxx>
- Date: Wed, 21 Nov 2012 08:20:14 -0600
One thing that is frustrating my efforts to mine this the way I want is to
identify application SQL code that is practically the same, versus exactly the
same.
For example, I need both SQL statements that differ by literals and SQL
statements that differ by plans.
In DBA_HIST_SQLSTAT the PLAN_HASH_VALUE may or may not be the same for
different SQL_IDs that differ by literals, and the SQL_IDS may have different
PLAN_HASH_VALUES as well for one period as the code doing operations that
invalidate prior plans (partition maintenance primarily).
I would like to be able to "roll up" similar statements (where SQL_IDs are the
same and where the SQL_IDs are different but the statement are fundamentally
the same).
I'm beginning to doubt I'm going to be able to accomplish that in any
meaningful way.
Chris
From: David Fitzjarrell [mailto:oratune@xxxxxxxxx]
Sent: Tuesday, November 20, 2012 4:06 PM
To: Taylor Christopher - Nashville; Jed_Walker@xxxxxxxxxxxxxxxxx;
andy@xxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: DBA_HIST_SQLSTAT
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<mailto:Christopher.Taylor2@xxxxxxxxxxxx>"
<Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>>
To: Jed_Walker@xxxxxxxxxxxxxxxxx<mailto:Jed_Walker@xxxxxxxxxxxxxxxxx>;
andy@xxxxxxxxxxxxxxx<mailto:andy@xxxxxxxxxxxxxxx>;
oratune@xxxxxxxxx<mailto:oratune@xxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx<mailto: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<mailto:Jed_Walker@xxxxxxxxxxxxxxxxx>]
Sent: Tuesday, November 20, 2012 2:53 PM
To: andy@xxxxxxxxxxxxxxx<mailto:andy@xxxxxxxxxxxxxxx>;
oratune@xxxxxxxxx<mailto:oratune@xxxxxxxxx>
Cc: Taylor Christopher - Nashville;
oracle-l@xxxxxxxxxxxxx<mailto: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>
[mailto: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<mailto:oratune@xxxxxxxxx>
Cc: Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: DBA_HIST_SQLSTAT
On Tue, Nov 20, 2012 at 12:24 PM, David Fitzjarrell
<oratune@xxxxxxxxx<mailto: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:
