Re: DBA_HIST_SQLSTAT

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Wed, 21 Nov 2012 16:22:53 +0100
Message-ID: <CAJu8R6j4wWFxORQsdh_-VkaO2N4AJ2uRT2513p8uGaH4m5sAFQ_at_mail.gmail.com>



There are two types of cursors (a) parent cursor and (b) child cursor. Think about the parent cursor as the cursor responsible for sharing the SQL statement text. The SQL text *must be exactly the same *for the parent cursor to be shared.

Think about the child cursor as the cursor responsible for sharing the execution plan. Tow SQL statements can share the same child cursor if they share the same parent cursor and the same optimizer execution parameters (environment). The mismatch that causes several child cursors is exposed in the view v$sql_shared_cursor (it could be optimizer_mode_mismatch , bind_mismatch etc...)

So in my opinion SQL statements that differ by literals (text) can't share the same child cursors (plans). But you could have SQL statements having the same parent cursor (exact same SQL text) but having different child cursor (different plans)

Best regards

Mohamed Houri

2012/11/21 <Christopher.Taylor2_at_parallon.net>

> 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
>
>

-- 
Bien Respectueusement
Mohamed Houri


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 21 2012 - 16:22:53 CET

Original text of this message