PT (check sql plan taking long time)

1) Gather stats + bkp stats + flush old plan




Disable the SQL Profile


EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('spm_id','STATUS','DISABLED');


Flush the SQL from shared pool



Get the address and hash_value of the sql_id:


select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='abcdefg';



Now purge the sql statement



exec DBMS_SHARED_POOL.PURGE ('0000000679JDGY99,1118349065','C');

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='abscdfs’;

it should show no row selected




--create table for stat's backup

begin   
dbms_stats.create_stat_table(ownname => 'SYSTEM',stattab => 'bkp_stats_TAB',tblspace => 'USERS');
end;
/








--taking backup of current stats of table



begin
dbms_stats.export_table_stats(ownname=>'XYZ',tabname=>'TBLE',stattab => 'bkp_stats_TAB',statid => 'xyz_tblc',statown => 'SYSTEM',cascade=>true);
end;
/


--gather stats

EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS( 'XYZ','TBL', CASCADE => TRUE ,degree => 28,estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

********************* FIX after gather stats + flush old plan ********************

 SQL> select NUM_ROWS,LAST_ANALYZED,degree from dba_tables where table_name='TBL ' and owner='XYZ';

  NUM_ROWS LAST_ANAL DEGREE
---------- --------- ----------------------------------------
  22150995 08-JAN-19          1


SQL> select NUM_ROWS,LAST_ANALYZED,degree from dba_indexes  where index_name='TBLINDX_PK' and owner='XYZ';

  NUM_ROWS LAST_ANAL DEGREE
---------- --------- ----------------------------------------
  21882106 08-JAN-19 10



===
SQL> select LAST_DDL_TIME from dba_objects where OBJECT_NAME='TBLINDEX_PK';

LAST_DDL_
---------
05-JAN-19

SQL> alter index XYZ.TBLINDEX_PK noparallel;

Index altered.

SQL>  select LAST_DDL_TIME from dba_objects where OBJECT_NAME='TBLINDEX_PK';

LAST_DDL_
---------
09-JAN-19

SQL>  select NUM_ROWS,LAST_ANALYZED,degree from dba_indexes  where index_name='TBLINDEX_PK' and owner='XYZ';

  NUM_ROWS LAST_ANAL DEGREE
---------- --------- ----------------------------------------
  21882106 08-JAN-19 1

SQL>  select NUM_ROWS,LAST_ANALYZED,degree from dba_indexes  where index_name='TBLINDEX_PK' and owner='XYZ';

  NUM_ROWS LAST_ANAL DEGREE
---------- --------- ----------------------------------------
  21882106 08-JAN-19 1

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='ao9yn1shdk';

ADDRESS          HASH_VALUE
---------------- ----------
000000045BD67B88 1118349065

SQL> exec DBMS_SHARED_POOL.PURGE ('000000045BD67B88,1118349065','C');

PL/SQL procedure successfully completed.



SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='ao9yn1shdk';

ADDRESS          HASH_VALUE
---------------- ----------
000000045BD67B88 1118349065