23Sep/110
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
-----
- 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'
/
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!