create a profile from hint

在oracle 日常运维中，在数据迁移后（或者数据库升级后），复杂的sql语句导致极差的性能（sql语句编写很烂是关键原因），往往会出现极差的sqlplan，我们建议用新的profile来fix 这种问题，下面是创建profile的方法：

1)Run the SQL first with a hint to get the outline data of the good plan:

SELECT /*+ INDEX(L OE_ORDER_LINES_U1)INDEX(H OE_ORDER_HEADERS_U1)*/H.ORDER_NUMBER SO_NO,L.LINE_NUMBER || '.' || L.SHIPMENT_NUMBER SO_LINE,I.SEGMENT1 ITEM_NO,H.ATTRIBUTE4 HUB_SUBINV,I.COST_OF_SALES_ACCOUNT COGS,-1 * T.TRANSACTION_QUANTITY TXN_QTY,T.TRANSACTION_UOM TXN_UOM,T.INVENTORY_ITEM_ID ITEM_ID,T.ORGANIZATION_ID,T.TRANSACTION_ID TXN_IDFROM MTL_MATERIAL_TRANSACTIONS T,OE_ORDER_HEADERS_ALL H,OE_ORDER_LINES_ALL L,MTL_SYSTEM_ITEMS_B IWHERE T.TRANSACTION_TYPE_ID = 33AND H.HEADER_ID = L.HEADER_IDAND I.ORGANIZATION_ID = T.ORGANIZATION_IDAND I.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_IDAND T.TRX_SOURCE_LINE_ID = L.LINE_IDAND T.ATTRIBUTE1 IS NULLAND H.ATTRIBUTE4 IS NOT NULL

2)Get information from v$sql

SELECT sql_id, plan_hash_value, child_number,executions, is_shareableFROM v$sqlWHERE sql_text LIKE 'SELECT /*+ INDEX(L OE_ORDER_LINES_U1)%';

3)Get the SQL plan and Outline Data using SQL_ID and child number you found in step 2:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'5981qk4vtzwhh', cursor_child_no=>0, format=>'ADVANCED'));

Please paste the result here.
Outline Data:

/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('11.1.0.7')DB_VERSION('11.1.0.7')OPT_PARAM('_b_tree_bitmap_plans' 'false')OPT_PARAM('_fast_full_scan_enabled' 'false')OPT_PARAM('_index_join_enabled' 'false')OPT_PARAM('_nlj_batching_enabled' 0)ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("MTL_MATERIAL_TRANSACTIONS"."TRANSACTION_TYPE_ID" "MTL_MATERIAL_TRANSACTIONS"."ATTRIBUTE1"))INDEX_RS_ASC(@"SEL$1" "L"@"SEL$1" ("OE_ORDER_LINES_ALL"."LINE_ID"))INDEX_RS_ASC(@"SEL$1" "H"@"SEL$1" ("OE_ORDER_HEADERS_ALL"."HEADER_ID"))INDEX(@"SEL$1" "I"@"SEL$1" ("MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"))LEADING(@"SEL$1" "T"@"SEL$1" "L"@"SEL$1" "H"@"SEL$1" "I"@"SEL$1")USE_NL(@"SEL$1" "L"@"SEL$1")USE_NL(@"SEL$1" "H"@"SEL$1")USE_NL(@"SEL$1" "I"@"SEL$1")NLJ_PREFETCH(@"SEL$1" "I"@"SEL$1")END_OUTLINE_DATA*/

4)create profile from outline data:
SPO sqlt_s36096_p3830036895_sqlprof.log;

SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;WHENEVER SQLERROR EXIT SQL.SQLCODE;VAR signature NUMBER;DECLAREsql_txt CLOB;h SYS.SQLPROF_ATTR;BEGINsql_txt := q'[select /*+ parallel(t3,8) parallel(t4,8) parallel(t5,8) leading(t1)*/count(distinct t1.username),count(distinct t3.username),count(distinct t4.username),count(distinct t5.username)from (select /*+ parallel(t1,8) parallel(t2,8) */distinct t1.workd, t1.usernamefrom bip.og_webfish_login_d01 t1, bip.dim_webfish_roles t2where t1.workd = '20111105'and t1.username = t2.bindusernameand t1.workd = t2.registerdate) t1,bip.og_webfish_login_d01 t3,bip.og_webfish_login_d01 t4,bip.og_webfish_login_d01 t5where t3.workd(+) = to_char(to_date(t1.workd, 'yyyymmdd') + 1, 'yyyymmdd')and t4.workd(+) = to_char(to_date(t1.workd, 'yyyymmdd') + 2, 'yyyymmdd')and t5.workd(+) = to_char(to_date(t1.workd, 'yyyymmdd') + 6, 'yyyymmdd')and t1.username = t3.username(+)and t1.username = t4.username(+)and t1.username = t5.username(+)]';h:= SYS.SQLPROF_ATTR(q'[BEGIN_OUTLINE_DATA]',q'[IGNORE_OPTIM_EMBEDDED_HINTS]',q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]',q'[DB_VERSION('11.2.0.2')]',q'[ALL_ROWS]',q'[OUTLINE_LEAF(@"SEL$F5BB74E1")]',q'[MERGE(@"SEL$2")]',q'[OUTLINE(@"SEL$1")]',q'[OUTLINE(@"SEL$2")]',q'[FULL(@"SEL$F5BB74E1" "T2"@"SEL$2")]',q'[FULL(@"SEL$F5BB74E1" "T1"@"SEL$2")]',q'[FULL(@"SEL$F5BB74E1" "T3"@"SEL$1")]',q'[FULL(@"SEL$F5BB74E1" "T4"@"SEL$1")]',q'[FULL(@"SEL$F5BB74E1" "T5"@"SEL$1")]',q'[LEADING(@"SEL$F5BB74E1" "T2"@"SEL$2" "T1"@"SEL$2" "T3"@"SEL$1" "T4"@"SEL$1" "T5"@"SEL$1")]',q'[USE_HASH(@"SEL$F5BB74E1" "T1"@"SEL$2")]',q'[USE_HASH(@"SEL$F5BB74E1" "T3"@"SEL$1")]',q'[USE_HASH(@"SEL$F5BB74E1" "T4"@"SEL$1")]',q'[USE_HASH(@"SEL$F5BB74E1" "T5"@"SEL$1")]',q'[PQ_DISTRIBUTE(@"SEL$F5BB74E1" "T1"@"SEL$2" BROADCAST NONE)]',q'[PQ_DISTRIBUTE(@"SEL$F5BB74E1" "T3"@"SEL$1" HASH HASH)]',q'[PQ_DISTRIBUTE(@"SEL$F5BB74E1" "T4"@"SEL$1" HASH HASH)]',q'[PQ_DISTRIBUTE(@"SEL$F5BB74E1" "T5"@"SEL$1" HASH HASH)]',q'[SWAP_JOIN_INPUTS(@"SEL$F5BB74E1" "T5"@"SEL$1")]',q'[END_OUTLINE_DATA]');:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);DBMS_SQLTUNE.IMPORT_SQL_PROFILE (sql_text => sql_txt,profile => h,name => 'sqlt_s36096_p3830036895',description => 's36096_ora11g_bipndb01 08hcfgsgr5bjt 3830036895 '||:signature,category => 'DEFAULT',validate => TRUE,replace => TRUE,force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );END;/