튜닝 이슈 ( ROWNUM 으로 COUNT STOPKEY 플랜으로 필터링 하니까 엄청 느려져요) 0 2 8,312

by 강명구 [Oracle Tuning] oracle 튜닝 tuning [2011.12.01 20:54:38]


SELECT
     ROWNUM rn, src.cid
FROM (
                 SELECT /*+ ordered use_nl(csrc, pop) */
                     pop.cid
                    FROM
                    (
                      SELECT /*+ index(am XPK_AM) */ 
                        DISTINCT con.cid, 'aaaa' AS mid
                      FROM
                        cat, con, am
                      WHERE cat.adate = '20111112095102' 
                        AND cat.full LIKE 'CGV%'
                        AND con.adate = cat.adate
                        AND con.id = cat.id
                        AND con.cid NOT IN  ( 'MG4', 'MG5', 'M01') 
                        AND con.adate = am.adate
                        AND con.cid = am.cid
                    ) csrc,  pop 
                    WHERE pop.mid = csrc.mid 
                      AND pop.cid = csrc.cid
                      AND pop.cid <> 'MG49' 
                    ORDER BY pop.point DESC
)src where ROWNUM < 100


빨강색 부분의 내부 쿼리는 0.3초 이내에 끝나는데
외부에 ROWNUM 으로 필터링을 걸어 주니까
몇십초 거의 1분이상 걸립니다.
어떻게 튜닝을 해야할지 문의드립니다.
필터링 걸면 더 빨라질 줄 알았는데 안 그러네요!!!

아래는 PLAN 입니다.

--------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           `        `             |                                 |     1 |    22 |   216     (2)| 00:00:03 |
|*  1 |  COUNT STOPKEY                                        |                                    |       |       |              |             |
|   2 |   VIEW                                                        |                                    |     1 |    22 |   216     (2)| 00:00:03 |
|*  3 |    SORT ORDER BY STOPKEY                 |                                    |     1 |    35 |   216     (2)| 00:00:03 |
|   4 |     VIEW                                                    | VM_NWVW_1           |     1 |    35 |   215     (2)| 00:00:03 |
|   5 |      HASH UNIQUE                                    |                                    |     1 |   219 |   215     (2)| 00:00:03 |
|   6 |       NESTED LOOPS                               |                                    |     3 |   657 |   214     (1)| 00:00:03 |
|   7 |        NESTED LOOPS                                |                                   |     2 |   300 |     7     (0)| 00:00:01 |
|   8 |     NESTED LOOPS                                     |                                   |     2 |   244 |     5     (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID         | CAT                        |     1 |    82 |     4     (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN                            | IDX2_CAT                |     1 |       |     3     (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN                                | IDXCON_01               |     4 |   160 |     1     (0)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN                             | XPK_AM                |     1 |    28 |     1     (0)| 00:00:01 |
|* 13 |        TABLE ACCESS FULL                 | POP                   |     1 |    69 |   104     (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------




이 PLAN 은 빨강색으로 된 내부 쿼리 PLAN 입니다.
--------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|*  1 |    SORT ORDER B                           |                                    |     1 |    35 |   216     (2)| 00:00:03 |
|   2 |     VIEW                                                    | VM_NWVW_1           |     1 |    35 |   215     (2)| 00:00:03 |
|   3 |      HASH UNIQUE                                    |                                    |     1 |   219 |   215     (2)| 00:00:03 |
|   4 |       NESTED LOOPS                               |                                    |     3 |   657 |   214     (1)| 00:00:03 |
|   5 |        NESTED LOOPS                                |                                   |     2 |   300 |     7     (0)| 00:00:01 |
|   6 |     NESTED LOOPS                                     |                                   |     2 |   244 |     5     (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID         | CAT                        |     1 |    82 |     4     (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN                            | IDX2_CAT                |     1 |       |     3     (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                                | IDXCON_01               |     4 |   160 |     1     (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN                             | XPK_AM                |     1 |    28 |     1     (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL                 | POP          |     1 |    69 |   104     (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------
by 마농 [2011.12.02 10:32:03]
/*+ ordered use_nl(csrc, pop) */ 이 힌트 때문에
nested loop 돌면서 pop 을 반복해서 풀스캔 하네요
힌트를 빼세요.

by 이재현 [2011.12.02 10:32:24]
pop 테이블에 총 몇건이나 존재하나요??
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입