Creating SQL_BASELINE on Problematic Queries.

Scenario : I have a sql query which is running in QA environment fine but it's not working properly in PROD environment.
Or else From years onwards query is working fine but started giving trouble from the last 2-3 days.

Well,  As a DBA everybody will face a scenario like this..!
For all this kind of scenarios the solution is creating SQL_BASELINE on those probematic SQL's.

But How...?
Here is the Answer :

Findout the snap intervals where your query is executed fine earlier from AWR using

select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode, q.CPU_TIME_TOTAL,q.BUFFER_GETS_TOTAL, q.DISK_READS_TOTAL
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.sql_id in ('fjadcdbwun2q9')
and q.snap_id = s.snap_id
and s.begin_interval_time between sysdate-14 and sysdate
order by s.snap_id desc;

O/P :-
6/4/2014 3:00:53.428 PM        6/4/2014 4:00:56.263 PM        8487    4129595089    fjadcdbwun2q9    1051222664    200    ALL_ROWS    3810000        16581    954
6/4/2014 3:00:53.428 PM        6/4/2014 4:00:56.263 PM        8487    4129595089    fjadcdbwun2q9    345059072    200    ALL_ROWS    5306540000    32366    3897
6/4/2014 2:00:50.381 PM        6/4/2014 3:00:53.428 PM        8486    4129595089    fjadcdbwun2q9    345059072    200    ALL_ROWS    5061430000    29239    2367
6/4/2014 2:00:50.381 PM        6/4/2014 3:00:53.428 PM        8486    4129595089    fjadcdbwun2q9    1051222664    200    ALL_ROWS    3810000        16581    954
6/4/2014 1:00:47.588 PM        6/4/2014 2:00:50.381 PM        8485    4129595089    fjadcdbwun2q9    345059072    200    ALL_ROWS    2907840000    28955    2288
6/4/2014 1:00:47.588 PM        6/4/2014 2:00:50.381 PM        8485    4129595089    fjadcdbwun2q9    1051222664    200    ALL_ROWS    3810000        16581    954
6/4/2014 11:00:41.248 AM    6/4/2014 12:00:45.552 PM    8483    4129595089    fjadcdbwun2q9    1051222664    200    ALL_ROWS    3810000        16581    954
6/4/2014 11:00:41.248 AM    6/4/2014 12:00:45.552 PM    8483    4129595089    fjadcdbwun2q9    345059072    200    ALL_ROWS    1364880000    13303    1508
6/4/2014 10:00:37.885 AM    6/4/2014 11:00:41.248 AM    8482    4129595089    fjadcdbwun2q9    345059072    200    ALL_ROWS    235030000    2347    0
6/4/2014 10:00:37.885 AM    6/4/2014 11:00:41.248 AM    8482    4129595089    fjadcdbwun2q9    1051222664    200    ALL_ROWS    3810000        16581    954

Ensure your intervals :

Beging_Snap_ID  : 8486
End_Snap_ID        : 8487
SQL_ID            : fjadcdbwun2q9
PLAN_HASH_VALUE    : 1051222664 (which we are going to force now to use this value bcoz with in the same interval we have 2 Plan_values where one is best)

Step -1 :First create STS(sql tuning set) for loading plan into it.

SQL> BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'MySTS01',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/

PL/SQL procedure successfully completed.

Step-2 : Populate our info into the STS using below

SQL> DECLARE
  2  cur sys_refcursor;
  3  BEGIN
  4  OPEN cur FOR
  5  SELECT VALUE(P)
  6  FROM TABLE(
  7  dbms_sqltune.select_workload_repository(begin_snap=>'8486', end_snap=>'8487',basic_filter=>'sql_id = ''fjadcdbwun2q9''',attribute_list=>'ALL')) p;
  8  DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
  9  CLOSE cur;
 10  END;
 11  /

PL/SQL procedure successfully completed.


#####Crossverify the info Baseline created or not.
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 => 'MySTS01'));


O/P :-

    4    EROCKS    762.186098    245.11    3127    1530    1778850    0    4    200    (DATASET)    345059072    fjadcdbwun2q9    (HUGECLOB)
    0    EROCKS    0            0        0        0        0        0    0    200    (DATASET)    1051222664    fjadcdbwun2q9    (HUGECLOB)
#########


Step-3 :  So,Now from the above we got 2 diff plan values in those AWR snap intevals,out of which we are going to force one using
SQL> DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'MySTS01',
basic_filter=>'plan_hash_value = ''1051222664'''
);
END;
/ 
PL/SQL procedure successfully completed.

Again we need to cross verify two options

SELECT * FROM dba_sql_plan_baselines ;
SELECT enabled,accepted FROM dba_sql_plan_baselines ;

O/P :-

YES        YES

############################################
Cross verify the problematic query , which is  using our desired plan_hash value or not, using explain plan.