HOW TO ENABLE AND DISABLE SPM IN ORACLE
If a auto baseline is already enabled in database and if that baseline is always used for a sql from the management base, then that may cause interruptions for a user defined query.
I expect optimizer to use result cache for a query but as the baseline is accepted for this query with a different plan,
SQL> set autotrace traceonly
SQL> SELECT /*+ RESULT_CACHE */ count(*) from dspm;
Execution Plan
----------------------------------------------------------
Plan hash value: 3894984268
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1569 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| DSPM | 219K| 1569 (1)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline "SQL_PLAN_bmkkb8jb4c4yx9671ac91" used for this statement
To disable SPM for the query, i use the below procedure to do it.
SQL> var xnum number;
SQL> EXEC :xnum := dbms_spm.alter_sql_plan_baseline(plan_name=>'SQL_PLAN_bmkkb8jb4c4yx9671ac91',attribute_name=>'ENABLED',attribute_value=>'NO');
PL/SQL procedure successfully completed.
To enable SPM for the query, i use the below procedure to do it.
SQL> var xnum number;
SQL> EXEC :xnum := dbms_spm.alter_sql_plan_baseline(plan_name=>'SQL_PLAN_bmkkb8jb4c4yx9671ac91',attribute_name=>'ENABLED',attribute_value=>'YES');