Intermediate SQL Color Coded SQL, UNIX and Database Essays


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.

SQL> @DESC dba_sql_plan_baselines
 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.

SQL> SELECT signature, sql_handle, plan_name
  FROM dba_sql_plan_baselines WHERE rownum <= 3;

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

SELECT sql_handle, plan_name
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.


Comments (6) Trackbacks (1)
  1. 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 :-)

  2. 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

  3. 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?

  4. Thanks, I am been working on this for a few months, even oracle support told me it can not find relationship between sql id and baseline.

  5. Thanks for your post.
    I was going through these woods, and found your clearing.
    Useful and helpful.

  6. Thanks!! It was really very helpful.

Leave a comment