튜닝개선포인트가 있을까요?
kwindow2 1학년

답변 채택시 : 0코아

답변 1 추천 0 조회 1823 2016.07.08

아래의 SQL및 trace에 개선사항이 있을지 문의드립니다.



SELECT
       /* OUTPUT */      
       lh1.product_code
     , lh1.srcmk_code
     , lh1.product_nm
     , lh1.sprc
     , lh1.event_sprc
     , lh1.pda_sn
     , lh1.inventory_qty
     , lh1.event_code
     , lh1.event_nm
     , lh2.sms_cpn_code
     , lh2.sms_cpn_nm
     , lh1.pog_cat_nm
     , lh1.pog_sn
     , lh1.apply_dy
       /* HIDDEN */
     , lh1.product_expn_01
     , lh1.product_expn_02
     , lh1.intrdc_rsn
     , lh1.product_charact
     , lh1.event_dy
     , lh2.sms_cpn_dy
     , lh2.sms_cpn_prnt_ctnt
     , CASE WHEN lh2.offer_apply_div_code = '01' AND lh2.offer_apply_dtl_div_code =
'1' THEN NVL(lh1.event_sprc, lh1.sprc) - lh2.offer_val
            WHEN lh2.offer_apply_div_code = '01' AND lh2.offer_apply_dtl_div_code =
'2' THEN NVL(lh1.event_sprc, lh1.sprc) - TRUNC(lh1.sprc * (lh2.offer_val / 100),0)
            ELSE NULL
       END  sms_cpn_sprc
     , lh1.cross_yn
     , lh1.pfu_size1
     , lh1.pfu_sprc1
     , lh1.pfu_event_sprc1
     , lh1.pfu_size2
     , lh1.pfu_sprc2
     , lh1.pfu_event_sprc2
     , lh1.pfu_size3
     , lh1.pfu_sprc3
     , lh1.pfu_event_sprc3
     , lh1.pog_info
     , lh1.rep_publ_product_code
     , lh1.publ_qty
