관련 sql 튜닝 방안좀 도움 부탁 드려요 0 11 2,369

by 콩나무 [Oracle Tuning] 튜닝 group by [2015.08.30 21:10:07]


plan.txt (4,367Bytes)

쿼리는 아래와 같구요. 쿼리튜닝은 초보라 잘 안되서 질문 드립니다.
일단 건수자체가 기간별로 조회인데 하루치만 해도 조인하면 20만건정도 됩니다.
QWK_UPDETAlL 이란 테이블은 19억건정도 되구요. 조인하면서 updetail 쪽에 발생하는 랜덤 엑세스가
많아서 줄이보고자 여러가지 생각을 했는데 그중 한가지가 아예 updetail테이블쪽 랜덤엑세스를 줄이고자
인덱스를 컬럼있는걸 다 생성하는거 였는데, 시간이 줄긴 하겠지만 정답은 아니라고 생각 되서요.
선행집합을 gropu by 한후에 join을 하고 싶어 시도해봤는데 데이터 조건이나 이런게 잘 안맞아서 잘 안되네요 혹시 이런경우에 최적화 하는 방법이 어떤것이 있을 까요?? 플랜이랑 쿼리는 파일 첨부 합니다.

 


SELECT A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.STEPNAME,
  A.ITEM,
  TO_CHAR(A.TOTCNT) AS TOTCNT,
  TO_CHAR(A.NGCNT) AS NGCNT, A.RATE AS RATE, 'DATA' AS DATA,
  ROWNUM AS TOP
FROM
  (
    SELECT  A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.COMMENT1 AS STEPNAME,
      A.ITEM,
      COUNT(*) AS TOTCNT,
      SUM(DECODE(A.JUDGE, '0', 1, 0)) AS NGCNT,
      ROUND(SUM(DECODE(A.JUDGE, '0', 1, 0)) * 100 / COUNT(*),
        2) AS RATE
    FROM QWK_UPDETAIL A
      INNER JOIN QWK_UPHEAD B ON A.PLANT = B.PLANT
      AND A.TSTCLS = B.TSTCLS AND A.HOGI = B.HOGI AND A.MIP = B.MIP
      AND A.DBKEY = B.DBKEY
    WHERE B.PLANT = 'E'
      AND B.TSTCLS = 'VB'
      AND B.DBKEY >= '201508170759'
      AND B.DBKEY <= '201508180759'
      AND NOT EXISTS (
        SELECT 'X'
        FROM QCD_STAND
        WHERE PLANT = B.PLANT
          AND TSTCLS = B.TSTCLS
          AND A.ITEM = ITEM
          AND SLT01 = '1'
        )
      AND B.TMCODE NOT IN (
        SELECT SNO
        FROM QCD_MASTER
        WHERE PLANT = B.PLANT
          AND TSTCLS = B.TSTCLS
        )
      AND B.INSPCHASU = '1'
    GROUP BY A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.COMMENT1,
      A.ITEM
  )A
WHERE ROWNUM <= 50
  AND A.NGCNT != 0

 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |       |    11 (100)|          |     50 |00:00:03.78 |   66871 |   1833 |       |       |          |
