How to find if your SQL is using SPM baseline

转自 http://intermediatesql.com/oracle/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!

 

补充问题

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?

 

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