FROM   (
        SELECT
               s1.product_code
             , s1.srcmk_code
             , s1.product_nm
             , s1.sprc
             , CASE WHEN NVL(s2.event_curr_sprc,0) > 0
                    THEN s2.event_curr_sprc * s1.sale_conv_rt
                    ELSE s1.event_sprc
               END event_sprc
             , '' AS pda_sn
             , s1.inventory_qty
             , s2.event_code
             , s4.event_nm
             , s1.pog_cat_nm
             , s1.pog_sn
             , s1.apply_dy
             , s1.product_expn_01
             , s1.product_expn_02
             , s1.intrdc_rsn
             , s1.product_charact
             , CASE WHEN s2.event_start_dy IS NOT NULL
                     AND s2.event_end_dy IS NOT NULL
                    THEN TO_CHAR(TO_DATE(s2.event_start_dy, 'YYYYMMDD'), 'MM/DD')|
|'~'||TO_CHAR(TO_DATE(s2.event_end_dy, 'YYYYMMDD'), 'MM/DD')
                    ELSE NULL
               END event_dy
             , '' AS cross_yn
             , SUBSTR(s1.pfu_info, 1, INSTR(s1.pfu_info, '|', 1, 1) - 1) AS
pfu_size1
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 1) + 1, INSTR(
s1.pfu_info, '|', 1, 2) - INSTR(s1.pfu_info, '|', 1, 1) - 1) AS pfu_sprc1
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 2) + 1, INSTR(
s1.pfu_info, '|', 1, 3) - INSTR(s1.pfu_info, '|', 1, 2) - 1) AS pfu_event_sprc1
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 3) + 1, INSTR(
s1.pfu_info, '|', 1, 4) - INSTR(s1.pfu_info, '|', 1, 3) - 1) AS pfu_size2
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 4) + 1, INSTR(
s1.pfu_info, '|', 1, 5) - INSTR(s1.pfu_info, '|', 1, 4) - 1) AS pfu_sprc2
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 5) + 1, INSTR(
s1.pfu_info, '|', 1, 6) - INSTR(s1.pfu_info, '|', 1, 5) - 1) AS pfu_event_sprc2
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 6) + 1, INSTR(
s1.pfu_info, '|', 1, 7) - INSTR(s1.pfu_info, '|', 1, 6) - 1) AS pfu_size3
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 7) + 1, INSTR(
s1.pfu_info, '|', 1, 8) - INSTR(s1.pfu_info, '|', 1, 7) - 1) AS pfu_sprc3
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 8) + 1, INSTR(
s1.pfu_info, '|', 1, 9) - INSTR(s1.pfu_info, '|', 1, 8) - 1) AS pfu_event_sprc3
             , s1.pog_sn||' '||s1.pog_cat_nm AS pog_info
             , s1.rep_publ_product_code
             , s1.brnd_code
             , s1.vendor_code
             , s1.publ_qty
        FROM   (
                SELECT b.str_code
                     , a.product_code
                     , c.srcmk_code
                     , a.product_nm
                     , (SELECT FU_CG_GET_APPLY_NRML_SPRC(:sch_dy, b.str_code,
a.product_code) FROM DUAL) AS sprc
                     , (SELECT FU_CG_GET_APPLY_event_SPRC(:sch_dy, b.str_code,
a.product_code) FROM DUAL) AS event_sprc
                     , (SELECT FU_IN_GET_CURR_QTY(b.str_code, b.product_code, TO_CHAR(
SYSDATE, 'YYYYMMDD'), '1') FROM DUAL) AS inventory_qty
                     , f.pog_cat_nm
                     , f.pog_sn
                     , f.apply_dy
                     , d.product_expn_01
                     , d.product_expn_02
                     , d.intrdc_rsn
                     , d.product_charact
                     , a.rep_publ_product_code
                     , CASE WHEN a.rep_publ_product_code IS NOT NULL
                            THEN (
                                  SELECT RTRIM(
                                          XMLAGG(
                                           XMLELEMENT( X
                                                     , aa.disp_totqty_val||
FU_CG_COMMCD('CG010', aa.disp_unit_code)||'|'||
                                                       (SELECT
FU_CG_GET_APPLY_NRML_SPRC(:sch_dy, b.str_code, aa.product_code) FROM DUAL)||'|'||
                                                       (SELECT
FU_CG_GET_APPLY_event_SPRC(:sch_dy, b.str_code, aa.product_code) FROM DUAL)||'|'))
.EXTRACT('//text()'), ',')
                                  FROM   CG_product_COMM aa
                                  WHERE  aa.rep_publ_product_code IN (SELECT
aaa.rep_publ_product_code FROM CG_product_COMM aaa WHERE aaa.product_code = a.product_code)
                                  AND    aa.disp_totqty_val >=
a.disp_totqty_val
                                 )
                            ELSE NULL
                       END AS pfu_info
                     , c.sale_conv_rt
                     , a.brnd_code
                     , b.vendor_code
                     , 1 AS publ_qty
                FROM   CG_product_COMM a
                     , CG_product_STR b
                     , CG_product_SALE c
                     , CG_product_GOAL_DESC d
                     , VW_CG_CAT e
                     , (
                        SELECT cc.srcmk_code
                             , aa.pog_cat_nm
                             , cc.seg_lctn||'-'||cc.rack_lctn||'-'||cc.loc_lctn
AS pog_sn
                             , aa.apply_dy
                        FROM   CG_POG aa
                             , CG_POG_STR_MAP bb
                             , CG_POG_product_DISP cc
                             , CG_STR dd
                        WHERE  aa.pog_id = bb.pog_id
                        AND    bb.pog_id = cc.pog_id
                        AND    bb.str_code = dd.str_code
                        AND    dd.strclose_dy > TO_CHAR(SYSDATE, 'YYYYMMDD')
                        AND    dd.str_code = :str_code
                       ) f
                WHERE  a.product_code = b.product_code
                AND    a.product_code = c.product_code
                AND    a.product_code = d.product_code
                AND    a.l4_code = e.l4_code
                AND    c.srcmk_code = f.srcmk_code(+)
                AND    b.product_oper_stat_code IN ('00', '10', '20', '30', '40',
'45', '50')
                AND    b.str_code = :str_code
--                AND    e.l1_code = :l1_code
--                AND    e.l2_code = :l2_code
--                AND    e.l3_code = :l3_code
--                AND    c.product_code = :product_code
--                AND    c.srcmk_code = :srcmk_code
--                AND    b.vendor_code = :vendor_code
--                AND    a.brnd_code = :brnd_code    
               ) s1
               LEFT OUTER JOIN CG_event_PRC_product s2
        ON     s1.product_code = s2.product_code
        AND    s1.str_code = s2.str_code
        AND    s2.event_start_dy <= :sch_dy
        AND    s2.event_end_dy >= :sch_dy
               LEFT OUTER JOIN CG_event_COND_MT s3
        ON     s2.event_code = s3.event_code
        AND    s2.event_cond_no = s3.event_cond_no
        AND    s3.event_div_code = '01'
        AND    s3.del_yn = 'N'
        AND    s3.event_start_dy <= :sch_dy
        AND    s3.event_end_dy >= :sch_dy
               LEFT OUTER JOIN CG_event s4
        ON     s3.event_code = s4.event_code
        AND    s4.event_kind_div_code = '01'
        AND    s4.event_start_dy <= :sch_dy
        AND    s4.event_end_dy >= :sch_dy
       ) lh1
       LEFT OUTER JOIN
       (
        SELECT
               s1.sms_cpn_code
             , s1.sms_cpn_nm
             , s1.sms_cpn_dy
             , s1.sms_cpn_prnt_ctnt
             , s1.trgt_div_code
             , s1.trgt_dtl_code
             , s1.offer_val
             , s1.offer_apply_div_code
             , s1.offer_apply_dtl_div_code
        FROM   (
                SELECT b.cpn_code AS sms_cpn_code
                     , b.cpn_nm AS sms_cpn_nm
                     , CASE WHEN b.event_start_dy IS NOT NULL
                             AND b.event_end_dy IS NOT NULL
                            THEN TO_CHAR(TO_DATE(b.event_start_dy, 'YYYYMMDD'),
'MM/DD')||'~'||TO_CHAR(TO_DATE(b.event_end_dy, 'YYYYMMDD'), 'MM/DD')
                            ELSE NULL
                       END sms_cpn_dy
                     , b.prnt_ctnt AS sms_cpn_prnt_ctnt
                     , b.trgt_div_code
                     , c.trgt_dtl_code
                     , f.offer_val
                     , b.offer_apply_div_code
                     , f.offer_apply_dtl_div_code
                     , ROW_NUMBER() OVER(PARTITION BY c.trgt_dtl_code ORDER BY
b.event_code ASC) AS cpn_rank
                FROM   CG_event a
                     , CG_event_COND_MT b
                     , CG_event_TRGT c
                     , CG_event_STR d
                     , CG_event_COND_DT e
                     , CG_event_OFFER f
                WHERE  a.event_code = b.event_code
                AND    b.event_code = c.event_code
                AND    b.event_cond_no = c.event_cond_no
                AND    a.event_code = d.event_code
                AND    b.event_code = e.event_code
                AND    b.event_cond_no = e.event_cond_no
                AND    e.event_code = f.event_code
                AND    e.event_cond_no = f.event_cond_no
                AND    e.cond_sectn_no = f.cond_sectn_no
                AND    c.trgt_seq = f.offer_seq
                AND    b.event_div_code IN ('08', '10', '11', '12', '09')
                AND    b.del_yn = 'N'
                AND    c.del_yn = 'N'
                AND    d.del_yn = 'N'
                AND    e.del_yn = 'N'
                AND    f.del_yn = 'N'
                AND    b.prnt_ctnt IS NOT NULL
                AND    d.str_code = :str_code
                AND    a.event_start_dy <= :sch_dy
                AND    a.event_end_dy >= :sch_dy
                AND    b.event_start_dy <= :sch_dy
                AND    b.event_end_dy >= :sch_dy
                AND    d.event_start_dy <= :sch_dy
                AND    d.event_end_dy >= :sch_dy
               ) s1
        WHERE  s1.cpn_rank = 1
       ) lh2