|*  1 |  COUNT STOPKEY                      |                  |      1 |        |       |            |          |     50 |00:00:03.78 |   66871 |   1833 |       |       |          |
|   2 |   VIEW                              |                  |      1 |      1 |   132 |    11  (19)| 00:00:01 |     50 |00:00:03.78 |   66871 |   1833 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY            |                  |      1 |      1 |   117 |    11  (19)| 00:00:01 |     50 |00:00:03.78 |   66871 |   1833 |  9216 |  9216 | 8192  (0)|
|*  4 |     FILTER                          |                  |      1 |        |       |            |          |     76 |00:00:03.78 |   66871 |   1833 |       |       |          |
|   5 |      HASH GROUP BY                  |                  |      1 |      1 |   117 |    11  (19)| 00:00:01 |   2344 |00:00:03.78 |   66871 |   1833 |   828K|   828K| 1338K (0)|
|*  6 |       FILTER                        |                  |      1 |        |       |            |          |    214K|00:00:03.62 |   66871 |   1833 |       |       |          |
|   7 |        NESTED LOOPS ANTI            |                  |      1 |      1 |   117 |     9   (0)| 00:00:01 |    214K|00:00:03.53 |   66749 |   1833 |       |       |          |
|   8 |         NESTED LOOPS                |                  |      1 |      1 |   104 |     7   (0)| 00:00:01 |    214K|00:00:03.47 |   66687 |   1833 |       |       |          |
|*  9 |          TABLE ACCESS BY INDEX ROWID| QWK_UPHEAD       |      1 |      1 |    39 |     4   (0)| 00:00:01 |   2011 |00:00:00.11 |     976 |      8 |       |       |          |
|* 10 |           INDEX RANGE SCAN          | QWK_UPHEAD_IDX04 |      1 |      1 |       |     3   (0)| 00:00:01 |   2176 |00:00:00.04 |      20 |      3 |       |       |          |
|  11 |          TABLE ACCESS BY INDEX ROWID| QWK_UPDETAIL     |   2011 |      1 |    65 |     5   (0)| 00:00:01 |    214K|00:00:03.31 |   65711 |   1825 |       |       |          |
|* 12 |           INDEX RANGE SCAN          | IDX_SCMR_010     |   2011 |      1 |       |     4   (0)| 00:00:01 |    214K|00:00:01.68 |   11231 |    971 |       |       |          |
|* 13 |         INDEX RANGE SCAN            | TEST_IDX         |     31 |     54 |   702 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      62 |      0 |       |       |          |
|* 14 |        INDEX UNIQUE SCAN            | XPKQCD_MASTER    |    122 |      1 |    18 |     0   (0)|          |      0 |00:00:00.01 |     122 |      0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$312E5F5B / A@SEL$1
   3 - SEL$312E5F5B
   9 - SEL$312E5F5B / B@SEL$2
  10 - SEL$312E5F5B / B@SEL$2
  11 - SEL$312E5F5B / A@SEL$2
  12 - SEL$312E5F5B / A@SEL$2
  13 - SEL$312E5F5B / QCD_STAND@SEL$4
  14 - SEL$5        / QCD_MASTER@SEL$5

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$312E5F5B")
      UNNEST(@"SEL$4")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$64EAE176")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$1" "A"@"SEL$1")
      INDEX_RS_ASC(@"SEL$312E5F5B" "B"@"SEL$2" ("QWK_UPHEAD"."PLANT" "QWK_UPHEAD"."TSTCLS" "QWK_UPHEAD"."DBKEY"))
      INDEX_RS_ASC(@"SEL$312E5F5B" "A"@"SEL$2" ("QWK_UPDETAIL"."DBKEY" "QWK_UPDETAIL"."PLANT" "QWK_UPDETAIL"."TSTCLS" "QWK_UPDETAIL"."HOGI" "QWK_UPDETAIL"."ITEM"
              "QWK_UPDETAIL"."PATTERN" "QWK_UPDETAIL"."STEP" "QWK_UPDETAIL"."MIP"))
      INDEX(@"SEL$312E5F5B" "QCD_STAND"@"SEL$4" ("QCD_STAND"."PLANT" "QCD_STAND"."TSTCLS" "QCD_STAND"."ITEM" "QCD_STAND"."SLT01"))
      LEADING(@"SEL$312E5F5B" "B"@"SEL$2" "A"@"SEL$2" "QCD_STAND"@"SEL$4")
      USE_NL(@"SEL$312E5F5B" "A"@"SEL$2")
      USE_NL(@"SEL$312E5F5B" "QCD_STAND"@"SEL$4")
      USE_HASH_AGGREGATION(@"SEL$312E5F5B")
      INDEX(@"SEL$5" "QCD_MASTER"@"SEL$5" ("QCD_MASTER"."PLANT" "QCD_MASTER"."TSTCLS" "QCD_MASTER"."SNO"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=50)
   3 - filter(ROWNUM<=50)
   4 - filter(SUM(DECODE("A"."JUDGE",'0',1,0))<>0)
   6 - filter( IS NULL)
   9 - filter("B"."INSPCHASU"=1)
  10 - access("B"."PLANT"='E' AND "B"."TSTCLS"='VB' AND "B"."DBKEY">='201508170800' AND "B"."DBKEY"<='201508180759')
  12 - access("A"."DBKEY"="B"."DBKEY" AND "A"."PLANT"='E' AND "A"."TSTCLS"='VB' AND "A"."HOGI"="B"."HOGI" AND "A"."MIP"="B"."MIP")
       filter(("A"."DBKEY">='201508170800' AND "A"."DBKEY"<='201508180759' AND "A"."MIP"="B"."MIP"))
  13 - access("PLANT"='E' AND "TSTCLS"='VB' AND "A"."ITEM"="ITEM" AND "SLT01"='1')
  14 - access("PLANT"=:B1 AND "TSTCLS"=:B2 AND "SNO"=:B3)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."STEPNAME"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7],
       "A"."TOTCNT"[NUMBER,22], "A"."NGCNT"[NUMBER,22], "A"."RATE"[NUMBER,22], ROWNUM[4]
   2 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."STEPNAME"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7],
       "A"."TOTCNT"[NUMBER,22], "A"."NGCNT"[NUMBER,22], "A"."RATE"[NUMBER,22]
   3 - (#keys=3) ROUND(SUM(DECODE("A"."JUDGE",'0',1,0))*100/COUNT(*),2)[22], COUNT(*)[22], "A"."STEP"[NUMBER,22], "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3],
       "A"."MIP"[VARCHAR2,30], SUM(DECODE("A"."JUDGE",'0',1,0))[22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7]
   4 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], COUNT(*)[22],
       SUM(DECODE("A"."JUDGE",'0',1,0))[22]
   5 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], COUNT(*)[22],
       SUM(DECODE("A"."JUDGE",'0',1,0))[22]
   6 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7],
       "A"."JUDGE"[VARCHAR2,1]
   7 - "B"."PLANT"[VARCHAR2,1], "B"."TSTCLS"[VARCHAR2,2], "B"."SNO"[VARCHAR2,20], "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30],
       "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], "A"."JUDGE"[VARCHAR2,1]
   8 - "B"."PLANT"[VARCHAR2,1], "B"."TSTCLS"[VARCHAR2,2], "B"."SNO"[VARCHAR2,20], "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30],
       "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], "A"."JUDGE"[VARCHAR2,1]
   9 - "B"."PLANT"[VARCHAR2,1], "B"."TSTCLS"[VARCHAR2,2], "B"."HOGI"[VARCHAR2,3], "B"."DBKEY"[VARCHAR2,30], "B"."MIP"[VARCHAR2,30], "B"."SNO"[VARCHAR2,20]
  10 - "B".ROWID[ROWID,10], "B"."PLANT"[VARCHAR2,1], "B"."TSTCLS"[VARCHAR2,2], "B"."DBKEY"[VARCHAR2,30]
  11 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7],
       "A"."JUDGE"[VARCHAR2,1]
  12 - "A".ROWID[ROWID,10], "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."ITEM"[VARCHAR2,7], "A"."STEP"[NUMBER,22], "A"."MIP"[VARCHAR2,30]


 

 

