How to find SPM baseline by sql_id

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