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_ID
FROM MTL_MATERIAL_TRANSACTIONS T,
OE_ORDER_HEADERS_ALL H,
OE_ORDER_LINES_ALL L,
MTL_SYSTEM_ITEMS_B I
WHERE T.TRANSACTION_TYPE_ID = 33
AND H.HEADER_ID = L.HEADER_ID
AND I.ORGANIZATION_ID = T.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND T.TRX_SOURCE_LINE_ID = L.LINE_ID
AND T.ATTRIBUTE1 IS NULL
AND H.ATTRIBUTE4 IS NOT NULL

2)Get information from v$sql

SELECT sql_id, plan_hash_value, child_number,executions, is_shareableFROM v$sql
WHERE 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_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_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_ROWS
OUTLINE_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;
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_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.username
from bip.og_webfish_login_d01 t1, bip.dim_webfish_roles t2
where t1.workd = '20111105'
and t1.username = t2.bindusername
and t1.workd = t2.registerdate) t1,
bip.og_webfish_login_d01 t3,
bip.og_webfish_login_d01 t4,
bip.og_webfish_login_d01 t5
where 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;
/