ON     DECODE( lh2.trgt_div_code
             , '01', lh1.srcmk_code
             , '07', lh1.brnd_code
             , '08', lh1.vendor_code) = lh2.trgt_dtl_code

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.002        0.003          0         45          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch      167    9.306      175.219      37819     608046          0      16555
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total      169    9.308      175.222      37819     608091          0      16555

Misses in library cache during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
  16040  FAST DUAL  (cr=0 pr=0 pw=0 time=12389 us cost=2 size=0 card=1)
  16040  FAST DUAL  (cr=0 pr=0 pw=0 time=11692 us cost=2 size=0 card=1)
  16040  FAST DUAL  (cr=0 pr=0 pw=0 time=9972 us cost=2 size=0 card=1)
    111  FAST DUAL  (cr=0 pr=0 pw=0 time=99 us cost=2 size=0 card=1)
    111  FAST DUAL  (cr=0 pr=0 pw=0 time=68 us cost=2 size=0 card=1)
    109  SORT AGGREGATE (cr=127132 pr=171 pw=0 time=1979832 us)
    167   HASH JOIN  (cr=123870 pr=0 pw=0 time=808336 us cost=313 size=30 card=1)
    109    TABLE ACCESS BY INDEX ROWID CG_product_COMM (cr=264 pr=0 pw=0 time=1110 us cost=2 size=13 card=1)
    109     INDEX UNIQUE SCAN PK_CG_product_COMM (cr=155 pr=0 pw=0 time=703 us cost=1 size=0 card=1)
  10078    TABLE ACCESS FULL CG_product_COMM (cr=123606 pr=0 pw=0 time=490223 us cost=311 size=119 card=7)
  16555  HASH JOIN RIGHT OUTER (cr=608046 pr=37819 pw=0 time=135343247 us cost=2758593 size=64621240 card=17390)
    741   TABLE ACCESS BY INDEX ROWID CG_event (cr=277 pr=0 pw=0 time=16937 us cost=4 size=480 card=10)
    777    INDEX RANGE SCAN IX1_CG_event (cr=161 pr=0 pw=0 time=564 us cost=3 size=0 card=2)
  16555   HASH JOIN RIGHT OUTER (cr=607769 pr=37819 pw=0 time=135321555 us cost=2758589 size=63786520 card=17390)
    112    TABLE ACCESS FULL CG_event_COND_MT (cr=731 pr=0 pw=0 time=1956 us cost=207 size=403 card=13)
  16555    HASH JOIN RIGHT OUTER (cr=607038 pr=37819 pw=0 time=135294616 us cost=2758381 size=63247430 card=17390)
      0     TABLE ACCESS BY INDEX ROWID CG_event_PRC_product (cr=74000 pr=0 pw=0 time=1392286 us cost=1241 size=1389844 card=30214)
      0      INDEX RANGE SCAN IX8_CG_event_PRC_product (cr=74000 pr=0 pw=0 time=1392278 us cost=671 size=0 card=979)
  16555     NESTED LOOPS OUTER (cr=533038 pr=37819 pw=0 time=133867985 us cost=2757140 size=62447490 card=17390)
  16555      VIEW  (cr=531813 pr=37819 pw=0 time=132863544 us cost=9248 size=60778050 card=17390)
  16555       HASH JOIN RIGHT OUTER (cr=11240 pr=0 pw=0 time=451759 us cost=9248 size=4799640 card=17390)
   2233        VIEW  (cr=136 pr=0 pw=0 time=2832 us cost=64 size=128835 card=2045)
   2233         NESTED LOOPS  (cr=136 pr=0 pw=0 time=1214 us cost=64 size=153375 card=2045)
     31          NESTED LOOPS  (cr=90 pr=0 pw=0 time=598 us cost=35 size=1363 card=29)
     31           NESTED LOOPS  (cr=55 pr=0 pw=0 time=254 us cost=5 size=780 card=30)
      1            INDEX RANGE SCAN IX2_CG_STR (cr=1 pr=0 pw=0 time=20 us cost=1 size=14 card=1)
     31            INDEX FAST FULL SCAN PK_CG_POG_STR_MAP (cr=54 pr=0 pw=0 time=233 us cost=4 size=360 card=30)
     31           TABLE ACCESS BY INDEX ROWID CG_POG (cr=35 pr=0 pw=0 time=144 us cost=1 size=21 card=1)
     31            INDEX UNIQUE SCAN PK_CG_POG (cr=4 pr=0 pw=0 time=56 us cost=0 size=0 card=1)
   2233          INDEX RANGE SCAN PK_CG_POG_product_DISP (cr=46 pr=0 pw=0 time=763 us cost=1 size=1960 card=70)
  16458        HASH JOIN  (cr=11104 pr=0 pw=0 time=410663 us cost=9184 size=3704070 card=17390)
  25206         TABLE ACCESS FULL CG_product_SALE (cr=460 pr=0 pw=0 time=6943 us cost=139 size=705768 card=25206)
  16496         HASH JOIN  (cr=10644 pr=0 pw=0 time=372297 us cost=9045 size=3206605 card=17333)
  25030          TABLE ACCESS FULL CG_product_GOAL_DESC (cr=447 pr=0 pw=0 time=8222 us cost=139 size=1702040 card=25030)
  16600          HASH JOIN  (cr=10197 pr=0 pw=0 time=324457 us cost=8906 size=2035566 card=17398)
  16600           VIEW  index$_join$_011 (cr=8818 pr=0 pw=0 time=42426 us cost=8587 size=498484 card=17803)
  16600            HASH JOIN  (cr=8818 pr=0 pw=0 time=35142 us)
  25124             INDEX RANGE SCAN IX1_CG_product_STR (cr=181 pr=0 pw=0 time=5629 us cost=183 size=498484 card=17803)
  16600             INDEX FAST FULL SCAN IX4_CG_product_STR (cr=8637 pr=0 pw=0 time=6927 us cost=10493 size=498484 card=17803)
  25124           HASH JOIN  (cr=1379 pr=0 pw=0 time=92743 us cost=319 size=2185217 card=24553)
    279            NESTED LOOPS  (cr=85 pr=0 pw=0 time=2238 us cost=8 size=9555 card=273)
    279             HASH JOIN  (cr=81 pr=0 pw=0 time=1302 us cost=8 size=8091 card=279)
    279              HASH JOIN  (cr=54 pr=0 pw=0 time=899 us cost=6 size=5580 card=279)
    279               INDEX FAST FULL SCAN IX6_CG_CAT (cr=27 pr=0 pw=0 time=244 us cost=3 size=2790 card=279)
    434               INDEX FAST FULL SCAN IX6_CG_CAT (cr=27 pr=0 pw=0 time=242 us cost=3 size=4340 card=434)
    484              INDEX FAST FULL SCAN IX7_CG_CAT (cr=27 pr=0 pw=0 time=35 us cost=2 size=4356 card=484)
    279             INDEX UNIQUE SCAN PK_CG_CAT (cr=4 pr=0 pw=0 time=270 us cost=0 size=6 card=1)
  25124            TABLE ACCESS FULL CG_product_COMM (cr=1294 pr=0 pw=0 time=53130 us cost=311 size=1356696 card=25124)
      0      VIEW  (cr=1225 pr=0 pw=0 time=73831 us cost=158 size=96 card=1)
      0       WINDOW SORT PUSHED RANK (cr=1225 pr=0 pw=0 time=52173 us cost=158 size=161 card=1)
      0        NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9237 us cost=157 size=161 card=1)
      0         NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9235 us cost=157 size=161 card=1)
      0          NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9234 us cost=155 size=139 card=1)
      0           NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9232 us cost=152 size=110 card=1)
      0            NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9231 us cost=150 size=78 card=1)
      0             NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9230 us cost=148 size=63 card=1)
    777              TABLE ACCESS BY INDEX ROWID CG_event (cr=277 pr=0 pw=0 time=1018 us cost=4 size=1800 card=72)
    777               INDEX RANGE SCAN IX1_CG_event (cr=161 pr=0 pw=0 time=232 us cost=3 size=0 card=2)
      0              TABLE ACCESS BY INDEX ROWID CG_event_COND_MT (cr=948 pr=0 pw=0 time=4785 us cost=2 size=38 card=1)
    861               INDEX RANGE SCAN PK_CG_event_COND_MT (cr=282 pr=0 pw=0 time=1900 us cost=1 size=0 card=2)
      0             TABLE ACCESS BY INDEX ROWID CG_event_COND_DT (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
      0              INDEX RANGE SCAN PK_CG_event_COND_DT (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0            TABLE ACCESS BY INDEX ROWID CG_event_STR (cr=0 pr=0 pw=0 time=0 us cost=2 size=32 card=1)
      0             INDEX UNIQUE SCAN PK_CG_event_STR (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0           TABLE ACCESS BY INDEX ROWID CG_event_TRGT (cr=0 pr=0 pw=0 time=0 us cost=3 size=29 card=1)
      0            INDEX RANGE SCAN PK_CG_event_TRGT (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
      0          INDEX UNIQUE SCAN PK_CG_event_OFFER (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0         TABLE ACCESS BY INDEX ROWID CG_event_OFFER (cr=0 pr=0 pw=0 time=0 us cost=2 size=22 card=1)

********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1,
spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        0    0.000        0.000          0          0          0          0
Execute      2    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0          6          0          2
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.000          0          6          0          2

Misses in library cache during parse   : 0
Optimizer Goal : RULE
Parsing user : SYS (ID=0)
Recursive Depth : 2

********************************************************************************

SELECT DTL_code_NM FROM CG_COMMCD WHERE GRP_code = :B2 AND DTL_code = :B1

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute    167    0.002        0.004          0          0          0          0
Fetch      167    0.001        0.002          0        501          0        167
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total      335    0.004        0.006          0        501          0        167

Misses in library cache during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=62)
Recursive Depth : 1


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID CG_COMMCD (cr=3 pr=0 pw=0 time=10 us cost=2 size=22 card=1)
      1   INDEX UNIQUE SCAN PK_CG_COMMCD (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)

********************************************************************************


댓글 (0)
목록 답변등록
튜닝개선포인트가 있을까요?
jhchoi 2016.08.11

Trace 결과를 볼 때 인덱스 변경이나 테이블 조인방식, 조인순서 등의 변경을 통해 메인쿼리의 수행 시간을 크게 단축하기는 어려워 보입니다. TRACE 결과 상에서 이 SQL의 성능문제가 가장 큰 포인트를 찾는다면, 아래 캡쳐 부분에서 HASH 조인 결과가 16555건이며 여기까지의 Elapsed Time 은 약 4.5초가 소요되었는데, 그 위의 “VIEW” 로 표시된 OPERATION 단계로 넘어가면서 갑자기 cr, pr, time 등이 크게 증가하는 것을 볼 수 있으며 이는 전체 SQL수행 성능의 대부분을 차지하는 부분입니다.

......

16555      VIEW  (cr=531813 pr=37819 pw=0 time=132863544 us cost=9248 size=60778050 card=17390)

  16555       HASH JOIN RIGHT OUTER (cr=11240 pr=0 pw=0 time=451759 us cost=9248 size=4799640 card=17390)

   2233        VIEW  (cr=136 pr=0 pw=0 time=2832 us cost=64 size=128835 card=2045)

   2233         NESTED LOOPS  (cr=136 pr=0 pw=0 time=1214 us cost=64 size=153375 card=2045)

     31          NESTED LOOPS  (cr=90 pr=0 pw=0 time=598 us cost=35 size=1363 card=29)

......

이 원인은 SELECT절에 포함되어 있는 FU_CG_GET_APPLY_NRML_SPRC, FU_CG_GET_APPLY_event_SPRC, FU_IN_GET_CURR_QTY 등의 사용자정의함수의 부하로 보입니다.  10046 Event Trace 를 추출하신 것 같은데 사용자정의함수 내에서 수행되는 SQL들이 Trace 상에 제대로 표시가 되지 않은 이유을 잘 모르겠네요.

10046 Event Trace 를 추출하면 사용자정의함수 내에서 수행되는 SQL들도 Trace 가 추출되어 어느 함수에서 부하가 가장 크게 발생하는지 찾을 수 있습니다.  만약 Trace 상에서 문제의 함수를 찾기 어렵다면 manual 로 사용자정의함수의 부하를 확인해보는 간단한 방법은, SQL 내에 사용자정의함수를 포함하고 있는 부분을 모두 주석처리하고서 다시 Trace 를 추출해 보면 메인 SQL 의 성능을 확인할 수 있습니다.  그리고나서 주석 처리했던 사용자정의함수를 하나씩 다시 포함시켜 가면서 Trace 를 추출해 보면 어느 함수에서 부하가 가장 크게 발생하고 있는지 찾을 수 있습니다.

이렇게 다시 테스트해보시고 사용자정의함수의 부하가 맞다면 해당 함수의 성능 개선이 필요합니다.







--------------------- 원문 ----------------------------------------------


아래의 SQL및 trace에 개선사항이 있을지 문의드립니다.



SELECT
       /* OUTPUT */      
       lh1.product_code
     , lh1.srcmk_code
     , lh1.product_nm
     , lh1.sprc
     , lh1.event_sprc
     , lh1.pda_sn
     , lh1.inventory_qty
     , lh1.event_code
     , lh1.event_nm
     , lh2.sms_cpn_code
     , lh2.sms_cpn_nm
     , lh1.pog_cat_nm
     , lh1.pog_sn
     , lh1.apply_dy
       /* HIDDEN */
     , lh1.product_expn_01
     , lh1.product_expn_02
     , lh1.intrdc_rsn
     , lh1.product_charact
     , lh1.event_dy
     , lh2.sms_cpn_dy
     , lh2.sms_cpn_prnt_ctnt
     , CASE WHEN lh2.offer_apply_div_code = '01' AND lh2.offer_apply_dtl_div_code =
'1' THEN NVL(lh1.event_sprc, lh1.sprc) - lh2.offer_val
            WHEN lh2.offer_apply_div_code = '01' AND lh2.offer_apply_dtl_div_code =
'2' THEN NVL(lh1.event_sprc, lh1.sprc) - TRUNC(lh1.sprc * (lh2.offer_val / 100),0)
            ELSE NULL
       END  sms_cpn_sprc
     , lh1.cross_yn
     , lh1.pfu_size1
     , lh1.pfu_sprc1
     , lh1.pfu_event_sprc1
     , lh1.pfu_size2
     , lh1.pfu_sprc2
     , lh1.pfu_event_sprc2
     , lh1.pfu_size3
     , lh1.pfu_sprc3
     , lh1.pfu_event_sprc3
     , lh1.pog_info
     , lh1.rep_publ_product_code
     , lh1.publ_qty
FROM   (
        SELECT
               s1.product_code
             , s1.srcmk_code
             , s1.product_nm
             , s1.sprc
             , CASE WHEN NVL(s2.event_curr_sprc,0) > 0
                    THEN s2.event_curr_sprc * s1.sale_conv_rt
                    ELSE s1.event_sprc
               END event_sprc
             , '' AS pda_sn
             , s1.inventory_qty
             , s2.event_code
             , s4.event_nm
             , s1.pog_cat_nm
             , s1.pog_sn
             , s1.apply_dy
             , s1.product_expn_01
             , s1.product_expn_02
             , s1.intrdc_rsn
             , s1.product_charact
             , CASE WHEN s2.event_start_dy IS NOT NULL
                     AND s2.event_end_dy IS NOT NULL
                    THEN TO_CHAR(TO_DATE(s2.event_start_dy, 'YYYYMMDD'), 'MM/DD')|
|'~'||TO_CHAR(TO_DATE(s2.event_end_dy, 'YYYYMMDD'), 'MM/DD')
                    ELSE NULL
               END event_dy
             , '' AS cross_yn
             , SUBSTR(s1.pfu_info, 1, INSTR(s1.pfu_info, '|', 1, 1) - 1) AS
pfu_size1
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 1) + 1, INSTR(
s1.pfu_info, '|', 1, 2) - INSTR(s1.pfu_info, '|', 1, 1) - 1) AS pfu_sprc1
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 2) + 1, INSTR(
s1.pfu_info, '|', 1, 3) - INSTR(s1.pfu_info, '|', 1, 2) - 1) AS pfu_event_sprc1
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 3) + 1, INSTR(
s1.pfu_info, '|', 1, 4) - INSTR(s1.pfu_info, '|', 1, 3) - 1) AS pfu_size2
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 4) + 1, INSTR(
s1.pfu_info, '|', 1, 5) - INSTR(s1.pfu_info, '|', 1, 4) - 1) AS pfu_sprc2
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 5) + 1, INSTR(
s1.pfu_info, '|', 1, 6) - INSTR(s1.pfu_info, '|', 1, 5) - 1) AS pfu_event_sprc2
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 6) + 1, INSTR(
s1.pfu_info, '|', 1, 7) - INSTR(s1.pfu_info, '|', 1, 6) - 1) AS pfu_size3
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 7) + 1, INSTR(
s1.pfu_info, '|', 1, 8) - INSTR(s1.pfu_info, '|', 1, 7) - 1) AS pfu_sprc3
             , SUBSTR(s1.pfu_info, INSTR(s1.pfu_info, '|', 1, 8) + 1, INSTR(
s1.pfu_info, '|', 1, 9) - INSTR(s1.pfu_info, '|', 1, 8) - 1) AS pfu_event_sprc3
             , s1.pog_sn||' '||s1.pog_cat_nm AS pog_info
             , s1.rep_publ_product_code
             , s1.brnd_code
             , s1.vendor_code
             , s1.publ_qty
        FROM   (
                SELECT b.str_code
                     , a.product_code
                     , c.srcmk_code
                     , a.product_nm
                     , (SELECT FU_CG_GET_APPLY_NRML_SPRC(:sch_dy, b.str_code,
a.product_code) FROM DUAL) AS sprc
                     , (SELECT FU_CG_GET_APPLY_event_SPRC(:sch_dy, b.str_code,
a.product_code) FROM DUAL) AS event_sprc
                     , (SELECT FU_IN_GET_CURR_QTY(b.str_code, b.product_code, TO_CHAR(
SYSDATE, 'YYYYMMDD'), '1') FROM DUAL) AS inventory_qty
                     , f.pog_cat_nm
                     , f.pog_sn
                     , f.apply_dy
                     , d.product_expn_01
                     , d.product_expn_02
                     , d.intrdc_rsn
                     , d.product_charact
                     , a.rep_publ_product_code
                     , CASE WHEN a.rep_publ_product_code IS NOT NULL
                            THEN (
                                  SELECT RTRIM(
                                          XMLAGG(
                                           XMLELEMENT( X
                                                     , aa.disp_totqty_val||
FU_CG_COMMCD('CG010', aa.disp_unit_code)||'|'||
                                                       (SELECT
FU_CG_GET_APPLY_NRML_SPRC(:sch_dy, b.str_code, aa.product_code) FROM DUAL)||'|'||
                                                       (SELECT
FU_CG_GET_APPLY_event_SPRC(:sch_dy, b.str_code, aa.product_code) FROM DUAL)||'|'))
.EXTRACT('//text()'), ',')
                                  FROM   CG_product_COMM aa
                                  WHERE  aa.rep_publ_product_code IN (SELECT
aaa.rep_publ_product_code FROM CG_product_COMM aaa WHERE aaa.product_code = a.product_code)
                                  AND    aa.disp_totqty_val >=
a.disp_totqty_val
                                 )
                            ELSE NULL
                       END AS pfu_info
                     , c.sale_conv_rt
                     , a.brnd_code
                     , b.vendor_code
                     , 1 AS publ_qty
                FROM   CG_product_COMM a
                     , CG_product_STR b
                     , CG_product_SALE c
                     , CG_product_GOAL_DESC d
                     , VW_CG_CAT e
                     , (
                        SELECT cc.srcmk_code
                             , aa.pog_cat_nm
                             , cc.seg_lctn||'-'||cc.rack_lctn||'-'||cc.loc_lctn
AS pog_sn
                             , aa.apply_dy
                        FROM   CG_POG aa
                             , CG_POG_STR_MAP bb
                             , CG_POG_product_DISP cc
                             , CG_STR dd
                        WHERE  aa.pog_id = bb.pog_id
                        AND    bb.pog_id = cc.pog_id
                        AND    bb.str_code = dd.str_code
                        AND    dd.strclose_dy > TO_CHAR(SYSDATE, 'YYYYMMDD')
                        AND    dd.str_code = :str_code
                       ) f
                WHERE  a.product_code = b.product_code
                AND    a.product_code = c.product_code
                AND    a.product_code = d.product_code
                AND    a.l4_code = e.l4_code
                AND    c.srcmk_code = f.srcmk_code(+)
                AND    b.product_oper_stat_code IN ('00', '10', '20', '30', '40',
'45', '50')
                AND    b.str_code = :str_code
--                AND    e.l1_code = :l1_code
--                AND    e.l2_code = :l2_code
--                AND    e.l3_code = :l3_code
--                AND    c.product_code = :product_code
--                AND    c.srcmk_code = :srcmk_code
--                AND    b.vendor_code = :vendor_code
--                AND    a.brnd_code = :brnd_code    
               ) s1
               LEFT OUTER JOIN CG_event_PRC_product s2
        ON     s1.product_code = s2.product_code
        AND    s1.str_code = s2.str_code
        AND    s2.event_start_dy <= :sch_dy
        AND    s2.event_end_dy >= :sch_dy
               LEFT OUTER JOIN CG_event_COND_MT s3
        ON     s2.event_code = s3.event_code
        AND    s2.event_cond_no = s3.event_cond_no
        AND    s3.event_div_code = '01'
        AND    s3.del_yn = 'N'
        AND    s3.event_start_dy <= :sch_dy
        AND    s3.event_end_dy >= :sch_dy
               LEFT OUTER JOIN CG_event s4
        ON     s3.event_code = s4.event_code
        AND    s4.event_kind_div_code = '01'
        AND    s4.event_start_dy <= :sch_dy
        AND    s4.event_end_dy >= :sch_dy
       ) lh1
       LEFT OUTER JOIN
       (
        SELECT
               s1.sms_cpn_code
             , s1.sms_cpn_nm
             , s1.sms_cpn_dy
             , s1.sms_cpn_prnt_ctnt
             , s1.trgt_div_code
             , s1.trgt_dtl_code
             , s1.offer_val
             , s1.offer_apply_div_code
             , s1.offer_apply_dtl_div_code
        FROM   (
                SELECT b.cpn_code AS sms_cpn_code
                     , b.cpn_nm AS sms_cpn_nm
                     , CASE WHEN b.event_start_dy IS NOT NULL
                             AND b.event_end_dy IS NOT NULL
                            THEN TO_CHAR(TO_DATE(b.event_start_dy, 'YYYYMMDD'),
'MM/DD')||'~'||TO_CHAR(TO_DATE(b.event_end_dy, 'YYYYMMDD'), 'MM/DD')
                            ELSE NULL
                       END sms_cpn_dy
                     , b.prnt_ctnt AS sms_cpn_prnt_ctnt
                     , b.trgt_div_code
                     , c.trgt_dtl_code
                     , f.offer_val
                     , b.offer_apply_div_code
                     , f.offer_apply_dtl_div_code
                     , ROW_NUMBER() OVER(PARTITION BY c.trgt_dtl_code ORDER BY
b.event_code ASC) AS cpn_rank
                FROM   CG_event a
                     , CG_event_COND_MT b
                     , CG_event_TRGT c
                     , CG_event_STR d
                     , CG_event_COND_DT e
                     , CG_event_OFFER f
                WHERE  a.event_code = b.event_code
                AND    b.event_code = c.event_code
                AND    b.event_cond_no = c.event_cond_no
                AND    a.event_code = d.event_code
                AND    b.event_code = e.event_code
                AND    b.event_cond_no = e.event_cond_no
                AND    e.event_code = f.event_code
                AND    e.event_cond_no = f.event_cond_no
                AND    e.cond_sectn_no = f.cond_sectn_no
                AND    c.trgt_seq = f.offer_seq
                AND    b.event_div_code IN ('08', '10', '11', '12', '09')
                AND    b.del_yn = 'N'
                AND    c.del_yn = 'N'
                AND    d.del_yn = 'N'
                AND    e.del_yn = 'N'
                AND    f.del_yn = 'N'
                AND    b.prnt_ctnt IS NOT NULL
                AND    d.str_code = :str_code
                AND    a.event_start_dy <= :sch_dy
                AND    a.event_end_dy >= :sch_dy
                AND    b.event_start_dy <= :sch_dy
                AND    b.event_end_dy >= :sch_dy
                AND    d.event_start_dy <= :sch_dy
                AND    d.event_end_dy >= :sch_dy
               ) s1
        WHERE  s1.cpn_rank = 1
       ) lh2
