SQL #1

 

SELECT *                                                  

FROM (SELECT T0.*, FLOOR((ROWNUM - 1) / :1 + 1) PAGE      

       FROM           (                                              

                                        SELECT                                         

                                                     a.entry_indexno, a.eventid,              

                                                     a.userid, a.name, a.reg_date,                 

                                                     b.phone, b.address1, b.address2,               

                                                     a.value1,                                   

                                                     a.value2,                                   

                                                     a.value3,                                   

                                                     a.value4,                                   

                                                     a.value5,                                    

                                                     a.value6,                                   

                                                     a.value7,                                   

                                                     a.value8,                                   

                                                     a.value9,                                   

                                                     a.winner_status,                          

                                                     a.event_goods_indexno,              

                                                     e.goods_name ,                          

                                                    (SELECT                                  

                                                                 count(*)                                   

                                                    FROM DDH_EVENT_ENTRY           

                                                    WHERE userid = a.userid             

                                                    AND winner_status = 'Y'             

                                                      ) as old_win_count                      

                                       FROM     DDH_EVENT_ENTRY a                   

                                                                                LEFT JOIN DDH_USERMASTER b                    

                                                                               ON a.userid = b.userid                        

                                                                               LEFT JOIN DDH_EVENT_MASTER c              

                                                                               ON a.eventid = c.eventid            

                                                                               LEFT JOIN DDH_EVENT_GOODS d                    

                                                                               ON a.event_goods_indexno = d.event_goods_indexno                       

                                                                               LEFT JOIN DDH_GOODS_MASTER e              

                                                                               ON d.goods_indexno = e.goods_indexno            

                                       WHERE a.eventid = :2                      

                                       ORDER BY a.reg_date ASC                       

                                        ) T0                                                 

             )                                                       

WHERE PAGE = :3

 

 

바인드 변수 값 없음.

 

 

수행 시간 / 일 량 결과

 

elapsed          disk      query     current      rows

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

3.157774         0       241757          0       4078

 

실제 수행 플랜 및 건수

 

Row Row_Source_Operation

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

     4078  SORT AGGREGATE  (cr=90324 pr=0 pw=0 time=468228)

        0   TABLE ACCESS BY INDEX ROWID DDH_EVENT_ENTRY (cr=90324 pr=0 pw=0 time=459254)

    90121    INDEX RANGE SCAN DDH_EVENT_ENTRY_I1 (cr=8496 pr=0 pw=0 time=65121)

       10  VIEW   (cr=151433 pr=0 pw=0 time=2689546)

    23364   COUNT   (cr=151433 pr=0 pw=0 time=2647019)

    23364    VIEW   (cr=151433 pr=0 pw=0 time=2623644)

    23364     SORT ORDER BY  (cr=151433 pr=0 pw=0 time=2623636)

    23364      HASH JOIN RIGHT OUTER  (cr=61109 pr=0 pw=0 time=2071387)

       87       TABLE ACCESS FULL DDH_GOODS_MASTER (cr=7 pr=0 pw=0 time=40) <-- 풀 스캔

    23364       NESTED LOOPS OUTER  (cr=61102 pr=0 pw=0 time=2047065)

    23364        HASH JOIN RIGHT OUTER  (cr=61102 pr=0 pw=0 time=2000328)

        1         INDEX UNIQUE SCAN DDH_EVENT_MASTER_PK (cr=1 pr=0 pw=0 time=13)

    23364         HASH JOIN OUTER  (cr=61101 pr=0 pw=0 time=1941106)

    23364          TABLE ACCESS FULL DDH_EVENT_ENTRY (cr=2770 pr=0 pw=0 time=23393) <-- 풀 스캔

  1461816          TABLE ACCESS FULL DDH_USERMASTER# (cr=58331 pr=0 pw=0 time=1723)<-- 풀 스캔

        0        TABLE ACCESS BY INDEX ROWID DDH_EVENT_GOODS (cr=0 pr=0 pw=0 time=43401)

        0         INDEX UNIQUE SCAN DDH_EVENT_GOODS_PK (cr=0 pr=0 pw=0 time=18149)

 

 

2. 문제점 및 원인

-    테이블을 풀 스캔 리드 되며 시간 소요가 증가 및 피지컬 I/O 수행이 증가 됨

 

-    앞으로의 Tuning Guide에서 제시하는 개선안을 실제 운영중인 시스템에 적용 시 반드시 원문SQL과 관련 있는 담당자나 개발자 분이 개선전의 실행결과값과 개선방안의 실행결과값을 다방면으로 면밀히 조사하여 결과값의 차이가 없는지 확인한 뒤 적용하여야 하며 개발환경이나 업무 특이사항으로 문제가 될 수 있는지 등에 대한 종합적인 판단을 하여 운영시스템 적용 시 문제가 발생되지 않도록 주의하여야 한다.

 

3. 개선방안

 

-    풀 스캔을 막고 인덱스 스캔으로 하도록 수정합니다.

 

 

개선 안.

SQL HINT 조정으로 JOIN 방식 및 인덱스 수행 되도록 변경함.

 

SELECT *                                                  

