Check the long running query and get the sql_id
SET LINES 1000 PAGES 5000 COL program FOR A20 COL username FOR a10 COL machine FOR a40 SELECT sid, serial#, program, sql_id, machine, status, TO_CHAR(sql_exec_start, 'MM-DD HH24:MI:SS') AS startdt, (SYSDATE - sql_exec_start)*24*60*60 AS during FROM v$session WHERE username IS NOT NULL AND status='ACTIVE' ORDER BY during DESC;

Get the source code from sql_id SELECT sql_text FROM v$sqltext WHERE SQL_id='7bcux5gc5n89h' ORDER BY piece;

SQL_TEXT

UPDATE ps_bi_hdr SET LAST_LINE_SEQ_NUM = LAST_LINE_SEQ_NUM + :1 WHERE business_unit=:2 and invoice=:3 Get the execution plan from the memory set lines 1000 pages 50000 SELECT * FROM table(dbms_xplan.display_cursor('7bcux5gc5n89h')); Get all execution plans from history SELECT DISTINCT plan_hash_value FROM dba_hist_sqlstat WHERE sql_id='7bcux5gc5n89h';

PLAN_HASH_VALUE

  979624593
 1892976149

Get execution plan by PHV (Check the old plan) SELECT * FROM table(dbms_xplan.display_awr('7bcux5gc5n89h', 979624593)); Get the owner and object_type for PS_BI_HDR SELECT owner, object_type FROM dba_objects WHERE object_name='PS_BI_HDR';

OWNER OBJECT_TYPE

SYSADM TABLE SYSADM INDEX Gather statement set timing on EXEC dbms_stats.gather_table_stats(ownname=>'SYSADM',tabname=>'PS_BI_HDR',degree=>32,cascade=>TRUE); Get the SNAP_ID for the PLAN_HASH_VALUE SELECT max(snap_id), plan_hash_value FROM dba_hist_sqlstat WHERE sql_id='7bcux5gc5n89h' GROUP BY plan_hash_value ORDER BY 1;

MAX(SNAP_ID) PLAN_HASH_VALUE

   24753       979624593
   24754      1892976149

Check if there any baseline exists set lines 1000 pages 50000 col SQL_HANDLE for a20 col PLAN_NAME for a30 SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT FROM DBA_SQL_PLAN_BASELINES WHERE ACCEPTED = 'YES' ORDER BY LAST_MODIFIED; Create a baseline for switching execution plan from current one to old one EXEC dbms_sqltune.create_sqlset(sqlset_name=>'DEVINSENROLLMENT', description=>'DevInsEnrollment Job'); SET serveroutput on DECLARE baseline_ref DBMS_SQLTUNE.SQLSET_CURSOR; ret PLS_INTEGER; BEGIN OPEN bashline_ref FOR select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( begin_snap=>24752, end_snap=>24753, basic_filter=>'sql_id='||CHR(39)||'7bcux5gc5n89h'||CHR(39)||'and plan_hash_value=979624593', attribute_list=>'ALL' )) p; DBMS_SQLTUNE.LOAD_SQLSET('DEVINSENROLLMENT',baseline_ref); ret := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name=>'DEVINSENROLLMENT', sqlset_owner=>'SYS', fixed=>'NO', enabled=>'YES' ); DBMS_OUTPUT.PUT_LINE(' Return ret: ' || ret); END; / EXEC dbms_sqltune.drop_sqlset(sqlset_name=>'DEVINSENROLLMENT'); Flush a single statement from memory SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID = '7bcux5gc5n89h';

ADDRESS HASH_VALUE

000000022B8A9A90 3629785392

EXEC DBMS_SHARED_POOL.PURGE('000000022B8A9A90,3629785392','C');