Intermediate SQL Color Coded SQL, UNIX and Database Essays


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:

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


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.