sql plan baseline


optimizer_capture_sql_plan_baselines=FALSE
optimizer_use_sql_plan_baselines=TRUE;


-- create tuning set
BEGIN
  dbms_sqltune.create_sqlset(
      sqlset_name => 'STS1', 
      sqlset_owner => 'SYSTEM');
END;
/

-- populate tuning set from cursor cache
DECLARE
  stscur dbms_sqltune.sqlset_cursor;
BEGIN
  
  open stscur for
    select value(p)
    from   table(dbms_sqltune.select_cursor_cache(
                   basic_filter      => 'sql_id = ''...''',
                   object_filter     => null,
                   ranking_measure1  => null,
                   ranking_measure2  => null,
                   ranking_measure3  => null,
                   result_percentage => null,
                   result_limit      => null,
                   attribute_list    => 'ALL')) P;
                   
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS1',
                           populate_cursor => stscur,
                           sqlset_owner    => 'SYSTEM');
END;
/

-- populate tuning set from AWR
DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN  
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(
        DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                    begin_snap        => 1234,
                    end_snap          => 1235,
                    basic_filter      => 'sql_id = ''...''',
                    object_filter     => NULL, 
                    ranking_measure1  => NULL,
                    ranking_measure2  => NULL,
                    ranking_measure3  => NULL,
                    result_percentage => NULL,
                    result_limit      => NULL,
                    attribute_list    => 'ALL')) P;

  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS1',
                           populate_cursor => cur,
                           sqlset_owner    => 'SYSTEM');
END;
/

-- select plans in tuning set
SELECT
     first_load_time,
     executions as execs,
     parsing_schema_name,
     elapsed_time  / 1000000 as elapsed_time_secs  ,
     cpu_time / 1000000 as cpu_time_secs           ,
     buffer_gets,
     disk_reads,
     direct_writes,
     rows_processed,
     fetches,
     optimizer_cost,
     sql_plan,
     plan_hash_value,
     sql_id,
     sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
                   sqlset_name  => 'STS1',
                   sqlset_owner => 'SYSTEM'))
;


-- create baseline using the plan in tunning set                           
DECLARE
  my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
       sqlset_name  => 'STS1', 
       sqlset_owner => 'SYSTEM',
       basic_filter=>'plan_hash_value = ''1239572551'''
     );
 dbms_output.put_line(my_plans);                           
END;
/

-- check baselines
select sql_handle, 
       plan_name, 
       enabled, 
       accepted, 
       sql_text
  from dba_sql_plan_baselines
 where sql_text like '%...%'
;

-- accept plan for baseline
DECLARE
  report clob;
BEGIN
  report := dbms_spm.evolve_sql_plan_baseline(
    sql_handle => 'SQL_a...', 
    plan_name  => 'SQL_PLAN_a...');
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s