My query is picking a bad execution plan , how to fix it to use the good execution plan available ( using Oracle baselines ) ?

Using Oracle baselines you can fix the sql plan for a SQLID:   

                                               

SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. This mechanism can build a SQL plan baseline, which is a set of accepted plans for a SQL statement. The accepted plans have been proven to perform well.

The goal of SQL plan baselines is to preserve the performance of corresponding SQL statements, regardless of changes in the database. Examples of changes include:

• New optimizer version
• Changes to optimizer statistics and optimizer parameters
• Changes to schema and metadata definitions
• Changes to system settings
• SQL profile creation

SQL plan baselines cannot help in cases where an event has caused irreversible execution plan changes, such as dropping an index.

The SQL tuning features of Oracle Database generate SQL profiles that help the optimizer to produce well-tuned plans. However, this mechanism is reactive and cannot guarantee stable performance when drastic database changes occur. SQL tuning can only resolve performance issues after they have occurred and are identified. 

For example, a SQL statement may become high-load because of a plan change, but SQL tuning cannot solve this problem until after the plan change occurs.

To know more details follow the below link

http://docs.oracle.com/cd/E28271_01/server.1111/e16638/optplanmgmt.htm

The query mines the AWR tables (beware the licence implications) for a specific SQL ID and date/time range and shows a few choice statistics for each snapshot period.

prompt enter start and end times in format DD-MON-YYYY [HH24:MI]

  

column sample_end format a21

select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end

, q.sql_id

, q.plan_hash_value

, sum(q.EXECUTIONS_DELTA) executions

, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec

, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec

, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) msec_exec

from dba_hist_sqlstat q, dba_hist_snapshot s

where q.SQL_ID=trim('&sqlid.')

and s.snap_id = q.snap_id

and s.dbid = q.dbid

and s.instance_number = q.instance_number

and s.end_interval_time >= to_date(trim('&start_time.'),'dd-mon-yyyy hh24:mi')

and s.begin_interval_time <= to_date(trim('&end_time.'),'dd-mon-yyyy hh24:mi')

and substr(to_char(s.end_interval_time,'DD-MON-YYYY DY HH24:MI'),13,2) like '%&hr24_filter.%'

group by s.snap_id

, q.sql_id

, q.plan_hash_value

order by s.snap_id, q.sql_id, q.plan_hash_value

/

 

 

Below I have example output showing one of the scripts many successful outings, quite a dramatic plan change I’m sure you’ll agree.

 

SQL> @awrsql

enter start and end times in format DD-MON-YYYY [HH24:MI]

Enter value for sqlid: 1jjpo2i4b313g

Enter value for start_time: 15-NOV-2011

Enter value for end_time: 21-NOV-2011 13:00

Enter value for hr24_filter:

 

 o/p  PIO = PHYSICAL READ LIO = LOGICAL RREAD

SAMPLE_END            SQL_ID        PLAN_HASH_VALUE EXECUTIONS PIO_PER_EXEC LIO_PER_EXEC  MSEC_EXEC

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

15-nov-2011 TUE 08:00 1jjpo2i4b313g      3133159894     129629            0            5          0

16-nov-2011 WED 08:01 1jjpo2i4b313g      3133159894     115003            0            5         .1

17-nov-2011 THU 08:01 1jjpo2i4b313g      3133159894     115741            0            5          0

18-nov-2011 FRI 07:00 1jjpo2i4b313g      3133159894      30997            0            5         .1

18-nov-2011 FRI 08:00 1jjpo2i4b313g      3133159894      81034            0            5          0

21-nov-2011 MON 00:00 1jjpo2i4b313g       790865878         16     323091.6     323128.3    36905.8

21-nov-2011 MON 01:00 1jjpo2i4b313g       790865878         29     349676.2     349713.7    48387.2

21-nov-2011 MON 02:00 1jjpo2i4b313g       790865878         35     339474.6     339509.2    34057.7

21-nov-2011 MON 03:00 1jjpo2i4b313g       790865878         37     340934.6     340970.2    35899.4

21-nov-2011 MON 04:01 1jjpo2i4b313g       790865878         38     333469.1     333503.9    35450.8

21-nov-2011 MON 05:00 1jjpo2i4b313g       790865878         35     347559.3     347595.2    35231.8

21-nov-2011 MON 06:00 1jjpo2i4b313g       790865878         32     340224.8       340260    35208.3

 I also like to use the query to track number of executions or
LIO per execution over a longer time frame to see if either the frequency or individual impact of the SQL is changing over time.
I can use the “hr24_filter” variable to do this, for example showing me all snapshots for hour “13” over a whole month.

Tips for Execution Plan Stability using Baseline
============================================================ 

Use the below queries to see the available execution plans and see which plan was running fine.

1.      @?/rdbms/admin/awrsqrpi.sql  ---> This will generate the html page for the required query based on the SQLID and its awr history.

(or )

