sql最佳化：使用儲存提綱穩定sql執行計劃

0、建立測試表

create table t as select * from dba_objects;
create index id_inx on t (object_id)

1、建立儲存提綱

create or replace outline test_outline for category test on select * from t where object_id=6284; ---建立

select * from user_outlines; --檢視

select dbms_lob.substr(hint) from user_outline_hints where name='TEST_OUTLINE'; --檢視提示

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system

SQL> create or replace outline test_outline for category test on select * from t where object_id=6284;

Outline created

SQL> set linesize 400
SQL> desc user_outlines;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------------------------------------------------------------------------
NAME VARCHAR2(30) Y Name of the outline
CATEGORY VARCHAR2(30) Y Category to which the outline belongs
USED VARCHAR2(6) Y Flag indicating whether the outline has ever been used
TIMESTAMP DATE Y Timestamp at which the outline was created
VERSION VARCHAR2(64) Y Oracle Version that created the outline
SQL_TEXT LONG Y SQL text of the query
SIGNATURE RAW(16) Y Signature uniquely identifying the outline SQL text
COMPATIBLE VARCHAR2(12) Y Flag indicating whether the outline hints were compatible across migration
ENABLED VARCHAR2(8) Y Flag indicating whether the outline is enabled
FORMAT VARCHAR2(6) Y Flag indicating what hint format is used
MIGRATED VARCHAR2(12) Y Flag indicating whether the outline has been migrated to SQL plan baseline

SQL> select name,sql_text from user_outlines where name='TEST_OUTLINE';

NAME SQL_TEXT
------------------------------ --------------------------------------------------------------------------------
TEST_OUTLINE select * from t where object_id=6284

SQL>

SQL> select dbms_lob.substr(hint) from user_outline_hints where name='TEST_OUTLINE';

DBMS_LOB.SUBSTR(HINT)
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS

6 rows selected

SQL>

2、更改儲存提綱名稱

alter outline TEST_OUTLINE rename to test1;

3、啟用儲存提綱

啟用儲存提綱必須要有兩個條件，

1）是儲存提綱必須是enable狀態,

如：

alter outlineTEST_OUTLINE enable;

2）是透過初始化引數use_stored_outlines在系統級或者會話級設定為true或者類別名

如：

alter system set use_stored_outlines=TEST_OUTLINE; ---test為類別名

alter system set use_stored_outlines=true;

4、編輯儲存提綱

修改一個儲存提綱只能透過修改私有儲存提綱完成。

1）建立一個私有儲存提綱

explain plan for select * from t where object_id=6284;

select * from table(dbms_xplan.display(null,null,'ALL'));

SQL> explain plan for select * from t where object_id=6284;

Explained

SQL> select * from table(dbms_xplan.display(null,null,'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 801830182
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:0
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:0
|* 2 | INDEX RANGE SCAN | ID_INX | 1 | | 1 (0)| 00:0
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=6284)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."OWNER"[VARCHAR2,30], "T"."OBJECT_NAME"[VARCHAR2,128],
"T"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,19],
"T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7],
"T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7],
"T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1],
"T"."SECONDARY"[VARCHAR2,1], "T"."NAMESPACE"[NUMBER,22],
"T"."EDITION_NAME"[VARCHAR2,30]
2 - "T".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement (level=2)

37 rows selected

SQL>

建立儲存提綱

create or replace private outline p_outline_editing

on select * from t where object_id=6284;

SQL>
SQL> create or replace private outline p_outline_editing on select * from t where object_id=6284;

Outline created

SQL>

檢視私有儲存提綱

select hint#,hint_text from ol$hints;

SQL>
SQL>
SQL> select hint#,hint_text from ol$hints;

HINT# HINT_TEXT
---------- --------------------------------------------------------------------------------
1 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
2 OUTLINE_LEAF(@"SEL$1")
3 ALL_ROWS
4 DB_VERSION('11.2.0.1')
5 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
6 IGNORE_OPTIM_EMBEDDED_HINTS

6 rows selected

SQL>

2）編輯私有儲存提綱

update ol$hints set hint_text='FULL(@SEL$1 T)' where hint#=1; --修改為全表掃描
commit;

select * from ol$hints;

SQL>
SQL> update ol$hints set hint_text='FULL(@SEL$1 T)' where hint#=1;

1 row updated

SQL> commit;

Commit complete

SQL> select hint_text from ol$hints;

HINT_TEXT
--------------------------------------------------------------------------------
FULL(@SEL$1 T)
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS

6 rows selected

SQL>

3）同步私有儲存提綱

execute dbms_outln_edit.refresh_private_outline('P_OUTLINE_EDITING');

4）測試私有提綱

alter session set use_private_outlines=TRUE;

explain plan for select * from t where object_id=6284;

select * from table(dbms_xplan.display(null,null,'ALL'));

SQL> alter session set use_private_outlines=TRUE
2 ;

Session altered

SQL>
SQL> explain plan for select * from t where object_id=6284;

Explained

SQL> select * from table(dbms_xplan.display(null,null,'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 837 | 169K| 281 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 837 | 169K| 281 (1)| 00:00:04 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=6284)
Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------
1 - "T"."OWNER"[VARCHAR2,30], "T"."OBJECT_NAME"[VARCHAR2,128],
"T"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,19],
"T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7],
"T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7],
"T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1],
"T"."SECONDARY"[VARCHAR2,1], "T"."NAMESPACE"[NUMBER,22],
"T"."EDITION_NAME"[VARCHAR2,30]
Note
-----
- outline "P_OUTLINE_EDITING" used for this statement

34 rows selected

5）釋出私有提綱

create public outline P_OUTLINE_EDITING_T from private P_OUTLINE_EDITING;

SQL> create public outline P_OUTLINE_EDITING_T from private P_OUTLINE_EDITING;

Outline created

SQL>
SQL>

5、刪除儲存提綱

刪除一個儲存提綱

drop outline P_OUTLINE_EDITING_T ;

刪除一個列別儲存提綱

dbms_outln>drop_by_cat(cat=>'P_OUTLINE_EDITING_T ');

sql最佳化：使用儲存提綱穩定sql執行計劃

相關文章