Oracle使用STORED OUTLINES

DBA小记 2020-10-27

568

使用STORED OUTLINES（存储纲要）

Oracle 8i引入了STORED OUTLINES，它允许查询每次执行之前使用预先设定好的执行计划，与查询在哪运行无关。

首先，通过使用alter system/session命令告诉Oracle为将要运行的查询存储纲要以设置STORED OUTLINES。接着，执行这个查询以存储想要的信息（通常只设置在会话级别，这样可以不影响其他用户）。最后，如果可以接受这个执行计划，就可以把它存放在数据库中，并能让任何人使用它。

STORED OUTLINES可以解释为冻结语句的执行计划，从Oracle 11g开始，逐渐被SPM取代，而SPM是稳定执行计划，它允许选择新的执行计划，只要能够提高SQL语句的性能即可。如果同时存在STORED OUTLINES和SPM，STORED OUTLINES优先使用。

以下在Oracle 11.2.0.3版本测试

一、创建测试表和索引

SQL> create table test_outline as select * from dba_objects;
Table created.
SQL> set autotrace on;
SQL> set linesize 200
SQL> select OWNER, OBJECT_NAME from test_outline where object_id = '1061';
Execution Plan
----------------------------------------------------------
Plan hash value: 2163581145
----------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |    12 |  1152 |   299   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST_OUTLINE |    12 |  1152 |   299   (1)| 00:00:04 |
----------------------------------------------------------------------------------
复制

二、创建STORED OUTLINES

--sys执行授权

SQL> grant CREATE ANY OUTLINE to test;
复制

--语法：(手动创建outline)

CREATE [ OR REPLACE ]
   [ PUBLIC | PRIVATE ] OUTLINE [ outline ]
   [ FROM [ PUBLIC | PRIVATE ] source_outline ]
   [ FOR CATEGORY category ]
   [ ON statement ] ;
复制

在创建某条语句的纲要时，Oracle会将SQL语句的文本，执行计划和语句使用的hints存储在一个系统默认用户OUTLN的3个表OL$，OL$HINTS，OL$NODES上。

--查询数据字典

user_outlines或dba_outlines：

SQL> SELECT NAME, SQL_TEXT , USED FROM USER_OUTLINES; 
no rows selected
SQL> select NAME,OWNER,CATEGORY,SQL_TEXT, USED from dba_outlines;
no rows selected
SQL> create or replace outline outline_full for category mycate  on select OWNER, OBJECT_NAME from test_outline where object_id = '1061';
Outline created.
--再次查询dba_outlines;
SQL> col NAME for a12
SQL> col OWNER for a10
SQL> col CATEGORY for a10
SQL> col SQL_TEXT for a50
SQL> set linesize 200
SQL> select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED from dba_outlines;
NAME           OWNER      CATEGORY   USED   SQL_TEXT                                   ENABLED
------------ ---------- ---------- ------ -------------------------------------------------- --------
OUTLINE_FULL TEST      MYCATE         UNUSED select OWNER, OBJECT_NAME from test_outline where  ENABLED
                                object_id = '1061'
复制

--使用大纲固定执行计划：

alter system/session set use_stored_outlines=mycate;系统级或会话级别；
SQL> alter system set use_stored_outlines=mycate;
复制

--重新执行SQL

SQL> select OWNER, OBJECT_NAME from test_outline where object_id = '1061';
Execution Plan
----------------------------------------------------------
Plan hash value: 2163581145
----------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |   890 | 85440 |   299   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST_OUTLINE |   890 | 85440 |   299   (1)| 00:00:04 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=1061)
Note
-----
   - outline "OUTLINE_FULL" used for this statement
复制

已经使用存储纲要

--查看是否生效

SQL> select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED from dba_outlines;
NAME           OWNER      CATEGORY   USED   SQL_TEXT                                   ENABLED
------------ ---------- ---------- ------ -------------------------------------------------- --------
OUTLINE_FULL TEST      MYCATE         USED   select OWNER, OBJECT_NAME from test_outline where  ENABLED
                                object_id = '1061'
或者：
SQL> SELECT NAME, SQL_TEXT ,used
  FROM USER_OUTLINES;
  WHERE CATEGORY='MYCATE';
NAME           SQL_TEXT                                    USED
------------ -------------------------------------------------- ------
OUTLINE_FULL select OWNER, OBJECT_NAME from test_outline where      USED
           object_id = '1061'
SQL> select * from USER_OUTLINE_HINTS;
NAME               NODE      STAGE   JOIN_POS HINT
------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
OUTLINE_FULL            1        1          1 FULL(@"SEL$1" "TEST_OUTLINE"@"SEL$1")
OUTLINE_FULL            1        1          0 OUTLINE_LEAF(@"SEL$1")
OUTLINE_FULL            1        1          0 ALL_ROWS
OUTLINE_FULL            1        1          0 DB_VERSION('11.2.0.3')
OUTLINE_FULL            1        1          0 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
OUTLINE_FULL            1        1          0 IGNORE_OPTIM_EMBEDDED_HINTS
复制

--创建索引

判断纲要是否正常

SQL> create index idex_test on test_outline (object_id);
Index created.
SQL> select OWNER, OBJECT_NAME from test_outline where object_id = '1061';
Execution Plan
----------------------------------------------------------
Plan hash value: 2163581145
----------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |   890 | 85440 |   299   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST_OUTLINE |   890 | 85440 |   299   (1)| 00:00:04 |
----------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=1061)


Note
-----
   - outline "OUTLINE_FULL" used for this statement
复制

还是使用了存储纲要。

--停止使用outlines

SQL> alter system set USE_STORED_OUTLINES =false;

--再次查询

停止使用后看看执行计划走的什么？

SQL>select OWNER, OBJECT_NAME from test_outline where object_id = '1061';
Execution Plan
----------------------------------------------------------
Plan hash value: 194274487
--------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time         |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |       1 |      96 |       2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_OUTLINE |       1 |      96 |       2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDEX_TEST    |       1 |         |       1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=1061)
Note
-----
   - dynamic sampling used for this statement (level=2)
复制

优化器选择使用了索引范围扫描。

三、存储纲要的其他操作

--删除outlines操作

begin

dbms_outln.drop_by_cat(cat => 'MYCAT');

end;

--使建立的纲要不可用

begin

dbms_outln.clear_used(name => 'OUTLINE_FULL');

end;

--删除不可用的纲要

begin

dbms_outln.drop_unused;

end;

oracle

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle使用STORED OUTLINES

评论

相关阅读