by 마농 [2015.08.31 08:58:05]

NOT IN 을 NOT EXISTS 로 바꿔보세요.


by 콩나무 [2015.08.31 10:08:09]

결과가 다르게 나옵니다


by 마농 [2015.08.31 10:29:11]

잘못 바꾸신 듯.


by 마농 [2015.08.31 10:47:26]
AND NOT EXISTS (SELECT 1
                  FROM qcd_master
                 WHERE plant  = b.plant
                   AND tstcls = b.tstcls
                   AND sno    = b.tmcode
                )

 


by 콩나무 [2015.08.31 13:39:02]

네 제가 잘못 바꿧네요 바꾸었지만 일단 부하가 되는 UPDETAIL 쪽 랜덤엑세스는 그대로인것 같아요

말씀하신 부분도 버퍼가 줄어들었습니다 감사합니다.


by 부쉬맨 [2015.08.31 10:36:20]

플랜이나 정확한 정보가 없지만

일단 변경초점을 QWK_UPDETAIL 가 드라이브되어서 

하단의 in이나 exists 로 빠지는 부분에서 반복 access를 방지하셔야됩니다.

저정도 데이터라하면 full로 자동으로 풀리긴하겟지만

하단의 서브쿼리부분을 filter 조건으로 푸는걸 목적으로 두시고

