outline的创建与使用

1.授权。赋予用户scott创建及执行outline的相应权限

SQL> GRANT CREATE ANY OUTLINE TO SCOTT;

Grant succeeded.

SQL> GRANT EXECUTE_CATALOG_ROLE TO SCOTT;

Grant succeeded.

2. 创建outline，以下创建一个名为test_outln的outline，期间执行了一条sql，该sql的执行计划被保存到test_outln中。

SQL> alter session set optimizer_mode=rule;                                          
                                                                                     
Session altered.                                                                     
                                                                                     
SQL> alter session set create_stored_outlines = TEST_OUTLN;                          
                                                                                     
Session altered.                                                                     
                                                                                     
SQL> select * from emp where empno=9000;                                             
                                                                                     
no rows selected                                                                     
                                                                                     
SQL> alter session set create_stored_outlines = false;                               
                                                                                     
Session altered.                            

3.使用outline。在会话级激活outline，并执行与上面相同的sql。                                        
                                                                                     
SQL> SELECT name, category, used FROM user_outlines;                                 
                                                                                     
NAME                           CATEGORY                       USED                   
------------------------------ ------------------------------ ------                 
SYS_OUTLINE_09080707363664016  TEST_OUTLN                     UNUSED                 
                                                                                     
SQL>  ALTER SESSION SET query_rewrite_enabled=TRUE;                                  
                                                                                     
Session altered.                                                                     
                                                                                     
SQL>  ALTER SESSION SET use_stored_outlines=TEST_OUTLN;                              
                                                                                     
Session altered.                                                                     
                                                                                     
SQL> select * from emp where empno=9000;                                             
                                                                                     
no rows selected                                                                     
                                                                                     
SQL> SELECT name, category, used FROM user_outlines;                                 
                                                                                     
NAME                           CATEGORY                       USED                   
------------------------------ ------------------------------ ------                 
SYS_OUTLINE_09080707363664016  TEST_OUTLN                     USED                   
                                                                                     
SQL> set autot traceonly explain                                                                     
                                                                                     
SQL> set lines 132                                                                   
SQL> select * from emp where empno=9000;                                                                             
                                                                                     
Execution Plan                                                                       
----------------------------------------------------------                           
Plan hash value: 2949544139                                                          
                                                                                     
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
                                                                                     
Predicate Information (identified by operation id):                                  
---------------------------------------------------                                  
                                                                                     
   2 - access("EMPNO"=9000)                                                          
                                                                                     
Note                                                                                 
-----                                                                                
   - outline "SYS_OUTLINE_09080707363664016" used for this statement
  
可以发现，outline已经生效。

另外，通过查询v$sql.OUTLINE_CATEGORY，也可以查看哪些sql使用了outline。

SQL> select sql_text,outline_sid,outline_category from v$sql where outline_CATEGORY is not null;

SQL_TEXT                                           OUTLINE_SID OUTLINE_CATEGORY
-------------------------------------------------- ----------- ----------------------------------------------------------------
select * from emp where empno=9000                             TEST_OUTLN

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-611588/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-611588/