Thursday, July 28, 2011

How to find SQL_ID and PLAN_HASH_VALUE in Oracle SQL Plan Management Baselines

SQL_ID and/or PLAN_HASH_VALUE are used almost globally across Oracle database to identify query. This is not a case for SPM mechanism
using different identifiers - SQL_HANDLE for query and PLAN_NAME for execution plan. SQL_HANDLE contain hexadecimal representation of EXACT_MATCHING_SIGNATURE from V$SQL but we don't have any information in DBA_SQL_PLAN_BASELINES about SQL_ID and PLAN_HASH_VALUE.
After some research I found two ways to find SQL_ID matched to SQL baseline (if there is more please let me know).
Let me present following example - first of all test environment will be prepared
SQL> select sql_handle, plan_name from dba_sql_plan_baselines;

no rows selected

SQL> select count(*) from soe.customers where customer_id = 19998;

  COUNT(*)
----------
         1

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449
There is no plan baseline at the beginning to make it more clear and only baseline for test query will be loaded.
SQL> vari rc number
SQL> exec :rc:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'aa1m3a9b64srz',plan_hash_value=>1925551782)

PL/SQL procedure successfully completed.

SQL> print rc

                            RC
------------------------------
                             1

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449

SQL> select count(*) from soe.customers where customer_id = 19998;

                      COUNT(*)
------------------------------
                             1

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449
Plan baseline has been loaded but still not used by query - new cursor has to be opened.
SQL> alter system flush shared_pool;

System altered.

SQL> select count(*) from soe.customers where customer_id = 19998;

                      COUNT(*)
------------------------------
                             1

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449 SQL_PLAN_gq2afgwdf4k9je2333459
After reload test query is using SQL plan baseline.

Now is a time to present first method of conversing SQL_HANLDE and PLAN_NAME into SQL_ID and PLAN_HASH_VALUE. This method is simple but it has one pitfall - query has to be in shared pool.
SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

SQL_ID                       PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ------------------------------ ------------------------------ ------------------------------
aa1m3a9b64srz                     1925551782           18089075686129158449 SQL_PLAN_gq2afgwdf4k9je2333459

SQL> select sql_handle from dba_sql_plan_baselines where plan_name = 'SQL_PLAN_gq2afgwdf4k9je2333459';

SQL_HANDLE
------------------------------
SQL_fb094e7f1ae24931

SQL> select sql_handle, plan_name from dba_sql_plan_baselines where sql_handle = 'SQL_fb094e7f1ae24931';

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_fb094e7f1ae24931           SQL_PLAN_gq2afgwdf4k9je2333459
First we need to display SQL_PLAN_BASELINE from V$SQL which is related to PLAN_NAME column in DBA_SQL_PLAN_BASELINES. Second query shows SQL_HANDLE for that PLAN_NAME and last one will show all entries (it can be more plans for one query) for this particular SQL_HANDLE. At the end all required information are on screen. I have used this method for long time but I hit into problem several times as my query wasn't in v$sql anymore.

Second method is more complicated but it is working even when SQL is not in shared pool any more. DBMS_XPLAN has possibility to display plan for particular SQL_HANDLE and it will be our source of PLAN_HASH_VALUE
SQL> select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('SQL_fb094e7f1ae24931'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_fb094e7f1ae24931
SQL text: select count(*) from soe.customers where customer_id = 19998
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_gq2afgwdf4k9je2333459         Plan id: 3795006553
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1925551782

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |              |     1 |     6 |            |          |       |       |
|   2 |   PARTITION HASH SINGLE|              |     1 |     6 |     1   (0)| 00:00:01 |    16 |    16 |
|*  3 |    INDEX UNIQUE SCAN   | CUSTOMERS_PK |     1 |     6 |     1   (0)| 00:00:01 |    16 |    16 |
-------------------------------------------------------------------------------------------------------

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

   3 - access("CUSTOMER_ID"=19998)

26 rows selected.
Unfortunately there is no still information about SQL_ID. But I realize that DBA_SQL_PLAN_BASELINES is keeping full text of SQL query so I should be able to calculate HASH_VALUE. Anyway this is what DBMS_XPLAN is using to generate PLAN_HASH_VALUE - optimizer is generating plan when DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE has been invoked. I tried to calculate SQL_ID using md5sum or perl but then I recall post from Tanel Poder blog how to calculate SQL_ID based on HASH_VALUE and other post by Slavik Markovich how to calculate SQL_ID using PL/SQL. Great Work Guys !
Rest was quite simple - I joined all parts together (PL/SQL loop calculating SQL_ID has been taken from Slavik script)
declare
v_sqlid VARCHAR2(13);
v_num number;
BEGIN
dbms_output.put_line('SQL_ID       '||' '|| 'PLAN_HASH_VALUE' || ' ' || 'SQL_HANDLE                    ' || ' ' || 'PLAN_NAME');
dbms_output.put_line('-------------'||' '|| '---------------' || ' ' || '------------------------------' || ' ' || '--------------------------------');
for a in (select sql_handle, plan_name, trim(substr(g.PLAN_TABLE_OUTPUT,instr(g.PLAN_TABLE_OUTPUT,':')+1)) plan_hash_value, sql_text
                 from (select t.*, c.sql_handle, c.plan_name, c.sql_text from dba_sql_plan_baselines c, table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
                 where c.sql_handle = '&sql_handle') g
                 where PLAN_TABLE_OUTPUT like 'Plan hash value%') loop
    v_num := to_number(sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),9,4)) || sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
    v_sqlid := '';
    FOR i IN 0 .. FLOOR(LN(v_num) / LN(32))
    LOOP
        v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
    END LOOP;
    dbms_output.put_line(v_sqlid ||' ' || rpad(a.plan_hash_value,15) || ' ' || rpad(a.sql_handle,30) ||  ' ' || rpad(a.plan_name,30));
