Intermediate SQL Color Coded SQL, UNIX and Database Essays

10Apr/113

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;

 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:

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.

Cheers!

Comments (3) 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?


Leave a comment