SQL Plan Management:
Build demo table & insert data
SQL> show user
USER is "ARCHIVE"
SQL> create table spm (col1 number,col2 varchar2(100));
Table created.
SQL> begin
for i in 1..10 loop
for j in 1..10 loop
for k in 1..1000 loop
insert into spm values (i,j);
insert into spm values (j,k);
end loop;
end loop;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from spm;
COUNT(*)
----------
200000
SQL> declare
2 aa char;
3 begin
4 for i in ascii('A')..ascii('Z') loop
5 aa:=chr(i);
6 insert into spm values (200,aa);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> begin
2 for i in 1..1000 loop
3 insert into spm values (100,'a');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> show parameter baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
Lets enable automatic capturing of the SQL_PLAN baselines
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
SQL> show parameter baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
Let's run a query twice so that optimizer will capture the SQL_PLAN for the query and will create the plan baseline.
You need to run the query at least twice to make optimizer capture the PLAN.
Attempt-1
SQL> set autot on exp
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 950742835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8334 | 50004 | 105 (3)| 00:00:02 |
| 1 | SORT ORDER BY | | 8334 | 50004 | 105 (3)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| SPM | 8334 | 50004 | 103 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=300)
Note
-----
- dynamic sampling used for this statement (level=2)
No Plan Yet...
SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled,accepted, fixed, autopurge from dba_sql_plan_baselines where sql_text
like 'select /*SPM_TEST*/%';
no rows selected
Attempt-2
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
Bingo..optimizer capture the plan & created the baseline…
SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled,accepted, fixed, autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%';
SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66 select /*SPM_TEST*/ * from spm where col1=300 order by col2 SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE YES YES NO YES
Now lets try to create different plan for the same query
SQL> create index spm_idx1 on spm(col1);
Index created.
SQL> exec dbms_stats.gather_table_stats('ARCHIVE','SPM');
PL/SQL procedure successfully completed.
SQL> select table_name,last_analyzed from user_tables where table_name='SPM';
TABLE_NAME LAST_ANAL
------------------------------ ---------
SPM 20-AUG-15
SQL> select index_name,last_analyzed from user_indexes where table_name='SPM';
INDEX_NAME LAST_ANAL
------------------------------ ---------
SPM_IDX1 20-AUG-15
Run the query again and verify whether it is using the new index or not?
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 950742835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 104 (2)| 00:00:02 |
| 1 | SORT ORDER BY | | 1 | 6 | 104 (2)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| SPM | 1 | 6 | 103 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=300)
Note
-----
- SQL plan baseline "SQL_PLAN_ahjc9da9z8g361a1720db" used for this statement
After the index creation there was second plan created but not yet accepted by the optimizer.
SQL> select signature, sql_handle,created,plan_name, origin, enabled,accepted, fixed, autopurge,sql_text from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%';
SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX AUT
---------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------- --- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66 20-AUG-15 06.13.29.000000 PM SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE YES YES NO YES
select /*SPM_TEST*/ * from spm where col1=300 order by col2
1.2125E+19 SYS_SQL_a845896a93f43c66 20-AUG-15 06.14.22.000000 PM SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE YES NO NO YES
select /*SPM_TEST*/ * from spm where col1=300 order by col2
SQL> select dbms_spm.evolve_sql_plan_baseline(sql_handle =>'SYS_SQL_a845896a93f43c66',time_limit => 10,verify => 'yes',commit =>'yes') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_A845896A93F43C66',TIME_LI
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Now verify whether the new plan is accepted or not.
You can verify the same information from the below:
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle =>'SYS_SQL_a845896a93f43c66'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_a845896a93f43c66
SQL text: select /*SPM_TEST*/ * from spm where col1=300 order by col2
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ahjc9da9z8g361a1720db Plan id: 437723355
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 950742835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 104 (2)| 00:00:02 |
| 1 | SORT ORDER BY | | 1 | 6 | 104 (2)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| SPM | 1 | 6 | 103 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=300)
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ahjc9da9z8g3693e18217 Plan id: 2481029655
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1694116892
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 6 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SPM | 1 | 6 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SPM_IDX1 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=300)
46 rows selected.
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines
where plan_name='SQL_PLAN_ahjc9da9z8g3693e18217';
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66 SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE YES YES YES
SQL> select signature, sql_handle,created,plan_name, origin, enabled,accepted, fixed, autopurge,sql_text from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%';
SIGNATURE SQL_HANDLE CREATED PLAN_NAME ORIGIN ENA ACC FIX AUT
---------- ------------------------------ --------------------------------------------------------------------------- ------------------------------ -------------- --- --- --- ---
SQL_TEXT
--------------------------------------------------------------------------------
1.2125E+19 SYS_SQL_a845896a93f43c66 20-AUG-15 06.13.29.000000 PM SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE YES YES NO YES
select /*SPM_TEST*/ * from spm where col1=300 order by col2
1.2125E+19 SYS_SQL_a845896a93f43c66 20-AUG-15 06.14.22.000000 PM SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE YES YES NO YES
select /*SPM_TEST*/ * from spm where col1=300 order by col2
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1694116892
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 6 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SPM | 1 | 6 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SPM_IDX1 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=300)
Note
-----
- SQL plan baseline "SQL_PLAN_ahjc9da9z8g3693e18217" used for this statement
From the above it is evident that the optimizer has accepted the better plan(SQL_PLAN_ahjc9da9z8g3693e18217) that was available.
Drop SQL_PLAN Baselines:
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines
where sql_handle='SYS_SQL_a845896a93f43c66';
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66 SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE YES YES YES
1.2125E+19 SYS_SQL_a845896a93f43c66 SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE YES YES YES
SQL> declare
abc pls_integer;
begin
abc:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_a845896a93f43c66');
end;
/
PL/SQL procedure successfully completed.
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_handle='SYS_SQL_a845896a93f43c66';
no rows selected
No comments:
Post a Comment