Ref: http://amitpawardba.blogspot.com/2017/05/creating-sql-baseline-to-fix-query-with.html
Anothr link : http://expertoracle.com/2016/05/17/create-sql-baseline-for-sql_id/ (can be checked)
STEP 1: GENERATE ALL PREVIOUS HISTORY RUN DETAILS OF SQL_ID FROM AWR
break off sdate
set lines 2000
set linesize 2000
col SDATE format a10
col STIME format a10
select to_char(begin_interval_time,’YYYY/MM/DD’) SDATE,to_char(begin_interval_time,’HH24:MI’) STIME,s.snap_id, sql_id, plan_hash_value PLAN, ROUND(elapsed_time_delta/1000000,2) ET_SECS, nvl(executions_delta,0) execs, ROUND((elapsed_time_delta/decode(executions_delta,null,1,0,1,executions_delta))/1000000,2) ET_PER_EXEC, ROUND((buffer_gets_delta/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_lio, ROUND((CPU_TIME_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_cpu_ms, ROUND((IOWAIT_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_iow_ms, ROUND((DISK_READS_DELTA/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_pio, ROWS_PROCESSED_DELTA num_rows from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where s.sql_id = ‘&sql_id’ and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number order by sdate,stime;
Enter value for sql_id: 7hgwdax4mn20v SDATE STIME SNAP_ID SQL_ID PLAN ET_SECS EXECS ET_PER_EXEC AVG_LIO AVG_CPU_MS AVG_IOW_MS AVG_PIO NUM_ROWS ---------- ---------- ---------- ------------- ---------- ---------- ------------ ----------- -------------- ---------- ---------- ---------- ---------- 2016/09/13 11:00 23468 7hgwdax4mn20v 2844841640 1421.36 1 1421.36 4,301.0 152720.78 312.25 269 336 2016/09/13 15:00 23472 7hgwdax4mn20v 2844841640 1070.12 1 1070.12 4,291.0 126987.69 348.34 270 336 2016/09/13 17:00 23474 7hgwdax4mn20v 2844841640 1211.98 1 1211.98 4,374.0 183347.13 157.32 270 336 2016/09/13 18:00 23475 7hgwdax4mn20v 2844841640 596.91 0 596.91 116.0 45123.14 0 0 0 2016/09/14 01:00 23482 7hgwdax4mn20v 2844841640 1038.41 1 1038.41 4,309.0 123455.23 413.94 272 336 2016/09/14 02:00 23483 7hgwdax4mn20v 1355798266 193.82 1 193.82 2,748.0 76799.32 28.95 16 168 2016/09/14 03:00 23484 7hgwdax4mn20v 2844841640 .04 0 .04 .0 38 0 0 0 2016/09/14 03:00 23484 7hgwdax4mn20v 0 .04 0 .04 .0 38.99 0 0 0 2016/09/14 10:00 23491 7hgwdax4mn20v 2844841640 626.06 0 626.06 64.0 54658.69 0 0 0
/*In this scenario sql_id=7hgwdax4mn20v and plan_hash_value for good plan that we want to force is 1355798266.*/
Follow below steps to create sql baseline for sql_id
STEP 2: DROP SQL TUNING SET (STS) IF EXISTS
BEGIN DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'SQL_FOR_7hgwdax4mn20v'); END;
STEP 3: CREATE SQL TUNING SET
BEGIN DBMS_SQLTUNE.create_sqlset ( sqlset_name => 'SQL_FOR_7hgwdax4mn20v', description => 'SQL tuning set for 7hgwdax4mn20v'); END; /
/* Populate STS from AWR by specifying snapshot for desired plan which we found using above query.
In this scenario snap id’s are 23483 and 23484 and change plan_hash_value accordingly.*/
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
23483, -- begin_snap
23484, -- end_snap
q'<sql_id in ('7hgwdax4mn20v') and plan_hash_value in (1355798266)>', -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
100) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'SQL_FOR_7hgwdax4mn20v',
populate_cursor => l_cursor);
END;
/
orasolution 