How to find SPM baseline by sql_id
When you start working with SQL Plan baselines, one of the annoying things that you might find is that the main “baseline” dictionary view dba_sql_plan_baselines does not have sql_id column.
Name NULL? TYPE
----------------------------------------- -------- ----------------------------
SIGNATURE NOT NULL NUMBER
SQL_HANDLE NOT NULL VARCHAR2(30)
SQL_TEXT NOT NULL CLOB
PLAN_NAME NOT NULL VARCHAR2(30)
CREATOR VARCHAR2(30)
ORIGIN VARCHAR2(14)
...
There are all kinds of identifiers: SQL_HANDLE, PLAN_NAME, SIGNATURE, but if you look at them, nothing resembles SQL_ID.
FROM dba_sql_plan_baselines WHERE rownum <= 3;
SIGNATURE SQL_HANDLE PLAN_NAME
---------- ------------------------------ ------------------------------
9.1707E+15 SYS_SQL_002094bafbab9fd3 SYS_SQL_PLAN_fbab9fd35f783afa
9.1707E+15 SYS_SQL_002094bafbab9fd3 SYS_SQL_PLAN_fbab9fd3bcdf6d3e
1.2199E+16 SYS_SQL_002b56dca9b266b3 SYS_SQL_PLAN_a9b266b3a3910e56
And yet, almost everywhere ORACLE seems to be making SQL_ID as the primary SQL identifier.
Of course, you can always use SQL_TEXT to join to v$sql, but this seems to be a bit drastic.
Fortunately, there is a better way.
Starting from ORACLE 10g v$sql has 2 additional columns: exact_matching_signature and force_matching_signature and it turns out they exactly match dba_sql_plan_baseline.signature, so finding out baselines that “attach” to your particular statement becomes quite easy:
FROM dba_sql_plan_baselines
WHERE signature IN (
SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
)
/
Why did ORACLE create a separate SQL identifier here ?
My guess is – SQL_IDs and SPM baselines are not exactly the same thing and do not have 1-1 mapping between them.
- SQL_ID is just another representation of SQL text (you can read about it in Tanel Poder’s blog)
- While baseline is about SQL plan (group of plans, really) and technically the same group of plans can be attached to many “distinct” SQLs.
Cheers!
April 11th, 2011 - 11:14
Thanks – this is extremely useful. I was just thinking about this today and then whilst I was distracted looking at something else, I came back to find the answer in my feed reader
May 26th, 2011 - 22:03
Good job!
EXACT_MATCHING_SIGNATURE NUMBER Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
FORCE_MATCHING_SIGNATURE NUMBER The signature used when the CURSOR_SHARING parameter is set to FORCE
June 16th, 2011 - 15:48
I have cursor_sharing set to EXACT. What does it mean when some sqls have exact_matching_signature and force_matching_signature set to 0?