SQL Plan Baseline çalışmaları

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.