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

Source: Internet
Author: User
Tags chr sorts

    • 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.
    • (
#!/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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.