ON     DECODE( lh2.trgt_div_code
             , '01', lh1.srcmk_code
             , '07', lh1.brnd_code
             , '08', lh1.vendor_code) = lh2.trgt_dtl_code

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.002        0.003          0         45          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch      167    9.306      175.219      37819     608046          0      16555
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total      169    9.308      175.222      37819     608091          0      16555

Misses in library cache during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
  16040  FAST DUAL  (cr=0 pr=0 pw=0 time=12389 us cost=2 size=0 card=1)
  16040  FAST DUAL  (cr=0 pr=0 pw=0 time=11692 us cost=2 size=0 card=1)
  16040  FAST DUAL  (cr=0 pr=0 pw=0 time=9972 us cost=2 size=0 card=1)
    111  FAST DUAL  (cr=0 pr=0 pw=0 time=99 us cost=2 size=0 card=1)
    111  FAST DUAL  (cr=0 pr=0 pw=0 time=68 us cost=2 size=0 card=1)
    109  SORT AGGREGATE (cr=127132 pr=171 pw=0 time=1979832 us)
    167   HASH JOIN  (cr=123870 pr=0 pw=0 time=808336 us cost=313 size=30 card=1)
    109    TABLE ACCESS BY INDEX ROWID CG_product_COMM (cr=264 pr=0 pw=0 time=1110 us cost=2 size=13 card=1)
    109     INDEX UNIQUE SCAN PK_CG_product_COMM (cr=155 pr=0 pw=0 time=703 us cost=1 size=0 card=1)
  10078    TABLE ACCESS FULL CG_product_COMM (cr=123606 pr=0 pw=0 time=490223 us cost=311 size=119 card=7)
  16555  HASH JOIN RIGHT OUTER (cr=608046 pr=37819 pw=0 time=135343247 us cost=2758593 size=64621240 card=17390)
    741   TABLE ACCESS BY INDEX ROWID CG_event (cr=277 pr=0 pw=0 time=16937 us cost=4 size=480 card=10)
    777    INDEX RANGE SCAN IX1_CG_event (cr=161 pr=0 pw=0 time=564 us cost=3 size=0 card=2)
  16555   HASH JOIN RIGHT OUTER (cr=607769 pr=37819 pw=0 time=135321555 us cost=2758589 size=63786520 card=17390)
    112    TABLE ACCESS FULL CG_event_COND_MT (cr=731 pr=0 pw=0 time=1956 us cost=207 size=403 card=13)
  16555    HASH JOIN RIGHT OUTER (cr=607038 pr=37819 pw=0 time=135294616 us cost=2758381 size=63247430 card=17390)
      0     TABLE ACCESS BY INDEX ROWID CG_event_PRC_product (cr=74000 pr=0 pw=0 time=1392286 us cost=1241 size=1389844 card=30214)
      0      INDEX RANGE SCAN IX8_CG_event_PRC_product (cr=74000 pr=0 pw=0 time=1392278 us cost=671 size=0 card=979)
  16555     NESTED LOOPS OUTER (cr=533038 pr=37819 pw=0 time=133867985 us cost=2757140 size=62447490 card=17390)
  16555      VIEW  (cr=531813 pr=37819 pw=0 time=132863544 us cost=9248 size=60778050 card=17390)
  16555       HASH JOIN RIGHT OUTER (cr=11240 pr=0 pw=0 time=451759 us cost=9248 size=4799640 card=17390)
   2233        VIEW  (cr=136 pr=0 pw=0 time=2832 us cost=64 size=128835 card=2045)
   2233         NESTED LOOPS  (cr=136 pr=0 pw=0 time=1214 us cost=64 size=153375 card=2045)
     31          NESTED LOOPS  (cr=90 pr=0 pw=0 time=598 us cost=35 size=1363 card=29)
     31           NESTED LOOPS  (cr=55 pr=0 pw=0 time=254 us cost=5 size=780 card=30)
      1            INDEX RANGE SCAN IX2_CG_STR (cr=1 pr=0 pw=0 time=20 us cost=1 size=14 card=1)
     31            INDEX FAST FULL SCAN PK_CG_POG_STR_MAP (cr=54 pr=0 pw=0 time=233 us cost=4 size=360 card=30)
     31           TABLE ACCESS BY INDEX ROWID CG_POG (cr=35 pr=0 pw=0 time=144 us cost=1 size=21 card=1)
     31            INDEX UNIQUE SCAN PK_CG_POG (cr=4 pr=0 pw=0 time=56 us cost=0 size=0 card=1)
   2233          INDEX RANGE SCAN PK_CG_POG_product_DISP (cr=46 pr=0 pw=0 time=763 us cost=1 size=1960 card=70)
  16458        HASH JOIN  (cr=11104 pr=0 pw=0 time=410663 us cost=9184 size=3704070 card=17390)
  25206         TABLE ACCESS FULL CG_product_SALE (cr=460 pr=0 pw=0 time=6943 us cost=139 size=705768 card=25206)
  16496         HASH JOIN  (cr=10644 pr=0 pw=0 time=372297 us cost=9045 size=3206605 card=17333)
  25030          TABLE ACCESS FULL CG_product_GOAL_DESC (cr=447 pr=0 pw=0 time=8222 us cost=139 size=1702040 card=25030)
  16600          HASH JOIN  (cr=10197 pr=0 pw=0 time=324457 us cost=8906 size=2035566 card=17398)
  16600           VIEW  index$_join$_011 (cr=8818 pr=0 pw=0 time=42426 us cost=8587 size=498484 card=17803)
  16600            HASH JOIN  (cr=8818 pr=0 pw=0 time=35142 us)
  25124             INDEX RANGE SCAN IX1_CG_product_STR (cr=181 pr=0 pw=0 time=5629 us cost=183 size=498484 card=17803)
  16600             INDEX FAST FULL SCAN IX4_CG_product_STR (cr=8637 pr=0 pw=0 time=6927 us cost=10493 size=498484 card=17803)
  25124           HASH JOIN  (cr=1379 pr=0 pw=0 time=92743 us cost=319 size=2185217 card=24553)
    279            NESTED LOOPS  (cr=85 pr=0 pw=0 time=2238 us cost=8 size=9555 card=273)
    279             HASH JOIN  (cr=81 pr=0 pw=0 time=1302 us cost=8 size=8091 card=279)
    279              HASH JOIN  (cr=54 pr=0 pw=0 time=899 us cost=6 size=5580 card=279)
    279               INDEX FAST FULL SCAN IX6_CG_CAT (cr=27 pr=0 pw=0 time=244 us cost=3 size=2790 card=279)
    434               INDEX FAST FULL SCAN IX6_CG_CAT (cr=27 pr=0 pw=0 time=242 us cost=3 size=4340 card=434)
    484              INDEX FAST FULL SCAN IX7_CG_CAT (cr=27 pr=0 pw=0 time=35 us cost=2 size=4356 card=484)
    279             INDEX UNIQUE SCAN PK_CG_CAT (cr=4 pr=0 pw=0 time=270 us cost=0 size=6 card=1)
  25124            TABLE ACCESS FULL CG_product_COMM (cr=1294 pr=0 pw=0 time=53130 us cost=311 size=1356696 card=25124)
      0      VIEW  (cr=1225 pr=0 pw=0 time=73831 us cost=158 size=96 card=1)
      0       WINDOW SORT PUSHED RANK (cr=1225 pr=0 pw=0 time=52173 us cost=158 size=161 card=1)
      0        NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9237 us cost=157 size=161 card=1)
      0         NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9235 us cost=157 size=161 card=1)
      0          NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9234 us cost=155 size=139 card=1)
      0           NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9232 us cost=152 size=110 card=1)
      0            NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9231 us cost=150 size=78 card=1)
      0             NESTED LOOPS  (cr=1225 pr=0 pw=0 time=9230 us cost=148 size=63 card=1)
    777              TABLE ACCESS BY INDEX ROWID CG_event (cr=277 pr=0 pw=0 time=1018 us cost=4 size=1800 card=72)
    777               INDEX RANGE SCAN IX1_CG_event (cr=161 pr=0 pw=0 time=232 us cost=3 size=0 card=2)
      0              TABLE ACCESS BY INDEX ROWID CG_event_COND_MT (cr=948 pr=0 pw=0 time=4785 us cost=2 size=38 card=1)
    861               INDEX RANGE SCAN PK_CG_event_COND_MT (cr=282 pr=0 pw=0 time=1900 us cost=1 size=0 card=2)
      0             TABLE ACCESS BY INDEX ROWID CG_event_COND_DT (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
      0              INDEX RANGE SCAN PK_CG_event_COND_DT (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0            TABLE ACCESS BY INDEX ROWID CG_event_STR (cr=0 pr=0 pw=0 time=0 us cost=2 size=32 card=1)
      0             INDEX UNIQUE SCAN PK_CG_event_STR (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0           TABLE ACCESS BY INDEX ROWID CG_event_TRGT (cr=0 pr=0 pw=0 time=0 us cost=3 size=29 card=1)
      0            INDEX RANGE SCAN PK_CG_event_TRGT (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
      0          INDEX UNIQUE SCAN PK_CG_event_OFFER (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
      0         TABLE ACCESS BY INDEX ROWID CG_event_OFFER (cr=0 pr=0 pw=0 time=0 us cost=2 size=22 card=1)

********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1,
spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        0    0.000        0.000          0          0          0          0
Execute      2    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0          6          0          2
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.000          0          6          0          2

Misses in library cache during parse   : 0
Optimizer Goal : RULE
Parsing user : SYS (ID=0)
Recursive Depth : 2

********************************************************************************

SELECT DTL_code_NM FROM CG_COMMCD WHERE GRP_code = :B2 AND DTL_code = :B1

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute    167    0.002        0.004          0          0          0          0
Fetch      167    0.001        0.002          0        501          0        167
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total      335    0.004        0.006          0        501          0        167

Misses in library cache during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=62)
Recursive Depth : 1


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID CG_COMMCD (cr=3 pr=0 pw=0 time=10 us cost=2 size=22 card=1)
      1   INDEX UNIQUE SCAN PK_CG_COMMCD (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)

********************************************************************************



댓글 (0)