利用hints控制outline

SQL> variable aa number;

SQL> exec :aa:=90;

PL/SQL 过程已成功完成。

SQL> select * from tt where id=:aa;

        ID MC                                                                  
---------- --------------------                                                
        90 1                                                                   

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT ptimizer=CHOOSE                                    
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TT'                              
   2    1     INDEX (UNIQUE SCAN) OF 'TT_PK' (UNIQUE)                          

 

Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
          2  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        422  bytes sent via SQL*Net to client                                  
        503  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

SQL> create or replace outline ol1 for category test_ol on
  2  select * from tt where id=:aa;

大纲已创建。

SQL> create or replace outline ol2 for category test_ol on
  2  select /*+FULL(tt)*/ * from tt where id=:aa;

大纲已创建。

SQL> desc dba_outlines
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 OWNER                                              VARCHAR2(30)
 CATEGORY                                           VARCHAR2(30)
 USED                                               VARCHAR2(9)
 TIMESTAMP                                          DATE
 VERSION                                            VARCHAR2(64)
 SQL_TEXT                                           LONG
 SIGNATURE                                          RAW(16)

SQL> col name for a10
SQL> select name,used from dba_outlines;

NAME       USED                                                                
---------- ---------                                                           
OL1        UNUSED                                                              
OL2        UNUSED                                                              

SQL> desc dba_outline_hints
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 OWNER                                              VARCHAR2(30)
 NODE                                               NUMBER
 STAGE                                              NUMBER
 JOIN_POS                                           NUMBER
 HINT                                               VARCHAR2(512)

SQL> col hint for a20
SQL> col name for a10
SQL> select name,node,stage,join_pos,hint from dba_outline_hints;

NAME             NODE      STAGE   JOIN_POS HINT                               
---------- ---------- ---------- ---------- --------------------               
OL1                 1          3          0 NO_EXPAND                          
OL1                 1          3          0 ORDERED                            
OL1                 1          3          0 NO_FACT(TT)                        
OL1                 1          3          1 INDEX(TT TT_PK)                    
OL1                 1          2          0 NOREWRITE                          
OL1                 1          1          0 NOREWRITE                          
OL1                 1          1          0 RULE                               
OL2                 1          3          0 NO_EXPAND                          
OL2                 1          3          0 ORDERED                            
OL2                 1          3          0 NO_FACT(TT)                        
OL2                 1          3          1 FULL(TT)                           

NAME             NODE      STAGE   JOIN_POS HINT                               
---------- ---------- ---------- ---------- --------------------               
OL2                 1          2          0 NOREWRITE                          
OL2                 1          1          0 NOREWRITE                          

已选择13行。

SQL> update outln.ol$hints
  2  set ol_name =decode(ol_name,
  3  'OL1','OL2',
  4  'OL2','OL1')
  5  where ol_name in ('OL1','OL2');

已更新13行。

SQL> update outln.ol$ ol1
  2  set hintcount = (select hintcount from outln.ol$ ol2
  3  where ol2.ol_name in ('OL1','OL2')
  4  and ol2.ol_name != ol1.ol_name)
  5  where ol1.ol_name in ('OL1','OL2');

已更新2行。

SQL> COMMIT;

提交完成。

SQL> select name,node,stage,join_pos,hint from dba_outline_hints;

NAME             NODE      STAGE   JOIN_POS HINT                               
---------- ---------- ---------- ---------- --------------------               
OL2                 1          3          0 NO_EXPAND                          
OL2                 1          3          0 ORDERED                            
OL2                 1          3          0 NO_FACT(TT)                        
OL2                 1          3          1 INDEX(TT TT_PK)                    
OL2                 1          2          0 NOREWRITE                          
OL2                 1          1          0 NOREWRITE                          
OL2                 1          1          0 RULE                               
OL1                 1          3          0 NO_EXPAND                          
OL1                 1          3          0 ORDERED                            
OL1                 1          3          0 NO_FACT(TT)                        
OL1                 1          3          1 FULL(TT)                           

NAME             NODE      STAGE   JOIN_POS HINT                               
---------- ---------- ---------- ---------- --------------------               
OL1                 1          2          0 NOREWRITE                          
OL1                 1          1          0 NOREWRITE                          

已选择13行。

SQL> alter session set use_stored_outlines=test_ol;

会话已更改。

SQL> select name,used from dba_outlines;

NAME       USED                                                                
---------- ---------                                                           
OL1        UNUSED                                                              
OL2        UNUSED                                                              

SQL> select * from tt where id=:aa;

        ID MC                                                                  
---------- --------------------                                                
        90 1                                                                   

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=25)           
   1    0   TABLE ACCESS (FULL) OF 'TT' (Cost=2 Card=1 Bytes=25)               

 

Statistics
----------------------------------------------------------                     
         69  recursive calls                                                   
          4  db block gets                                                     
         15  consistent gets                                                   
          0  physical reads                                                    
        612  redo size                                                         
        422  bytes sent via SQL*Net to client                                  
        503  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          2  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

SQL> select name,used from dba_outlines;

NAME       USED                                                                
---------- ---------                                                           
OL1        USED                                                                
OL2        UNUSED                                                              

 

10G以后可以用dbms_outln.CREATE_OUTLINE来创建:

BEGIN
  DBMS_OUTLN.create_outline(
    hash_value    => 921600969,
    child_number  => 0,
    category      => 'TEST_OUTLINE');
END;
/

SELECT sql_id,hash_value,outline_category,sql_text FROM v$sqlarea WHERE hash_value=921600969;

参看metalink
    Note:604022.1 How To Force A Query To Used Index Hint With Stored Outline
    Note:730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline
    Note:144194.1 Editing Stored Outlines in Oracle9i - an example

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

转载于:http://blog.itpub.net/730796/viewspace-605362/