USR master'da yapildi.
create table a1 as select * from dba_objects;
select object_name from a1 where object_name='EVENT';
select sql_id,substr(sql_text,1,40) from v$sql where sql_text like '%select object_name from a1%';
21z70uk6vu3wy
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('21z70uk6vu3wy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 21z70uk6vu3wy, child number 0
-------------------------------------
select object_name from a1 where object_name='EVENT'
Plan hash value: 3500360281
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 228 (100)| |
|* 1 | TABLE ACCESS FULL| A1 | 13 | 858 | 228 (2)| 00:00:03 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='EVENT')
Note
-----
- dynamic sampling used for this statement (level=2)
select sql_id,plan_hash_value from v$sql where sql_id='21z70uk6vu3wy';
SQL_ID PLAN_HASH_VALUE
------------- ---------------
21z70uk6vu3wy 3500360281
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id=>'21z70uk6vu3wy',plan_hash_value =>3500360281);
end;
/
SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines where sql_handle='SQL_9f67def84efc8a66';
SQL_HANDLE SQL_TEXT ENABLED ACCEPTED FIXED
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES YES NO
SQL> create index xxx on a1(object_name);
Index created.
SQL_ID PLAN_HASH_VALUE SQL_TEXT CHILD_NUMBER BUFFER_GETS EXECUTIONS TRUNC(BUFFER_GETS/EXECUTIONS)
2ysg1zwhcx86c 3924418374 select * from v$sql where sql_text like '%select object_name from a1%' 0 43 1 43
9mv9mar4b5hj4 3924418374 select sql_id from v$sql where sql_text like '%select object_name from a1%' 0 89 1 89
7bvxhvmpkk0s7 3924418374 select sql_id,substr(sql_text,1,40) from v$sql where sql_text like '%select object_name from a1%' 0 3 3 1
931nzh21mf3fj 3670124693 select object_name from a1 where object_name='CABL' 0 132 1 132
21z70uk6vu3wy 3500360281 select object_name from a1 where object_name='EVENT' 0 1347 1 1347
21z70uk6vu3wy 3500360281 select object_name from a1 where object_name='EVENT' 2 1347 1 1347
dt2qa1cygyyx1 3924418374 select sql_id,plan_hash_value,sql_text,child_number,buffer_gets,executions,trunc(buffer_gets/executions) from v$sql where sql_text like '%select object_name from a1%' 0 3 1 3
4pngumv8czhv5 3924418374 select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%select object_name from a1%' 0 3 1 3
select object_name from a1 where object_name='CABL';
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('931nzh21mf3fj',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 931nzh21mf3fj, child number 0
-------------------------------------
select object_name from a1 where object_name='CABL'
Plan hash value: 3670124693
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | INDEX RANGE SCAN| XXX | 6 | 396 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='CABL')
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_9f67def84efc8a66'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_9f67def84efc8a66
SQL text: select object_name from a1 where object_name='EVENT'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9ytyyz17gt2m66b488122 Plan id: 1799913762
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3500360281
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 945 | 62370 | 228 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| A1 | 945 | 62370 | 228 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='EVENT')
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9ytyyz17gt2m6d98c5d8a Plan id: 3649854858
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3670124693
-------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 945 | 62370 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| XXX | 945 | 62370 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='EVENT')
43 rows selected.
SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines where sql_handle='SQL_9f67def84efc8a66';
SQL_HANDLE SQL_TEXT ENABLED ACCEPTED FIXED
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES YES NO
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES NO NO
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
( sql_id=>'931nzh21mf3fj',plan_hash_value =>'3924418374',SQL_HANDLE => 'SQL_9f67def84efc8a66');
end;
SQL PLAN BASELINE'da iki tane plan var. Bu durumda evaluate edilmesi gerekiyor. Index yaratildiktan sonra geleb SQL ler SQLPLANBASELINE'da
ACCEPTED NO olan kayıt ekliyor. bu durumda ise evaluate edilerek plan düzeltiliyor.
SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(SQL_HANDLE => 'SQL_9f67def84efc8a66')
FROM dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9F67DEF84EFC8A66')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_9f67def84efc8a66
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9F67DEF84EFC8A66')
--------------------------------------------------------------------------------
COMMIT = YES
Plan: SQL_PLAN_9ytyyz17gt2m6d98c5d8a
------------------------------------
Plan was verified: Time used 1.879 seconds.
Plan passed performance criterion: 406.67 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9F67DEF84EFC8A66')
--------------------------------------------------------------------------------
Rows Processed: 3 3
Elapsed Time(ms): 13.779 .034 405.26
CPU Time(ms): 20 0
Buffer Gets: 1218 3 406
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9F67DEF84EFC8A66')
--------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines where sql_handle='SQL_9f67def84efc8a66';
SQL_HANDLE SQL_TEXT ENABLED ACCEPTED FIXED
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES YES NO
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES YES NO
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('21z70uk6vu3wy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 21z70uk6vu3wy, child number 0
-------------------------------------
select object_name from a1 where object_name='EVENT'
Plan hash value: 3670124693
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | INDEX RANGE SCAN| XXX | 3 | 198 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='EVENT')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline SQL_PLAN_9ytyyz17gt2m6d98c5d8a used for this statement
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
23 rows selected.
Wednesday, January 18, 2012
Monday, January 16, 2012
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;
/* 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;
Subscribe to:
Posts (Atom)
