Plan management

Look for sql_id based on a fragment of sql text:

select s.CHILD_NUMBER, s.PLAN_HASH_VALUE, s.IS_BIND_SENSITIVE, s.IS_BIND_AWARE, s.* from v$sql s
where
sql_text like ‘%O.ORDER_NO%’

 

Explain plan for sql_id:

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR(sql_id => ‘gd92hchnmrbwc’, cursor_child_no => 0));

select * from TABLE(dbms_xplan.display_awr(‘5q5x7zcnt3c5d’));

Purge bad plan from SGA:

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like ‘gd92hchnmrbwc’;

begin
    sys.DBMS_SHARED_POOL.PURGE (‘0000000C6EEFECA0, 691777420’, ‘C’);
end;

Lookup existing baselines:

select * from dba_sql_plan_baselines

Create new baseline from sql_id:

select plan_hash_value from v$sqlarea_plan_hash where SQL_ID like ‘5q5x7zcnt3c5d’;

declare
l_p PLS_INTEGER;
begin
l_p := dbms_spm.load_plans_from_cursor_cache(sql_id => ‘5q5x7zcnt3c5d’,
plan_hash_value => 1536724876,
fixed => ‘NO’,
enabled => ‘YES’);
end;

Show baseline details:

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>‘SQL_b4f5fb6afdc0392b’, format=>‘basic’));

Check if the baseline is being used:

select * from v$sql
where
sql_plan_baseline = ‘SQL_b4f5fb6afdc0392b’