PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
45 rows selected.
我们可以发现，我们现在走的是索引，对索引IDX_T2的索引范围扫描，因为只执行过一次，所以不会自动捕获其SQL Plan Baseline
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
no rows selected
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 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> 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'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8vtdn0kgytfxr, child number 1
-------------------------------------
select object_id,object_name from t2 where object_id between 103 and 108
Plan hash value: 2008370210
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | | | 3 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 7 | 210 | 3 (0)| 00:0
0:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 7 | | 2 (0)| 00:0
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:01 |
--------------------------------------------------------------------------------
------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T2@SEL$1
2 - SEL$1 / T2@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------