2.      dbms_xplan.display_awr()

Ex: select * from TABLE(dbms_xplan.display_awr('47qjdv3ncanhr'));

(or )

3.       USING GRID Control 12c

 To gather the history of a SQL execution and the plans used during those runs, obtain the SQL Id to be evaluated, connect to the GRID Control 12c:

select the Targets/Databases -->Select the database -->Performance/SQL/Search SQL-->

Check AWR Snapshots -->Enter the SQL ID in the SQL ID filed / Search-->

Verify the executions and the different Hash Plans used.

 The ones with the Smallest Elapsed Times are the best execution Plans for the SQL.

• If the HASH Plan is still in the Cursor Cache it can be created as a baseline and instructed to run every time that SQL ID is loaded to the Shared Pool.

• If the HASH Plan is no longer in the Cursor Cache, then it is still possible to load the HASH Plan to a Sql Tuning Set and create a baseline from the STS and assign it the SQL ID as well. Take note of  the Snap ID (from the GRID SQL Search above) for the desired HASH Plan

HASH /SQL plan needed found in the Cursor Cache

Now you know which hash plan hash to be fixed. Now follow the below example. If the needed plan is found in the cursor cache then it is very simple to create a baseline and fixing the plan for the SQL query.

Ex: Determined the Hash Plan: 2601263939 is the best to run against the SQL ID: 47qjdv3ncanhr

1.Create the Baseline:

var v_num number;

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id =>'47qjdv3ncanhr',plan_hash_value => 2601263939);

OR

Example from Internet for script:

SQL> !cat create_baseline.sql

var ret number

exec :ret := dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value);

SQL> @create_baseline

Enter value for sql_id: 47qjdv3ncanhr

Enter value for plan_hash_value: 2601263939

2. Verify the baseline got created or not

=================================

SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                      PLAN_NAME                                              ENA    ACC     FIX

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

SQL_4bd90f15ef3c1f10           SQL_PLAN_4rq8g2rrms7sh3cc6a555       YES     YES     NO

To see all the details, this will create a file with all the baseline info.:

spool baseline_plan.txt

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_4bd90f15ef3c1f10',  format=>'basic'));

verify the spool file to confime the SQL ID and the HASH associated with it.

3.TO MODIFY A SQL PLAN BASELINE

var v_num number;

exec :v_num:=dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle =>'SQL_4bd90f15ef3c1f10',plan_name => 'SQL_PLAN_4rq8g2rrms7sh3cc6a555', attribute_name=> 'FIXED',  attribute_value  => 'YES');

Attributes

·enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.

· fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.

·  autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.

·  plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.

· description : Used to amend the SQL plan description, up to a maximum of 30 character.

   

Sometimes the required HASH / SQL plan  will not be present in the Cursor Cache, then you have to  load it from a AWR snapshots.

Steps are as below:

==============

To load plans to the cursor cache from awr snapshots:

1. -- Drop SQL Tuning Set (STS)

BEGIN

  DBMS_SQLTUNE.DROP_SQLSET(

    sqlset_name => 'SAMPLE_TUNING_SET');

END;

 2. -- Create SQL Tuning Set (STS)

 BEGIN

  DBMS_SQLTUNE.CREATE_SQLSET(

    sqlset_name => 'SAMPLE_TUNING_SET',

    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');

END;

3.-- Populate STS from AWR using a time duration when the desired plan was used.

Retrieve the begin Snap ID from the same session described in the GRID Contol above or by :   SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;

Note: Specify the sql_id in the basic_filter (other predicates are available, see desc dba_hist_snapshot) if necessary.

DECLARE

  cur sys_refcursor;

BEGIN

  OPEN cur FOR

    SELECT VALUE(P)

    FROM TABLE(

       dbms_sqltune.select_workload_repository(begin_snap=>1477, end_snap=>1478,basic_filter=>'sql_id = ''9n82zq1gkpg2t''',attribute_list=>'ALL')

              ) p;

     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'SAMPLE_TUNING_SET', populate_cursor=>cur);

  CLOSE cur;

END;

/

4. -- List out SQL Tuning Set contents to check we got what we wanted

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 => 'SAMPLE_TUNING_SET'));

5.-- Finally create the baseline from the STS:

DECLARE

my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

    sqlset_name => 'SAMPLE_TUNING_SET',

    basic_filter=>'plan_hash_value = ''1117073691'''

    );

END;

/

6.-- Verify the baseline got created and modify it if necessary

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

exec :v_num:=dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle =>'SQL_ab2ab5c194ee0fc8',plan_name => 'SQL_PLAN_aqapps6afw3y81722054c', attribute_name=> 'FIXED',  attribute_value  => 'YES');

7.-- Verify all details for the new Baseline:

spool baseline_plan.txt

select * from table(

    dbms_xplan.display_sql_plan_baseline(

        sql_handle=>'SQL_ab2ab5c194ee0fc8',

        format=>'basic'));