SQL tuning için Outline ve SQL plan baseline kullanımı

/* Öncelikle SQL 'in SQL_id si bulunur */

/* Sorunlu SQL i çalıştıran sessionlar incelenir */

SELECT sid,
username,
action,
event,
sql_exec_start,
sql_child_number,
plsql_object_id
FROM v$session
WHERE sql_id = :hsid;

/* SQL hakkında genel bilgi ve Full text alınır */

select * from v$sqlarea where sql_id=:sql;

SELECT sql_id,
child_number,
buffer_gets,
executions,
TRUNC (buffer_gets / executions),
plan_hash_value
FROM v$sql
WHERE sql_id = :hsid
ORDER BY executions DESC;


/* Child number'e göre exec planlar teker teker incelenir */

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id,:child_number));


/* Eğer childları varsa , exec basina buffer get leri düşükse ve bunlar kullanılmıyor ise
Bu exec planlar kullanıma alınır.

Eğer 10g de iseniz buffer get i düşük olan SQL plan OUTLINE ile fix edilir.
Burada daha önce'den
aşağıdaki iki adımın yapılış olması lazımdır.
*/


GRANT CREATE ANY OUTLINE TO username;
Alter system set USE_STORED_OUTLINES=FIXPLAN;

/* Outline yaratarak Planın fixlenmesi */


BEGIN
DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
category => 'FIXPLAN');
END;
/
/*

Eğer 11g de iseniz SQL Plan Baseline ile Fix etmeniz daha iyidir.
İster Cursor cache 'deki exec planı isterseniz SQL 'in AWR history sindeki planını yükliyebilirisinz

CURSOR CACHA'den SQL plan baseline ile fixleme

Cursor cache'deki SQL_id nin biraz önce bakılan exec basina buffer get'i az olan plan_hash_value seçilerek
SQL plan baseline fixlenir.

*/


DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id=>'88hcbmn31jt4v',plan_hash_value =>2213722297 , FIXED=>'YES');
end ;


/*
eğer Cursor Cache'de Child lar yoksa AWR history'de SQL 'in exec başına buffer get' ine bakilir.
Eğer buradan değer dönerse buradadan alinan SQL plan ile önce SQL tuning Set (STS) oluşturulur sonra
bu STS içindeki plan SQL Plan baseline içine fixlenir.

*/

-- AWR rapor içinde SQL i arama

SELECT a.snap_id,
b.begin_interval_time,
a.plan_hash_value,
BUFFER_GETS_DELTA,
EXECUTIONS_DELTA ,
TRUNC (buffer_gets_delta / executions_delta) gets_per_exec,
ROWS_PROCESSED_DELTA
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE a.sql_id = :sql_id
AND a.snap_id = b.snap_id
AND EXECUTIONS_DELTA > 0
ORDER BY a.snap_id desc;

-- SQL ler ile historic olarak exec plan incelenir. Kullanılacak SQL_ID ve SQL_PLAN_HASH_VALUE seçilmelidir

select * from dba_hist_sql_plan where sql_id = :sql_id ;

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(:sql_id));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id =>'bpb99ctkcw5zx' ,plan_hash_value =>3385879679 ));

select plan_hash_value , count(*) from dba_hist_sqlstat where sql_id = :sql_id group by plan_hash_value order by 2 desc;

-- uygun exec planı STS içine yüklemek için önce bir STS yaratılır.

BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'STS_2',
description => 'STS_for_plan_change');
END;
/


-- STS içine yükleme yapılır

DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
begin_snap => 19075,
end_snap => 19290,
basic_filter => 'sql_id='||CHR(39)||'87xfz4gm9c0ms'||CHR(39)||' and plan_hash_value=3359181701',
attribute_list => 'ALL')
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'STS_2',
populate_cursor => l_cursor);
END;
/

-- Yukleme yapılmışmı diye buradan bakılır

select * from DBA_SQLSET where name ='STS_1';
Select * from DBA_SQLSET_STATEMENTS where sqlset_name ='STS_2';
select * from DBA_SQLSET_plans where sqlset_name ='STS_2';

SELECT * FROM table(DBMS_XPLAN.DISPLAY_SQLSET('STS_2',:sql_id));


-- SQL tuning Set içinden Sql Plan baseline alınıp SQL in fix edilmesi

DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name=>'STS_2',basic_filter => 'sql_id='||CHR(39)||'87xfz4gm9c0ms'||CHR(39)||' and plan_hash_value=3359181701',FIXED=>'YES');
END;
/

-- SQL plan baseline a bakılır

SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines;