Tuesday, April 18, 2017

how to find sqltext by using old_hash_value

In Oracle standard edition, AWR isn't there, as workaround, we still rely on STATSPACK.

one issue facing is incompleted SQL text listed in statspack report, when it is long ...


below is my method, to dig out the completed sql text.
1. with old_hash_value, I find sql_id from STATS$SQL_PLAN_usage
2. with sql_id, pull from dbms_xplan.display_cursor (if still in memory), or get from STATS$SQLTEXT

e.g.

 @sp_find_sqlid_by_oldhashvalue.sql
Enter value for ohv: 3353767585
old   1: select distinct sql_id from STATS$SQL_PLAN_usage where OLD_HASH_VALUE=&ohv
new   1: select distinct sql_id from STATS$SQL_PLAN_usage where OLD_HASH_VALUE=3353767585

SQL_ID
-------------
6mtr4m135zf22

@dplan
Enter value for sqlid: 6mtr4m135zf22
old   1: select * from table(dbms_xplan.display_cursor('&sqlid'))
new   1: select * from table(dbms_xplan.display_cursor('6mtr4m135zf22'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 6mtr4m135zf22, child number: 0 cannot be found


 @sp_hist_sqltext
Enter value for sql_id: 6mtr4m135zf22
old   1: select sql_text from STATS$SQLTEXT where sql_id='&sql_id' order by piece
new   1: select sql_text from STATS$SQLTEXT where sql_id='6mtr4m135zf22' order by piece

SQL_TEXT
----------------------------------------------------------------
CREATE TABLE ....

11 rows selected.


Next, can use below sql to find out historical execution plan too.

>  cat sp_hist_sqlplan_oldhash.sql

set lines 180
select snap_id, id,parent_id, position,operation,object_name, bytes,cardinality, cost,cpu_cost,temp_space,plan_hash_value from STATS$SQL_PLAN
where plan_hash_value in (select distinct plan_hash_value from  STATS$SQL_PLAN_usage where
 OLD_HASH_VALUE='&old_hash_value')
order by snap_id, id,parent_id, position, plan_hash_value
/