Kerry Osborne's Oracle Blog

I ran across a great post by Marcin Przepiorowski (How to find SQL_ID and PLAN_HASH_VALUE in Oracle SQL Plan Management Baselines) thanks to Karl Arao‘s tweet. Marcin posted a bit of code for calculating a SQL_ID using the text of a SQL statement (based on some research by Tanel Poder). I was aware of Tanel’s research but had not taken the time to try to figure out how to code it. The context of Marcin’s post was based on wanting to relate a Baseline to a SQL_ID. This is something I’ve wanted to do in the past so I was quite interested in the post. As a bonus Marcin also demonstrated a technique to pull the PLAN_HASH_VALUE associated with a Baseline. Of course I can never leave well enough alone and so I had to re-arrange Marcin’s code a little bit to suite my own purposes. So I created a function that returns either the PLAN_HASH_VALUE or the SQL_ID of a Baseline. Here’s the code to create the function: create_baseline_info.sql and a script that uses it: baselines2.sql

Here’s an example of how to use them:

SYS@SANDBOX1> select count(*) from kso.skew;

  COUNT(*)
----------
  32000004

SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.skew
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
gm0w4as5hhr4m      0  578366071      1       1.30      8 Yes          71.78 select count(*) from kso.skew

SYS@SANDBOX1> @dplan
Enter value for sql_id: gm0w4as5hhr4m
Enter value for child_no: 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gm0w4as5hhr4m, child number 0
-------------------------------------
select count(*) from kso.skew

Plan hash value: 578366071

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |  6183 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |          |    32M|  6183   (1)| 00:01:15 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SKEW     |    32M|  6183   (1)| 00:01:15 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - storage(:Z>=:Z AND :Z<=:Z)


23 rows selected.

SYS@SANDBOX1> @create_baseline
Enter value for sql_id: gm0w4as5hhr4m
Enter value for plan_hash_value: 578366071
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQLID_sqlid_planhashvalue): 
sql_id: gm0w4as5hhr4m
plan_hash_value: 578366071
fixed: NO
enabled: YES
plan_name: SQLID_gm0w4as5hhr4m_578366071
sql_handle: SQL_e86d1a732b7a5c25
Baseline SQLID_gm0w4as5hhr4m_578366071 created.

PL/SQL procedure successfully completed.

SYS@SANDBOX1> select sql_handle, plan_name from dba_sql_plan_baselines where plan_name like upper('SQLID_gm0w4as5hhr4m_578366071');

SQL_HANDLE           PLAN_NAME
-------------------- ------------------------------
SQL_e86d1a732b7a5c25 SQLID_GM0W4AS5HHR4M_578366071

SYS@SANDBOX1> @baselines2
Enter value for sql_text: select count(*) from kso.skew%
Enter value for name: 
Enter value for plan_name: 

SQL_ID        SQL_TEXT                                           PLAN_HASH_VALUE SQL_HANDLE           PLAN_NAME                      ENABLED ACC FIX LAST_EXECUTED
------------- -------------------------------------------------- --------------- -------------------- ------------------------------ ------- --- --- ----------------
1jp7sjmt0wp1j select count(*) from kso.skew s , skew_gtt g where 2414665383      SQL_936b37ad684d18d4 SQL_PLAN_96utrppn4u66ncefcc71f YES     NO  NO
                                                                 2178955164      SQL_936b37ad684d18d4 SQL_PLAN_96utrppn4u66ne441e87d YES     NO  NO
gm0w4as5hhr4m select count(*) from kso.skew                      578366071       SQL_e86d1a732b7a5c25 SQLID_GM0W4AS5HHR4M_578366071  YES     YES NO

SQL> -- cool - it's a match

So in the previous listing I ran a select statement, found the SQL_ID and PLAN_HASH_VALUE for that statement (using fsx.sql), displayed the plan (using dplan.sql) and created a Baseline for the statement (using create_baseline.sql). I then verified that the Baseline existed in DBA_SQL_PLAN_BASELINES and ran the baselines2.sql script which returns the SQL_ID and PLAN_HASH_VALUE using the baseline_info() function, which as you can see matched the original query.