--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 339 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 7 | 210 | 339 (1)| 00:00:05 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T2@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" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - filter(("OBJECT_ID"<=108 AND "OBJECT_ID">=103))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
42 rows selected.
此时走的是全表扫描。查看此时的基线
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE PLAN_NAME ORIGIN ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE YES
YES
select object_id,object_name from t2 where object_id between 103 and 108
SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE YES
NO
select object_id,object_name from t2 where object_id between 103 and 108
SQL_HANDLE PLAN_NAME ORIGIN ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
打开SPM，恢复默认设置
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.
SQL> alter system set optimizer_use_sql_plan_baselines=true;
System altered.
SQL> show parameter sql_plan;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T2 24000000
SQL> select object_id,object_name from t2 where object_id between 103 and 108;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
103
MIGRATE$
104
DEPENDENCY$
105
ACCESS$
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
106
I_DEPENDENCY1
107
I_DEPENDENCY2
108
I_ACCESS1
6 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));