Intermediate SQL Color Coded SQL, UNIX and Database Essays

23Sep/113

How to find if your SQL is using SPM baseline

I’ve always wondered how many of my SQLs are NOT using baselines.

Of course, when you run DBMS_XPLAN.DISPLAY_CURSOR for the statement, it can put a nicely formatted note for you, something like:

Note
-----
   - SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement

But can you find it globally for ALL SQLs in the shared pool ?

It turns out that the answer was staring right in my face: v$sql has a SQL_PLAN_BASELINE column, which along with EXACT_MATCHING_SIGNATURE is a key into dba_sql_plan_baselines view.

If v$sql.SQL_PLAN_BASELINE is empty, then the baseline is NOT used, otherwise the column contains baseline plan name being used at the moment.

Anyway, here is a useful script to find out if your particular cursor is using plan(s) from baseline or not:

SELECT b.sql_handle, b.plan_name, s.child_number,
  s.plan_hash_value, s.executions
FROM v$sql s, dba_sql_plan_baselines b
WHERE s.exact_matching_signature = b.signature(+)
  AND s.sql_plan_baseline = b.plan_name(+)
  AND s.sql_id='&SQL_ID'
/

Cheers!

Comments (3) Trackbacks (0)
  1. SQL_profile, sql_patch and sql_plan_baseline columns exist in gv$sql/sqlarea. Funnily enough, dba_hist_sqlstat doesn’t have sql_patch and sql_plan_baseline. All you have is sql_profile column. If you have to do a probe into finding historically on whether a sql_patch of sql_plan_baselines were indeed used for a SQL execution, it doesn’t appear to be stored in any of the dba_hist_sql% views.

    Any insight into this?

  2. Hey Sridhar,

    dba_hist_sqlstat also does not have child cursor information, which, in my mind, is a more serious limitation.

    But back to you question: I do not know of any direct way to see in AWR history whether a baseline or sql patch was used at some point in time.
    But there are always a few indirect ways to get what you want.

    First of all, there is plan_hash_value. Since baseline’s job is to force the plan, as long as plan_hash_value matches what you want, it really is quite irrelevant whether that plan was forced by baseline or was “discovered” by the optimizer.

    Also look at dba_hist_sqlstat.force_matching_signature which should directly match dba_sql_plan_baselines.signature for existing baselines (as long as bind variables are used đŸ™‚ )

    Maxym Kharchenko

  3. ok, the sql above tells me that my baseline is not being used, how do I make it use ?


Leave a comment

No trackbacks yet.