How to specify an execution plan for an SQL statement by outline

• Create a test table

Connect with user Jyu, create test table sql> conn Jyu/jyu; Connected.sql> CREATE TABLE T (ID number, name VARCHAR2 (100)); Table created. sql> INSERT INTO T select Rownum,object_name from dba_objects;47391 rows created. Sql> commit; Commit complete. To create an index sql>. t_idx1 on t (ID), index created. Collect statistics sql> exec dbms_stats.gather_table_stats (' Jyu ', ' T ');P L/sql procedure successfully completed.

• Execution plan

View SQL statement Execution Plan sql> set autotrace traceonlysql> select * from t where id=1; Execution Plan----------------------------------------------------------plan hash value:3292636276--------------- -----------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------------------| 0 |        SELECT STATEMENT |     |    1 |     28 | 2 (0) |   00:00:01 | |  1 | TABLE ACCESS by INDEX rowid|     T |    1 |     28 | 2 (0) | 00:00:01 | |   * 2 | INDEX RANGE SCAN |     t_idx1 |       1 |     | 1 (0) | 00:00:01 |--------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------2-access ("id" =1) Note------outline "old_outln" used for this statementstatistics----------------------------------------------------------0 Recursive calls 0 DB block gets 4 consistent gets 0 p Hysical reads 0 Redo size 576 bytes sent via sql*net to client 492 bytes received via Sql*net fr OM Client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 ro The WS processedsql statement selects an execution plan that uses the index to use hint to specify that the statement uses a full table scan of the execution plan sql> explain plan for select/*+ Full (t) */* from t where I d=1; Explained.sql> select * FROM table (dbms_xplan.display); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----Plan Hash value:2153619298--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------| 0 |      SELECT STATEMENT |     |    1 |    28 | 50 (2) |00:00:01 | |  * 1 | TABLE ACCESS full|     T |    1 |    28 | 50 (2) | 00:00:01 |--------------------------------------------------------------------------predicate information ( identified by Operation ID):---------------------------------------------------plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----1-filter ("ID" =1) is selected.

Fixed execution plan with outline

Connect the database sql> conn/as sysdba Connected with SYSDBA. 2 SQL statements created outlinesql> alter session set Current_schema = Jyu; Session altered. sql> Create or replace outline old_outln for category Temp_plan on select * from T where Id=1;outline created. sql> Create or replace outline new_outln for category Temp_plan on select/*+ Full (t) */* from T where Id=1;outline CR Eated. outlinesql> of the interchange SQL statement create private outline Olfrom from Old_outln;outline created. sql> Create private outline Olto from New_outln;outline created. sql> Update ol$ Set hintcount= (select Hintcount from ol$ where ol_name= ' Olto ') where ol_name= ' Olfrom '; 1 row Updated.sql > Delete from ol$ where ol_name= ' Olto '; 1 row deleted. sql> Update ol$ set ol_name= ' Olto ' where ol_name= ' olfrom '; 1 row updated. Sql> commit; Commit complete. Sql> Execute dbms_outln_edit.refresh_private_outline (' Olto ');P L/sql procedure successfully completed. Sql> Create or REplace outline old_outln from the private olto for category Good_plan;outline created. sql> drop outline New_outln;outline dropped.

• Settings using the specified outlines

There are two ways to use outline in global settings: Use the ALTER system setting (expires after a database restart) sql> conn/as sysdbaconnected.sql> alter system set use_stored _outlines=good_plan; System altered. Mode two: Via Trigger settings (database restart is still valid) sql> Create or replace trigger Enable_outlines_trig--ref:how to enable Use_ Stored_outlines permanently (Doc ID 560331.1) after the startup on Databasebegin  execute immediate (' Alter system set use_s Tored_outlines=good_plan '); end;/

• Check the SQL statement execution plan

Sql> Conn jyu/jyuconnected.sql> Set autotrace traceonlysql> select * from t where id=1; Execution Plan----------------------------------------------------------plan hash value:2153619298--------------- -----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------| 0 |      SELECT STATEMENT |     |    1 |    28 | 50 (2) | 00:00:01 | |  * 1 | TABLE ACCESS full|     T |    1 |    28 | 50 (2) | 00:00:01 |--------------------------------------------------------------------------predicate information ( identified by Operation ID):---------------------------------------------------1-filter ("id" =1) Note------Outline "Old_outln" used for this statementstatistics----------------------------------------------------------recurs    Ive calls 145 db block gets 269 consistent gets 0 physical reads    576 Redo size 576 bytes sent via sql*net to client 492, bytes via received from client 2 sql*net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed

Outline takes effect, the SQL statement performs a full table scan.

• Fix_plan.sh (http://alexzeng.wordpress.com/2013/12/30/how-to-use-hinted-sql-to-fix-bad-sql-plan/)

#!/bin/Bashif[$#-lt3]; ThenCat<<eoffix given SQL plan using given SQLinch fileUsage:fix_plan.SHExamples:fix_plan.SH 716428968Good.sql alexeofexit1fiHash_value=$1Sql_file=$2Ol_owner=$3 Echo "hash_value: $HASH _value"Echo "sql_file: $SQL _file"Echo "ol_owner: $OL _owner"Echo ""#Create outline by Hash_valuefunctionCreate_ol_from_hashvalue {hash_value=$1Ol_name=$2Ol_owner=$3#generate Create outline sql#i didn't use Dbms_outln.create_outline, because it cannot create given name outline# and there's no hash value in V$sql and Dba_outlines to associate the# according to"How to Match a SQL Statement to a Stored Outline (Doc ID 743312.1)"Sqlplus-S"/As Sysdba">/tmp/tmp_$ol_name.sql <<eofset Feedback offset serveroutput on size unlimiteddeclarev_sqltext varchar2 (32000); Begin--Get SQL TextSelectDbms_lob.substr (Sql_fulltext,30000,1) into V_sqltext from v\ $sql where hash_value = $HASH _value and rownum=1; Dbms_output.put_line ('alter session Set Current_schema = $OL _owner;'); V_sqltext:='Create or replace outline $OL _name for category Temp_plan on'|| ChrTen) || V_sqltext | | ChrTen) ||';';  Dbms_output.put_line (V_sqltext); Dbms_output.put_line ('exit;'); end;/EOF Sqlplus-S"/As Sysdba"@/tmp/Tmp_$ol_name.sql} #Create outline from SQLfilefunctionCreate_ol_from_sqlfile {sql_file=$1Ol_name=$2Ol_owner=$3#generate Create outline SQLCat>/tmp/tmp_$ol_name.sql <<eofalter Session Set Current_schema=$OL _owner;create or replace outline $OL _name forcategory Temp_plan on 'Cat$SQL _file ' exit; EOF Sqlplus-S"/As Sysdba"@/tmp/Tmp_$ol_name.sql} #Exchange outlines, Makegood_sql plan to Good_plan category#ref:how to Edit a Stored Outline to use the plan from another Stored Outline (D OC ID730062.1)functionExchange_outline {OL1=$1OL2=$2Ol_owner=$3Sqlplus-S"/As Sysdba"<<Eofset Feedback offalter session set Current_schema=$OL _owner;create Private outline olfrom from $OL 1;create private outline Olto from $OL 2;update ol$ set Hintcount=(SelectHintcount from ol$ where ol_name='Olto') Where ol_name='Olfrom';d elete from ol$ where Ol_name='Olto'; update ol$ set Ol_name='Olto'where ol_name='Olfrom'; Commit;execute Dbms_outln_edit.refresh_private_outline ('Olto'Create or replace outline $OL 1 from private Olto forcategory Good_plan;drop outline $OL 2;exit; EOF} #display OutlinefunctionDisplay_outline {ol_name=$1Ol_owner=$2Sqlplus-S"/As Sysdba"<<Eofset pagesize +Linesize theSetLong 32000col hint format a55col join_pos format a45col owner format a12col name format a18col TS format A14col h Format999Col category format A12col sql_text format a80col used format A6SelectName, Sql_text, category, used, To_char (TIMESTAMP,'YY-MM-DD Hh24:mi') from Dba_outlineswhere name='$OL _name'and OWNER ='$OL _owner'; Selectol_name name, category, hint#, stage# stage, hint_text hint, join_pred join_pos from outln.ol\ $hints where Ol_name 
    ='$OL _name'ORDER by Ol_name, Hint#;exit; EOF} #mainfunctionEcho "1. Create outline Ol_$hash_value for SQL $HASH _value"create_ol_from_hashvalue $HASH _value ol_$hash_value $OL _ownerEcho "2. Create outline ol_temp for SQL in $SQL _file"create_ol_from_sqlfile $SQL _file ol_temp $OL _ownerEcho "3. Exchange outline Ol_$hash_value with Ol_temp, and drop ol_temp"exchange_outline ol_$hash_value ol_temp $OL _ownerEcho "4. Display final outline for SQL $HASH _value:ol_$hash_value in category Good_plan"display_outline ol_$hash_value $OL _owner