SPM介绍
SPM是一种主动的稳定执行计划的手段,能够保证只有被验证过的执行计划才会被启用,当由于种种原因(如统计信息的变更)而导致目标SQL产生了新的执行计划后,这个新的执行计划并不会被马上启用,直到它已经被我们验证过其执行效率会比原先执行计划高才会被启用。
随着Oracle数据库版本的不段推进,其CBO的算法、功能也在一直不断进化和增加,所以同样的SQL有可能在新版本的Oralce数据库中执行效率更高,如果我们使用了SQL Profile(特别是使用了Manual类型的SQL Profile)来稳定目标SQL的执行计划,那就意味着可能失去了继续优化上述SQL的执行效率的机会。而SPM的推出可以说彻底解决了执行计划稳定性的问题,它既能主动地稳定执行计划,又保留了继续使用新的执行效率可能更高的执行计划的机会。
当启用了SPM后,每一个SQL都会存在对应的SQL Plan Baseline,这个SQL Plan Baseline里存储的就是该SQL的执行计划,如果一个SQL有多个执行计划,那么该SQL就可能会有多个SQL Plan Baseline,可以从DBA_SQL_PLAN_BASELINES中查看目标SQL所有的SQL Plan Baseline。
DBA_SQL_PLAN_BASELINES中的列ENABLED和ACCEPTED用来描述一个SQL Plan Baseline所对应的执行计划是否能被Oracle启用,只有ENABLED和ACCEPTED的值均为“YES”的SQL Plan Baseline所对应的执行计划才会被Oracle启用,如果一具SQL有超过1个以上的SQL Plan Baseline的ENABLED和ACCEPTED的值均为YES,则Oracle会从中选择成本值最小的一个所对应的执行坟墓来作为该SQL的执行计划。
在Oracle 11g及其以上的版本中,有如下两种方法可以产生目标SQL的SQL Plan Baseline。
自动捕获
手工生成/批量导入(批量导入尤其适用于Oracle数据库大版本的升级,它可以确保升级后原有系统所胡SQL的执行计划不会发生变化)
下面分别介绍如何自动捕获和手工的方式来产生SQL Plan Baseline。
实施步骤
自动捕获SQL Plan Baseline
参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES用于控制是否开启自动捕获SQL Plan Baseline,其默认值为FALSE,表示在默认情况下,Oracle并不会自动捕获SQL Plan Baseline。这个参数可以在session或系统级别动态修改。当修改为TRUE后,则Oracle会对上述参数影响范围内所有重复执行的SQL自动捕获其SQL Plan Baseline,并且针对目标SQL第一次捕获的SQL Plan Baseline的ENABLED和ACCEPTED的值均为“YES”。随后如果该SQL的执行计划发生了变更,则再次捕获到的SQL Plan Baseline的ENABLED的值依然为YES,但ACCEPTED的值变为了NO,这表示后续变更的执行计划虽然被捕获了,但Oracle不会将其作为该SQL的执行计划来执行,即此时Oracle会永远沿用该SQL第一次被捕获的SQL Plan Baseline所对应的执行计划(除非后续做了手工调整)。
参数OPTIMIZER_USE_SQL_PLAN_BASELINES用于控制是否启用SQL Plan Baseline,其默认值为TRUE,表示在默认情况下,Oracle在生成执行计划时就会启用SPM,使用已有的SQL Plan Baseline,这个参数也可以在session或系统级别动态修改。
查看目前数据库optimizer_capture_sql_plan_baselines和optimizer_use_sql_plan_baselines参数值
SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
禁掉当前session中SPM并同时开启自动捕获SQL Plan Baseline:
SQL> alter session set optimizer_use_sql_plan_baselines=FALSE;
Session altered.
SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;
Session altered.
1.创建测试表
SQL> create table test as select * from dba_objects;
Table created.
SQL> create index idx_object_id on test(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'test',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
SQL> explain plan for select object_id,object_name from test where object_id between 103 and 108;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 985375477
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 210 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 210 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 7 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)
从执行计划上看,走的是索引IDX_OBJECT_ID 上的索引范围扫描,因为SQL只执行了一次,所以Oracle不会自动捕获SQL Plan Baseline,DBA_SQL_PLAN_BASELINES中没有记录.
SQL> col sql_handle for a30
col plan_name for a30
col origin for a20
col sql_text for a70
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines;SQL> SQL> SQL> SQL>
no rows selected
再次执行上述SQL,因为重复执行该SQL Oracle才会自动捕获了这个SQL的SQL Plan Baseline
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SQL_012424e52a9ad18c SQL_PLAN_02914wnp9pncc0aa9b9ce AUTO-CAPTURE YES YES select object_id,object_name from test where object_id betw
een 103 and 108
现在将索引IDX_OBJECT_ID的聚簇因子修改为2500万,目的是为了能让SQL的执行计划变为对表T2的全表扫描。修改完后再执行上述SQL,并查看执行计划:
SQL> exec dbms_stats.set_index_stats(ownname=>'SCOTT',indname=>'IDX_OBJECT_ID',clstfct=>25000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_OBJECT_ID' and owner='SCOTT';
INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------
IDX_OBJECT_ID 25000000
SQL> explain plan for select object_id,object_name from test where object_id between 103 and 108;
Explained.
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 210 | 345 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST | 7 | 210 | 345 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_ID"<=108 AND "OBJECT_ID">=103)
13 rows selected.
从执行计划中可以看出该SQL的执行计划已经变为全表扫描。因为目标SQL已经重复执行且同时又产生了一个新的执行计划,所以现在Oracle就会自动捕获并创建这个新的执行计划所对应的SQL Plan Baseline了。从如下查询可以看出Oracle对新的执行计划产生了一个新的SQL Plan Baseline,其ENABLED的值依然为YES,但ACCEPTED的值变为了NO:
现在我们对当前Session关闭自动捕获SQL Plan Baseline并同时开启SPM,现在索引IDX_OBJECT_ID的聚簇因子依然为2500万,再次执行目标SQL,并查看执行计划:
SQL> alter session set optimizer_use_sql_plan_baselines=TRUE;
Session altered.
SQL> alter session set optimizer_capture_sql_plan_baselines=FALSE;
Session altered.
SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------
IDX_OBJECT_ID 25000000
SQL> select object_id,object_name from test where object_id between 103 and 108;
OBJECT_ID OBJECT_NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
6 rows selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 985375477
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 210 | 2005 (0)| 00:00:25 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 210 | 2005 (0)| 00:00:25 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 7 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)
Note
-----
- SQL plan baseline "SQL_PLAN_02914wnp9pncc0aa9b9ce" used for this statement
18 rows selected.
2.修改全全表扫描对应的SQL Plan Baseline的ACCEPTED值设为“YES”
在11gR2中,使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE和DBMS_SPM.ALTER_SQL_PLAN_BASELINE达到启用目标SQL新的执行计划的目的。
先用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE将新的执行计划(全表扫描)所对应的SQL Plan Baseline的ACCEPTED值设为“YES”:
SQL> var temp varchar2(1000);
SQL> exec :temp := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_012424e52a9ad18c',plan_name=>'SQL_PLAN_02914wnp9pncc97bbe3d0',verify=>'NO',commit=>'YES');
PL/SQL procedure successfully completed.
SQL> select :temp from dual;
:TEMP
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_012424e52a9ad18c
PLAN_NAME = SQL_PLAN_02914wnp9pncc97bbe3d0
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = NO
:TEMP
-------------------------------------------------------------------------------
COMMIT = YES
Plan: SQL_PLAN_02914wnp9pncc97bbe3d0
------------------------------------
Plan was changed to an accepted plan.
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
There were no SQL plan baselines that required processing.
从上面显示的内容看到如下信息:“Plan: SQL_PLAN_02914wnp9pncc97bbe3d0----Plan was changed to an accepted plan.”,这表明已经将新的执行计划(全表扫描)所对应的SQL Plan Baseline的ACCEPTED值设为YES.
确认SQL_PLAN_02914wnp9pncc97bbe3d0的SQL Plan Baseline是否ACCEPTED
再使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE将原先的执行计划(索引范围扫描)对应的SQL Plan Baseline的ENABLED的值设为NO:
SQL> exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_012424e52a9ad18c',plan_name=>'SQL_PLAN_02914wnp9pncc0aa9b9ce',attribute_name=>'enabled',attribute_value=>'NO');
PL/SQL procedure successfully completed.
SQL> select a.sql_handle,a.PLAN_NAME,a.origin,a.ENABLED,a.ACCEPTED,a.sql_text from dba_sql_plan_baselines a where a.sql_text like '%select object_id%'
2 ;
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------ --------- --------- --------------------------------------------------------------------------------
SQL_012424e52a9ad18c SQL_PLAN_02914wnp9pncc0aa9b9ce AUTO-CAPTURE NO YES select object_id,object_name from test where object_id between 103 and 108
SQL_012424e52a9ad18c SQL_PLAN_02914wnp9pncc97bbe3d0 AUTO-CAPTURE YES YES select object_id,object_name from test where object_id between 103 and 108
再次执行SQL
SQL> select object_id,object_name from test where object_id between 103 and 108;
OBJECT_ID OBJECT_NAME
---------- ------------------------------------------------------------------------------------------------
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
6 rows selected.
SQL> explain plan for select object_id,object_name from test where object_id between 103 and 108;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 210 | 345 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST | 7 | 210 | 345 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_ID"<=108 AND "OBJECT_ID">=103)
Note
-----
- SQL plan baseline "SQL_PLAN_02914wnp9pncc97bbe3d0" used for this statement
17 rows selected.
从上述显示可以看出,现在SQL的执行计划已经变为了全表扫描,我们要启用新的执行计划(全表扫描)的目的已经实现,Note部分也有了提示。
从上述测试结果可以看出,实际上我们可以轻易地在目标SQL的多个执行计划中切换,所以SPM确实是既能够主动地稳定执行计划,又保留了继续使用新的执行计划的机会,并且我们很容易就能启用新的执行计划。
手工生成SQL Plan Baseline:
手工生成目标SQL的SQL Plan Baseline其实非常简单,其核心就是调用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE。这里只讨论针对单个SQL的SQL Plan Baseline的手工生成。
之前介绍过用Manual类型的SQL Profile可以在不改变目标SQL的SQL文本的情况下调整其执行计划。实际上,用手工生成SQL Plan Baseline的方式也完全可以实现同样的目的,甚至会比使用Manual类型的SQL Profile更加简洁。
1.针对目标SQL使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成其初始执行计划所对应的SQL Plan Baseline.
SQL> select /*+ no_index(test idx_object_id) */ object_name,object_id from test where object_id=4;
OBJECT_NAME OBJECT_ID
----------- ----------
TAB$ 4
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9ygp7d5ba06mb, child number 0
-------------------------------------
select /*+ no_index(test idx_object_id) */ object_name,object_id from
test where object_id=4
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 345 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| TEST | 1 | 30 | 345 (1)| 00:00:05 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=4)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
43 rows selected.
SQL> select a.sql_handle,a.PLAN_NAME,a.origin,a.ENABLED,a.ACCEPTED,a.sql_text from dba_sql_plan_baselines a where a.sql_text like '%select /*+ no_index%';
no rows selected
SQL> var temp number
SQL> exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'9ygp7d5ba06mb',plan_hash_value=>1357081020);
PL/SQL procedure successfully completed.
SQL> select a.sql_handle,a.PLAN_NAME,a.origin,a.ENABLED,a.ACCEPTED,a.sql_text from dba_sql_plan_baselines a where a.sql_text like '%select /*+ no_index%';
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------ --------- --------- --------------------------------------------------------------------------------
SQL_15274114cd719983 SQL_PLAN_1a9u12m6r36c397bbe3d0 MANUAL-LOAD YES YES select /*+ no_index(test idx_object_id) */ object_name,object_id from test where
从上述显示目标SQL初始执行计划为全表扫描,sql_id和plan hash value可以从执行计划中找到,由于没有启用自动捕获SQL Plan Baseline,一开始没有查到目标SQL对应的SQL Plan Baseline,手工生成后,可以查到全表扫描对应的SQL Plan Baseline。
2.在原SQL中加入合适的Hint后,使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成新的执行计划所对应的SQL Plan Baseline
SQL> select /*+ index(test idx_object_id) */ object_name,object_id from test where object_id=4;
OBJECT_NAME OBJECT_ID
----------- ------------
TAB$ 4
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 6rrgx46jtukfg, child number 0
-------------------------------------
select /*+ index(test idx_object_id) */ object_name,object_id from test
where object_id=4
Plan hash value: 985375477
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 291 (100)| |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 30 | 291 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
Outline Data
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=4)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
46 rows selected.
SQL> exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'6rrgx46jtukfg',plan_hash_value=>985375477,sql_handle=>'SQL_15274114cd719983');
PL/SQL procedure successfully completed.
SQL> select a.sql_handle,a.PLAN_NAME,a.origin,a.ENABLED,a.ACCEPTED,a.sql_text from dba_sql_plan_baselines a where a.sql_text like '%select /*+ no_index%';
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------ --------- --------- --------------------------------------------------------------------------------
SQL_15274114cd719983 SQL_PLAN_1a9u12m6r36c30aa9b9ce MANUAL-LOAD YES YES select /*+ no_index(test idx_object_id) */ object_name,object_id from test where
SQL_15274114cd719983 SQL_PLAN_1a9u12m6r36c397bbe3d0 MANUAL-LOAD YES YES select /*+ no_index(test idx_object_id) */ object_name,object_id from test where
从上述输出可以看出把改写过的SQL的新的执行计划所对应的SQL Plan Baseline已经成功生成,而且所有手工生成的SQL Plan Baseline的ENABLED和ACCEPTED的值均为YES,这是和自动捕获的SQL Plan Baseline不一样的地方。
3.使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE删除步骤1中手工生成的原目标SQL的初始执行计划所对应的SQL Plan Baseline。
SQL> select /*+ no_index(test idx_object_id) */ object_name,object_id from test where object_id=4;
OBJECT_NAME OBJECT_ID
----------- ----------
TAB$ 4
SQL> explain plan for select /*+ no_index(test idx_object_id) */ object_name,object_id from test where object_id=4;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 985375477
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 291 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 30 | 291 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
2 - access("OBJECT_ID"=4)
Note
-----
- SQL plan baseline "SQL_PLAN_1a9u12m6r36c30aa9b9ce" used for this statement
18 rows selected.
从上述输出可以看出,原目标SQL已经走了新的执行计划(索引范围扫描),而且Note部分也有提示“SQL plan baseline “SQL_PLAN_1a9u12m6r36c30aa9b9ce” used for this statement”说明走了SPM。