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 ');
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27571661/viewspace-1787135/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/27571661/viewspace-1787135/
扫一扫
712
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
