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
