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
/
