SQL Plan Baselines


      select sql_handle, to_char(created,'DD-MM-YYYY') created, ENABLED, ACCEPTED, ORIGIN, substr(sql_text,1,40) sql_text from dba_sql_plan_baselines where created > '09-APR-18' order by created


     Select s.sql_text, b.plan_name, b.origin, b.accepted  

     From dba_sql_plan_baselines b, v$sql s  

Where s.exact_matching_signature = b.signature  

And s.SQL_PLAN_BASELINE = b.plan_name  

And SQL_ID ='&sql_id';  


      
      

      select sql_handle from dba_sql_plan_baselines where plan_name ='SQL_PLAN_f3skjbpguvvc658b57878'; 

      

      
set serveroutput on 

declare 

  report clob; 

 BEGIN 

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_e1e2515d5faded86'); 

 DBMS_OUTPUT.PUT_LINE(report); 

End; 

/


declare 

myplan pls_integer; 

begin 

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_3ee135d4bc7f913c',  

                                          plan_name  => 'SQL_PLAN_3xs9puky7z49w7e74e9f1',  

                                          attribute_name => 'ENABLED',  
   
                                          attribute_value => 'YES');  

end;  

/


DECLARE 

  l_plans_loaded  PLS_INTEGER; 

BEGIN 

  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( 

    sql_id => '1fkh93md0802n'); 

END; 

/

REPORT_AUTO_TUNING_TASK

The REPORT_AUTO_TUNING_TASK function replaces DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK as the preferred way to get the results of the SYS_AUTO_SQL_TUNING_TASK task. Calling it with no parameters shows the results for the latest execution of the task.


SET LONG 1000000 



SELECT DBMS_AUTO_SQLTUNE.report_auto_tuning_task FROM dual;

Plan Baseline Management


select parameter_name, parameter_value from dba_sql_management_config; 




PARAMETER_NAME                 PARAMETER_VALUE 

------------------------------ --------------- 

SPACE_BUDGET_PERCENT                        30 

PLAN_RETENTION_WEEKS                       105

Plan Retention


BEGIN 

  DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS',3); 

END; 

/ 



exec dbms_spm.configure(parameter_name => 'plan_retention_weeks',parameter_value => 12); 





Find SQL Baseline from SQL ID

SELECT -- s.sql_text,
b.plan_name, b.origin, b.accepted
From dba_sql_plan_baselines b, v$sql s
Where s.exact_matching_signature = b.signature
And s.SQL_PLAN_BASELINE = b.plan_name
And SQL_ID ='&sql_id';




DECLARE 

  l_plans_dropped  PLS_INTEGER; 

BEGIN 

  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline ( 

    sql_handle => NULL, 

    plan_name  => '&1'); 

  DBMS_OUTPUT.put_line(l_plans_dropped); 

END; 

/

HOME