oracle OUTLN用户及使用OUTLINE固定SQL执行计划
Mar292013
今天在写巡检报告时，发现同事写的巡检脚本里把OUTLN用户当成普通用户来分析了。
OUTLN用户是ORACLE自带的用户，默认该用户下的表就是在SYSTEM表空间，这个用户存放ORACLE的SQL执行计划，如果要固定SQL的执行计划，就需要用到这个用户，默认这个用户是锁定的。
SQL> select USERNAME,ACCOUNT_STATUS from dba_users where username='OUTLN'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- OUTLN EXPIRED & LOCKED
在没有固定SQL执行计划时，OUTLN用户下面的表里是没有数据的。下面演示下怎么固定SQL的执行计划。
- 创建测试表，并建立索引
SQL> create table t_test as select * from dba_tables; 表已创建。 SQL> create index i_test_owner on t_test(owner); 索引已创建。
- 分别通过索引和全表扫描访问测试表
索引：
SQL> set autotrace on SQL> select table_name,TABLESPACE_NAME,STATUS from t_test where owner='STREAM'; TABLE_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ -------- T_IMAGES USERS VALID ALERT_DBDREAM_2 USERS VALID TEST USERS VALID SPERRORLOG USERS VALID TEST_1 USERS VALID
执行计划
---------------------------------------------------------- Plan hash value: 2051741444 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 5 | 285 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_TEST | 5 | 285 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_TEST_OWNER | 5 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------
全表扫描：
SQL> select /*+full(t_test)*/ table_name,TABLESPACE_NAME,STATUS from t_test where owner='STREAM'; TABLE_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ -------- T_IMAGES USERS VALID ALERT_DBDREAM_2 USERS VALID TEST USERS VALID SPERRORLOG USERS VALID TEST_1 USERS VALID
执行计划
---------------------------------------------------------- Plan hash value: 2796558804 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 285 | 20 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T_TEST | 5 | 285 | 20 (0)| 00:00:01 | ----------------------------------------------------------------------------
- 分别创建走索引和全表扫描的两条SQL的OUTLINE
SQL> create or replace outline ol_test_ind on select table_name,TABLESPACE_NAME,STATUS from t_test where owner='STREAM'; 大纲已创建。 SQL> create or replace outline ol_test_full on select /*+full(test)*/ table_name,TABLESPACE_NAME,STATUS from t_test where owner='STREAM'; 大纲已创建。
- 查询OUTLINE
SQL> select NAME,OWNER,TIMESTAMP,SQL_TEXT from dba_outlines; NAME OWNER TIMESTAMP SQL_TEXT ------------ ------- -------------- -------------------------------------------------- OL_TEST_IND STREAM 29-3月 -13 select table_name,TABLESPACE_NAME,STATUS from t_te st where owner='STREAM' OL_TEST_FULL STREAM 29-3月 -13 select /*+full(test)*/ table_name,TABLESPACE_NAME, STATUS from t_test where owner='STREAM'
- 使用OUTLINE
要使用固定好的执行计划，需要设置USE_STORED_OUTLINES参数值为TRUE。
SQL> alter session set USE_STORED_OUTLINES=TRUE; 会话已更改。 SQL> select table_name,TABLESPACE_NAME,STATUS from t_test where owner='STREAM'; TABLE_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ -------- T_IMAGES USERS VALID ALERT_DBDREAM_2 USERS VALID TEST USERS VALID SPERRORLOG USERS VALID TEST_1 USERS VALID
执行计划
----------------------------------------------------------
Plan hash value: 2051741444
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 3192 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 56 | 3192 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_OWNER | 23 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='STREAM')
Note
-----
- outline "OL_TEST_IND" used for this statement
可以看到已经使用OUTLINE，SQL执行计划成功被固定，也可以使用DBMS_OUTLN包来固定SQL的执行计划。
下面是DBMS_OUTLN包的描述：
SQL> DESC DBMS_OUTLN PROCEDURE CLEAR_USED 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN PROCEDURE CREATE_OUTLINE 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- HASH_VALUE NUMBER IN CHILD_NUMBER NUMBER IN CATEGORY VARCHAR2 IN DEFAULT PROCEDURE DROP_BY_CAT 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- CAT VARCHAR2 IN PROCEDURE DROP_COLLISION FUNCTION DROP_COLLISION_EXPACT RETURNS VARCHAR2 PROCEDURE DROP_EXTRAS FUNCTION DROP_EXTRAS_EXPACT RETURNS VARCHAR2 PROCEDURE DROP_UNREFD_HINTS FUNCTION DROP_UNREFD_HINTS_EXPACT RETURNS VARCHAR2 PROCEDURE DROP_UNUSED PROCEDURE EXACT_TEXT_SIGNATURES PROCEDURE REFRESH_OUTLINE_CACHE FUNCTION REFRESH_OUTLINE_CACHE_EXPACT RETURNS VARCHAR2 PROCEDURE UPDATE_BY_CAT 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- OLDCAT VARCHAR2 IN DEFAULT NEWCAT VARCHAR2 IN DEFAULT PROCEDURE UPDATE_SIGNATURES
下面演示使用DBMS_OUTLN包来固定SQL执行计划。首先，先跑一条SQL语句。
SQL> SELECT TABLE_NAME FROM T_TEST WHERE OWNER='STREAM'; TABLE_NAME ------------------------------ T_IMAGES ALERT_DBDREAM_2 TEST SPERRORLOG TEST_1
然后，从V$SQL视图中找到这条SQL的HASH_VALUE和CHILD_NUMBER的值。
SQL> SELECT HASH_VALUE,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT TABLE_NAME FROM%'; HASH_VALUE CHILD_NUMBER ---------- ------------ 3308915684 0
接下来就可以使用DBMS_OUTLN包来固定SQL的执行计划了。
SQL> EXEC DBMS_OUTLN.CREATE_OUTLINE(3308915684,0,'IND'); PL/SQL 过程已成功完成。
这种方式ORACLE会自动生成OUTLINE的名字。
SQL> select NAME,CATEGORY,SQL_TEXT from dba_outlines; NAME CATEGORY SQL_TEXT ------------------------------ ---------- -------------------------------------------------- OL_TEST_FULL DEFAULT select table_name,TABLESPACE_NAME,STATUS from t_te st where owner='STREAM' SYS_OUTLINE_13032916582825201 IND SELECT TABLE_NAME FROM T_TEST WHERE OWNER='STREAM' OL_TEST_IND DEFAULT select /*+full(test)*/ table_name,TABLESPACE_NAME, STATUS from t_test where owner='STREAM'
设置USE_STORED_OUTLINES参数是这个OUTLINE生效，注意这种方式USE_STORED_OUTLINES需要指定OUTLINE的CATEGORY名字。
SQL> ALTER SESSION SET USE_STORED_OUTLINES=IND; 会话已更改。
再次执行SQL就会使用到CATEGORY名字为IND的OUTLINE。
SQL> SELECT TABLE_NAME FROM T_TEST WHERE OWNER='STREAM'; TABLE_NAME ------------------------------ T_IMAGES ALERT_DBDREAM_2 TEST SPERRORLOG TEST_1
执行计划
----------------------------------------------------------
Plan hash value: 2051741444
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 1904 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 56 | 1904 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_OWNER | 23 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='STREAM')
Note
-----
- outline "SYS_OUTLINE_13032916582825201" used for this statement
oracle OUTLN用户及使用OUTLINE固定SQL执行计划：等您坐沙发呢！
发表评论