end loop;
end;
How it work:
SQL> declare
  2  v_sqlid VARCHAR2(13);
  3  v_num number;
  4  BEGIN
dbms_output.put_line('SQL_ID       '||' '|| 'PLAN_HASH_VALUE' || ' ' || 'SQL_HANDLE                    ' || ' ' || 'PLAN_NAME');
  6      LOOP
dbms_output.put_line('-------------'||' '|| '---------------' || ' ' || '------------------------------' || ' ' || '--------------------------------');
for a in (select sql_handle, plan_name, trim(substr(g.PLAN_TABLE_OUTPUT,instr(g.PLAN_TABLE_OUTPUT,':')+1)) plan_hash_value, sql_text
                 from (select t.*, c.sql_handle, c.plan_name, c.sql_text from dba_sql_plan_baselines c, table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
                 where c.sql_handle = '&sql_handle') g
                 where PLAN_TABLE_OUTPUT like 'Plan hash value%') loop
    v_num := to_number(sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),9,4)) || sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
    v_sqlid := '';
    FOR i IN 0 .. FLOOR(LN(v_num) / LN(32))
    LOOP
        v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
    END LOOP;
    dbms_output.put_line(v_sqlid ||' ' || rpad(a.plan_hash_value,15) || ' ' || rpad(a.sql_handle,30) ||  ' ' || rpad(a.plan_name,30));
 18  end loop;
 19  end;
 20  /
Enter value for sql_handle: SQL_fb094e7f1ae24931
old   9:                  where c.sql_handle = '&sql_handle') g
new   9:                  where c.sql_handle = 'SQL_fb094e7f1ae24931') g
SQL_ID        PLAN_HASH_VALUE SQL_HANDLE                     PLAN_NAME
------------- --------------- ------------------------------ --------------------------------
aa1m3a9b64srz 1925551782      SQL_fb094e7f1ae24931           SQL_PLAN_gq2afgwdf4k9je2333459

PL/SQL procedure successfully completed.
Let me clean shared pool
SQL> alter system flush shared_pool;

System altered.

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

no rows selected
Let's try one more time
SQL> declare
  2  v_sqlid VARCHAR2(13);
  3  v_num number;
  4  BEGIN
dbms_output.put_line('SQL_ID       '||' '|| 'PLAN_HASH_VALUE' || ' ' || 'SQL_HANDLE                    ' || ' ' || 'PLAN_NAME');
  6      LOOP
dbms_output.put_line('-------------'||' '|| '---------------' || ' ' || '------------------------------' || ' ' || '--------------------------------');
for a in (select sql_handle, plan_name, trim(substr(g.PLAN_TABLE_OUTPUT,instr(g.PLAN_TABLE_OUTPUT,':')+1)) plan_hash_value, sql_text
                 from (select t.*, c.sql_handle, c.plan_name, c.sql_text from dba_sql_plan_baselines c, table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
                 where c.sql_handle = '&sql_handle') g
                 where PLAN_TABLE_OUTPUT like 'Plan hash value%') loop
    v_num := to_number(sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),9,4)) || sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
    v_sqlid := '';
    FOR i IN 0 .. FLOOR(LN(v_num) / LN(32))
    LOOP
        v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
    END LOOP;
    dbms_output.put_line(v_sqlid ||' ' || rpad(a.plan_hash_value,15) || ' ' || rpad(a.sql_handle,30) ||  ' ' || rpad(a.plan_name,30));
 18  end loop;
 19  end;
 20  /
Enter value for sql_handle: SQL_fb094e7f1ae24931
old   9:                  where c.sql_handle = '&sql_handle') g
new   9:                  where c.sql_handle = 'SQL_fb094e7f1ae24931') g
SQL_ID        PLAN_HASH_VALUE SQL_HANDLE                     PLAN_NAME
------------- --------------- ------------------------------ --------------------------------
aa1m3a9b64srz 1925551782      SQL_fb094e7f1ae24931           SQL_PLAN_gq2afgwdf4k9je2333459

PL/SQL procedure successfully completed.

SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

no rows selected

SQL>
Hope it help work with SQL Plan Management feature.

regards,
Marcin

4 comments:

karlarao said...

cool! thanks for the tip.. ;)

Kerry Osborne said...

Hi Marcin,

Very interesting post. I had wanted to find a way to connect a baseline to a SQL_ID in the past. Thanks for posting this. I wrote up a small post on my blog as well, building on your method. Thanks again for taking the time to publish your findings.

Kerry

Kyoung il Roh said...

very useful sql script..

but i just found it needs some modifications..(lpad function)

bms_output.put_line(lpad(v_sqlid, 13, '0') ..

it's because this useful script omitted leading zeros of sql_id.

sql_id of 0000f4x1g96rc is printed just to f4x1g96rc

Marcin Przepiorowski said...

Thanks for correction.