Manually loading the SQL Plan Baselines
SQL> show parameter baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
SQL> show parameter baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
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)
SQL> set long 10000
SQL> declare
2 abc pls_integer;
3 begin
4 abc:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'fydaq3sch82s7');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%'
2
SQL> ;
1* select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%'
SQL> /
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66 SQL_PLAN_ahjc9da9z8g361a1720db MANUAL-LOAD YES YES YES
SQL> drop index spm_idx1;
Index dropped.
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 |
---------------------------------------------------------------------------
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
SQL> create index spm_idx on spm(col1);
Index created.
SQL> exec dbms_stats.gather_table_stats('ARCHIVE','SPM');
PL/SQL procedure successfully completed.
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;
no rows selected
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%';
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66 SQL_PLAN_ahjc9da9z8g360281aedb AUTO-CAPTURE YES NO YES
1.2125E+19 SYS_SQL_a845896a93f43c66 SQL_PLAN_ahjc9da9z8g361a1720db MANUAL-LOAD YES YES YES
SQL> show parameter baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_a845896a93f43c66') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_A845896A93F43C66')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_a845896a93f43c66
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_ahjc9da9z8g360281aedb
------------------------------------
Plan was verified: Time used .75 seconds.
Plan passed performance criterion: 187.76 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): 74.08 .013 5698.46
CPU Time(ms): 15.111 0
Buffer Gets: 374 2 187
Physical Read Requests: 370 0
Physical Write Requests: 0 0
Physical Read Bytes: 3031040 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%';
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66 SQL_PLAN_ahjc9da9z8g360281aedb AUTO-CAPTURE YES YES YES
1.2125E+19 SYS_SQL_a845896a93f43c66 SQL_PLAN_ahjc9da9z8g361a1720db MANUAL-LOAD YES YES YES
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_ahjc9da9z8g360281aedb Plan id: 42053339
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3250893592
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 168 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 28 | 168 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SPM | 28 | 168 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SPM_IDX | 28 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=300)
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ahjc9da9z8g361a1720db Plan id: 437723355
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 950742835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 168 | 104 (2)| 00:00:02 |
| 1 | SORT ORDER BY | | 28 | 168 | 104 (2)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| SPM | 28 | 168 | 103 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=300)
46 rows selected.
SQL> SELECT parameter_name, parameter_value FROM dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
