Find Plan history and pin good plan in Oracle database | Performance tuning

 Query to check complete SQL

…………………………………

SQL> select * from DBA_HIST_SQLtext where SQL_TEXT like '%select object_name,object_type from dba_objects%';

Query to check plan hash value for N number of days

…………………………………………………………

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1, executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1, executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&SQL_ID'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number

and executions_delta > 0

order by 1, 2, 3;

Drop Profile:

set serveroutput on
begin
dbms_sqltune.drop_sql_profile(name=>'coe_afjbvzx1dzOrs_1089342751);
end;

/

SQL PATCH:

SQL> connect / as sysdba

variable x varchar2(100);

SQL> exec :x:=dbms_sqldiag.create_sql_patch(sql_id=>'&SQL_ID',hint_text=>'optimizer_features_enable("12.1.0.2")',name=>'SQL_Patch_FOR_FULL_TABLEDETAILS_REPORT');

Enter value for sql_id: 419m6hbp7jzx2

PL/SQL procedure successfully completed.

DROP SQL PATCH :

BEGIN

DBMS_SQLDIAG.drop_sql_patch(name => 'SQL_Patch_FOR_FULL_TABLEDETAILS_REPORT');

END;

/

********************************

To Pin the PLAN_HASH_VALUE using BASE LINE

Declare baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;

my_plans pls_integer;

I_sql_id v$sql.sql_id%type := 'afjbvzx1d20rs';

l_plan_hash_value vSsql-plan_hash_value%type:=2287798688;

I_snap_id dba_hist_sqlstat.snap_id%type;

begin

select snap_ id into I_snap_id from dba_hist_sqlstat where sql_id = Isql_id and plan hash_value = I_plan _hash_value and rownum < 2;

dbms_sqltune.create_sqiset(sqlset_name=>I_sql_id I| ' _awr', description => I_sql_id || "' I| L_plan_hash_value | | ' AWR Load’);

open baseline _ref _cur for select VALUE(p) from table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(L_snap_id - 1, L_snap_ id,

'sql_id-"" 11 L_sqL_id II "" and plan _hash _value-* I| Lplan_hash_value,

NULL, NULL, NULL, NULL, NULL, NULL, 'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET(L_sq|_Id || '_awr', baseline_ref_cur);

my_plans = dbms_spm.load _plans_from_sqlset (sqlset_name => [sql_id II '_awr', fixed => 'YES', enabled => 'YES");

end;

/

****************************

DROP SQL BASE LINE:

キキキキネキキキキネネキキキキキ

declare

drop _result pls_integer;

begin drop_result:= DBMS_SPM.DROP_SOL_PLAN_BASELINE( sql_handle => 'SOL_bd58b9032bb5928a', plan _name => 'SQL_PLAN_97guq53r2rp6ke1fed9e5');

dbms output.put_line(drop_result);

end;

/

select * from dba_sql_profiles;

select * from ba_sq|_patches where NAME like'%afjbvzx1d20rs%;

select * from ba sql plan _baselines;

select ADDRESS, HASH_VALUE from V$SOLAREAJwhere SOL_Id-'afibvzxidzors';

exec DBMS_SHARED_POOL.PURGE ('000000010CA6A1D8,1121944312', 'C');

select ADDRESS, HASH_VALUE from V$SOLAREA where SOL_Id-'afibvzxidzOrs';