SYS_SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD YES YES select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_i
d=4
再次执行原目标SQL，并查看执行计划
zx@MYDB>select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where object_id=4;
OBJECT_NAME OBJECT_ID
------------------------------ ----------
TAB$ 4
zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0n5z3wmf8qpgn, child number 2
-------------------------------------
select /*+ no_index(t2 idx_t2) */ object_name,object_id from t2 where
object_id=4
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 335 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 30 | 335 (0)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T2@SEL$1
2 - SEL$1 / T2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
END_OUTLINE_DATA
*/
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]
2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_7bc3aw1b24guz24c6dbb6 used for this statement
50 rows selected.
从上述输出可以看出，原目标SQL已经走了新的执行计划(索引范围扫描)，而且Note部分也有提示“SQL plan baseline SQL_PLAN_7bc3aw1b24guz24c6dbb6 used for this statement”说明走了SPM。