FROM (SELECT T0.*, FLOOR((ROWNUM - 1) / :1 + 1) PAGE     

       FROM           (                                              

                                        SELECT /*+ leading(a b c d e) use_nl(a b c d e) index(b DDH_USERMASTER_I1) index(e DDH_GOODS_MASTER_PK) */         a.entry_indexno, a.eventid,              

                                                     a.userid, a.name, a.reg_date,                  

                                                     b.phone, b.address1, b.address2,              

                                                     a.value1,                                   

                                                     a.value2,                                   

                                                     a.value3,                                   

                                                     a.value4,                                    

                                                     a.value5,                                   

                                                     a.value6,                                   

                                                     a.value7,                                   

                                                     a.value8,                                   

                                                     a.value9,                                    

                                                     a.winner_status,                         

                                                     a.event_goods_indexno,              

                                                     e.goods_name ,                          

                                                    (SELECT                                  

                                                                 count(*)                                    

                                                    FROM DDH_EVENT_ENTRY           

                                                    WHERE userid = a.userid             

                                                    AND winner_status = 'Y'             

                                                      ) as old_win_count                      

                                       FROM     DDH_EVENT_ENTRY a                   

                                                                                LEFT JOIN DDH_USERMASTER b                    

                                                                               ON a.userid = b.userid                        

                                                                               LEFT JOIN DDH_EVENT_MASTER c              

                                                                               ON a.eventid = c.eventid            

                                                                               LEFT JOIN DDH_EVENT_GOODS d                    

                                                                               ON a.event_goods_indexno = d.event_goods_indexno                      

                                                                               LEFT JOIN DDH_GOODS_MASTER e              

                                                                               ON d.goods_indexno = e.goods_indexno            

                                       WHERE a.eventid = :2                      

                                       ORDER BY a.reg_date ASC                       

                                        ) T0                                                

             )                                                       

WHERE PAGE = :3

 

 

수행 시간 / 일 량 결과

 

elapsed          disk      query     current       rows

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

 10.847647           0    1949657          0      18159

 

 

실제 수행 플랜 및 건수

 

Row  Row_Source_Operation

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

    18159  SORT AGGREGATE  (cr=938445 pr=0 pw=0 time=5027074)

        0   TABLE ACCESS BY INDEX ROWID DDH_EVENT_ENTRY (cr=938445 pr=0 pw=0 time=4986103)

   989129    INDEX RANGE SCAN DDH_EVENT_ENTRY_I1 (cr=39979 pr=0 pw=0 time=488034)

       10  VIEW   (cr=1011212 pr=0 pw=0 time=5820573)

    23364   COUNT   (cr=1011212 pr=0 pw=0 time=5771581)

    23364    VIEW   (cr=1011212 pr=0 pw=0 time=5748205)

    23364     SORT ORDER BY  (cr=1011212 pr=0 pw=0 time=5748197)

    23364      NESTED LOOPS OUTER  (cr=72767 pr=0 pw=0 time=607526)

    23364       NESTED LOOPS OUTER  (cr=72767 pr=0 pw=0 time=537432)

    23364        NESTED LOOPS OUTER  (cr=72767 pr=0 pw=0 time=467339)

    23364         NESTED LOOPS OUTER  (cr=72765 pr=0 pw=0 time=373878)

    23364          TABLE ACCESS FULL DDH_EVENT_ENTRY (cr=2770 pr=0 pw=0 time=46768)

    23265          TABLE ACCESS BY INDEX ROWID DDH_USERMASTER# (cr=69995 pr=0 pw=0 time=296150)

    23265           INDEX UNIQUE SCAN DDH_USERMASTER_I1 (cr=46730 pr=0 pw=0 time=173375)

    23364         INDEX UNIQUE SCAN DDH_EVENT_MASTER_PK (cr=2 pr=0 pw=0 time=68697)

        0        TABLE ACCESS BY INDEX ROWID DDH_EVENT_GOODS (cr=0 pr=0 pw=0 time=47826)

        0         INDEX UNIQUE SCAN DDH_EVENT_GOODS_PK (cr=0 pr=0 pw=0 time=18914)

        0       TABLE ACCESS BY INDEX ROWID DDH_GOODS_MASTER (cr=0 pr=0 pw=0 time=44078)

        0        INDEX UNIQUE SCAN DDH_GOODS_MASTER_PK (cr=0 pr=0 pw=0 time=18043)

 

<- 풀 스캔 테이블을 제거 하였으나 select 절에 count 문으로 인해 DDH_EVENT_ENTRY 테이블을 수행 건수 만큼 다시 서치하므로 이를 제거

사업자 정보 표시
(주)블루원 | 김홍태 | 서울특별시 용산구 원효로 4가 135 금홍 2빌딩 | 사업자 등록번호 : 106-86-76684 | TEL : 02-3272-7200 | Mail : support_ora@blueone.co.kr | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기

'DB - ORACLE > DB Tunning(TABLE,SQL,...)' 카테고리의 다른 글

인덱스 사용 현황 모니터링  (0) 2016.03.23
Segment shrinking  (0) 2016.01.02
Latch & Lock  (0) 2015.12.30
Keep Buffer 활용방안  (0) 2015.12.30
SQL 성능 향상 지침  (0) 2015.12.29

+ Recent posts