[Tuning] Stored Outline을 이용한 SQL Profile 설정

DROP OUTLINE STX_OUTLINE_01_T;

CREATE OR REPLACE OUTLINE STX_OUTLINE_01_T ON
SELECT /*+ USE_CONCAT INDEX(A ENG_REVISED_ITEMS_SN2) */ *
FROM   ENG_REVISED_ITEMS a
WHERE  (REVISED_ITEM_SEQUENCE_ID = :B1
        OR     PARENT_REVISED_ITEM_SEQ_ID = :B1 )
AND    STATUS_TYPE <> 5;

SELECT  '''' || REPLACE(h.hint_text, '''','"') || ''','
FROM    outln.ol$hints h
WHERE   ol_name = 'STX_OUTLINE_01_T'
ORDER BY ol_name, hint# DESC;

----------------------


1. Plan Hint 추출을 위한 Outline 설정 (Tuning된 SQL)

CREATE OR REPLACE OUTLINE SAMPLE_OL ON
SELECT COUNT (*) "TOTAL"
FROM   (select /*+ index(b mtl_system_items_b_u1) */distinct a.INVENTORY_ITEM_ID "AhlucEN_invItemID",
               nvl(a.UOM_CODE, c.UOM_CODE) "AhlucEN_uomCode",
...;


2. Outline에서 Hint Text 추출 (추출 후 마지막 라인의 comma 제거)

SELECT  '''' || REPLACE(h.hint_text, '''','"') || ''','
FROM    outln.ol$hints h
WHERE   ol_name = 'SAMPLE_OL'
ORDER BY ol_name, hint# DESC;


3. SQL Profile 등록

DECLARE
BEGIN
    dbms_sqltune.import_sql_profile(
        name => 'SAMPLE_PR',  -- Profile Name
        description => 'Description',  -- Description
        force_match => TRUE,
        -- Source SQL
        sql_text => '
SELECT COUNT (*) "TOTAL"
FROM   (select distinct a.INVENTORY_ITEM_ID "AhlucEN_invItemID",
...
                WHERE  NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
        order by 4)
',
        -- Tuning된 Plan Hint
        profile => sqlprof_attr (
--'IGNORE_OPTIM_EMBEDDED_HINTS',
--'OPTIMIZER_FEATURES_ENABLE("10.2.0.4")', -- 불필요한 Hint 제거
'OUTLINE_LEAF(@"SEL$F68927DC")',
...
'USE_NL(@"SEL$F68927DC" "MP"@"SEL$8")',
'USE_NL(@"SEL$F68927DC" "TL"@"SEL$3")'
            )
    );

    COMMIT;
END;


4. Outline 삭제

DROP OUTLINE SAMPLE_OL;


5. Profile 삭제 (참고)

dbms_sqltune.drop_sql_profile(name => 'SAMPLE_PR');