使用SPM来稳定执行计划(3)

日期：2020-06-03 栏目：程序人生浏览：次

/*+
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")

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.

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> exec dbms_stats.set_index_stats(ownname=>'sys',indname=>'IDX_T2',clstfct=>24000000,no_invalidate=>false);

PL/SQL procedure successfully completed.

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'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8vtdn0kgytfxr, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 8vtdn0kgytfxr, CHILD_NUMBER: 1
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)

8 rows selected.
这里刚执行的sql执行计划就被age out了，继续执行。