head 테이블과 하단의 서브쿼리절을 먼저 드라이빙하시고 (unnest라든지 no_unnest 힌트 참조)

tail테이블과 조인될 수 있게 조정해보세요.

 


by 콩나무 [2015.08.31 13:40:12]

플랜 추가 하였습니다!! 의견 감사합니다.


by 부쉬맨 [2015.08.31 13:53:36]

플랜만보면 해당 tail 인덱스를 읽고 dbkey등의 다른 컬럼의 값을 찾으로 table access 한 비용이 높게 잡혀있으므로 그냥 해당 테이블에 대해서 index -> table 을 가지않고

table만 읽게 1안

2안은 tail 테이블을 full 로 읽을경우 해당 쿼리의 플랜이 변경 될 여지가 있으므로

leading흰트

SELECT A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.STEPNAME,
  A.ITEM,
  TO_CHAR(A.TOTCNT) AS TOTCNT,
  TO_CHAR(A.NGCNT) AS NGCNT, A.RATE AS RATE, 'DATA' AS DATA,
  ROWNUM AS TOP
FROM
  (
    --SELECT  /*+ full(a) */   1안
	--SELECT  /*+ leading(b a) full(a) */ 2안
    A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.COMMENT1 AS STEPNAME,
      A.ITEM,
      COUNT(*) AS TOTCNT,
      SUM(DECODE(A.JUDGE, '0', 1, 0)) AS NGCNT,
      ROUND(SUM(DECODE(A.JUDGE, '0', 1, 0)) * 100 / COUNT(*),
        2) AS RATE
    FROM QWK_UPDETAIL A
      INNER JOIN QWK_UPHEAD B ON A.PLANT = B.PLANT
      AND A.TSTCLS = B.TSTCLS AND A.HOGI = B.HOGI AND A.MIP = B.MIP
      AND A.DBKEY = B.DBKEY
    WHERE B.PLANT = 'E'
      AND B.TSTCLS = 'VB'
      AND B.DBKEY >= '201508170759'
      AND B.DBKEY <= '201508180759'
      AND NOT EXISTS (
        SELECT 'X'
        FROM QCD_STAND
        WHERE PLANT = B.PLANT
          AND TSTCLS = B.TSTCLS
          AND A.ITEM = ITEM
          AND SLT01 = '1'
        )
      AND B.TMCODE NOT IN (
        SELECT SNO
        FROM QCD_MASTER
        WHERE PLANT = B.PLANT
          AND TSTCLS = B.TSTCLS
        )
      AND B.INSPCHASU = '1'
    GROUP BY A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.COMMENT1,
      A.ITEM
  )A
WHERE ROWNUM <= 50
  AND A.NGCNT != 0

 


by 콩나무 [2015.08.31 15:55:44]

시도를 해보왔는데 엄청 느려지는 것 같아요 일단 updetail 건이 19억건정도 table이다보니

full 로 바꾸면 b와 a가 hash 조인으로 풀리면서 uphaed에서 걸러진 3만건정도와 19억건이

조인되는것 같습니다. 지금은 하루치라 평상시 쿼리 플랜으로도 어느정도 나오는데 1달정도가 되면 건수가 더많아져서 updetail쪽 엑세스하는 건수 + 랜덤엑세스 이걸 줄이려고 하는데 잘안되네요 

감사합니다.


by 부쉬맨 [2015.08.31 16:37:12]

filter(("A"."DBKEY">='201508170800' AND "A"."DBKEY"<='201508180759' AND "A"."MIP"="B"."MIP"))

여부분 이 핃터로 풀리는데

해당 필터로 풀리는 2개의 컬럼의 결합인덱스를 만들어보세요.


by 아발란체 [2015.08.31 11:05:34]

최종 50건씩 보는 관점에서 풀어볼려니 많이 복잡해보이네영.... OTL

파티션 정책에 따라 영향도 있어보이지만

정렬 기준이 명확하지 않은 것 같은데, 먼저 50건을 빠르게 추출하고 해당 50건에 대해 TOTCNT와 NGCNT를 구하는 것이 어떨까하네용..

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입