|
안녕하세요.
다른 DB를 사용하다 오라클을 사용하니 좋기는 한데 여러 가지 낮선 것이 많네요.
궁금한 것이 있어 고수님께 자문좀 듣고 싶어서요...
아래 쿼리가 where절에 입력값이 변경 될때 마다 다른 실행계획을 타고 있습니다.
원인이 뭔지 모르겠습니다.
데이터는 대량 600,000건 정도 되는데
SELECT
- 생략 -
FROM CSSOWN.WC_PBSC A
, CSSOWN.WC_PBSCP B
, CSSOWN.WC_PBSCI C
, CSSOWN.WC_PBSA D
, CSSOWN.WC_PBSAP E
, CSSOWN.WC_PBSAI F
, (SELECT
T.CS_NO ,
MAX(P.PLLO_MW12_GTST_DLQ_MON_CN) AS PLLO_MW12_GTST_DLQ_MON_CN ,
MAX(P.PLLO_MW6_GTST_DLQ_MON_CN) AS PLLO_MW6_GTST_DLQ_MON_CN
FROM CSSOWN.WC_PBSA T ,
CSSOWN.WC_PBSAP P
WHERE T.STR_YM = '201307'
AND P.INON_NO = T.INON_NO
AND P.STR_YM = T.STR_YM
GROUP BY T.CS_NO) T
, (SELECT A1.STR_YM, A1.INON_NO, A1.CSS_TLON_AM, A1.CSS_LON_AM, A1.CSS_LNLI_AM, A1.CSS_INT_AM, A1.CSS_PAM_AM, A1.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A1 WHERE A1.STR_YM = '201307') OCCUR1
, (SELECT A2.INON_NO, A2.CSS_TLON_AM, A2.CSS_LON_AM, A2.CSS_LNLI_AM, A2.CSS_INT_AM, A2.CSS_PAM_AM, A2.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A2 WHERE A2.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -1), 'yyyymm')) OCCUR2
, (SELECT A3.INON_NO, A3.CSS_TLON_AM, A3.CSS_LON_AM, A3.CSS_LNLI_AM, A3.CSS_INT_AM, A3.CSS_PAM_AM, A3.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A3 WHERE A3.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -2), 'yyyymm')) OCCUR3
, (SELECT A4.INON_NO, A4.CSS_TLON_AM, A4.CSS_LON_AM, A4.CSS_LNLI_AM, A4.CSS_INT_AM, A4.CSS_PAM_AM, A4.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A4 WHERE A4.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -3), 'yyyymm')) OCCUR4
, (SELECT A5.INON_NO, A5.CSS_TLON_AM, A5.CSS_LON_AM, A5.CSS_LNLI_AM, A5.CSS_INT_AM, A5.CSS_PAM_AM, A5.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A5 WHERE A5.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -4), 'yyyymm')) OCCUR5
, (SELECT A6.INON_NO, A6.CSS_TLON_AM, A6.CSS_LON_AM, A6.CSS_LNLI_AM, A6.CSS_INT_AM, A6.CSS_PAM_AM, A6.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A6 WHERE A6.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -5), 'yyyymm')) OCCUR6
, (SELECT A7.INON_NO, A7.CSS_TLON_AM, A7.CSS_LON_AM, A7.CSS_LNLI_AM, A7.CSS_INT_AM, A7.CSS_PAM_AM, A7.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A7 WHERE A7.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -6), 'yyyymm')) OCCUR7
, (SELECT A8.INON_NO, A8.CSS_TLON_AM, A8.CSS_LON_AM, A8.CSS_LNLI_AM, A8.CSS_INT_AM, A8.CSS_PAM_AM, A8.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A8 WHERE A8.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -7), 'yyyymm')) OCCUR8
, (SELECT A9.INON_NO, A9.CSS_TLON_AM, A9.CSS_LON_AM, A9.CSS_LNLI_AM, A9.CSS_INT_AM, A9.CSS_PAM_AM, A9.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A9 WHERE A9.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -8), 'yyyymm')) OCCUR9
, (SELECT A10.INON_NO, A10.CSS_TLON_AM, A10.CSS_LON_AM, A10.CSS_LNLI_AM, A10.CSS_INT_AM, A10.CSS_PAM_AM, A10.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A10 WHERE A10.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -8), 'yyyymm')) OCCUR10
, (SELECT A11.INON_NO, A11.CSS_TLON_AM, A11.CSS_LON_AM, A11.CSS_LNLI_AM, A11.CSS_INT_AM, A11.CSS_PAM_AM, A11.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A11 WHERE A11.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -10), 'yyyymm')) OCCUR11
, (SELECT A12.INON_NO, A12.CSS_TLON_AM, A12.CSS_LON_AM, A12.CSS_LNLI_AM, A12.CSS_INT_AM, A12.CSS_PAM_AM, A12.CSS_DLQ_DAY_TC
FROM CSSOWN.WC_PBSAPM A12 WHERE A12.STR_YM = to_char(add_months(to_date('201307', 'yyyymm'), -11), 'yyyymm')) OCCUR12
WHERE A.STR_YM = '201307'
AND B.STR_YM = A.STR_YM
AND B.CS_NO = A.CS_NO
AND C.STR_YM = A.STR_YM
AND C.CS_NO = A.CS_NO
AND D.STR_YM = A.STR_YM
AND D.CS_NO = A.CS_NO
AND E.STR_YM = A.STR_YM
AND E.INON_NO = D.INON_NO
AND F.STR_YM = A.STR_YM
AND F.INON_NO = D.INON_NO
AND T.CS_NO = A.CS_NO
AND OCCUR1.INON_NO = D.INON_NO
AND OCCUR2.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR3.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR4.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR5.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR6.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR7.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR8.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR9.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR10.INON_NO(+) = OCCUR1.INON_NO
AND OCCUR11.INON_NO(+) = OCCUR1.INON_NO
쿼리 실행결과가 잘 나왔을 때 (빠르게 나왔을 때) 실행 계획이구요
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 401K| 336M| | 239K (1)| 00:47:55 | | |
|* 1 | HASH JOIN RIGHT OUTER | | 401K| 336M| 34M| 239K (1)| 00:47:55 | | |
| 2 | PARTITION RANGE SINGLE | | 603K| 27M| | 3031 (1)| 00:00:37 | 24 | 24 |
|* 3 | TABLE ACCESS FULL | WC_PBSAPM | 603K| 27M| | 3031 (1)| 00:00:37 | 24 | 24 |
|* 4 | HASH JOIN RIGHT OUTER | | 401K| 318M| 34M| 218K (1)| 00:43:46 | | |
| 5 | PARTITION RANGE SINGLE | | 599K| 27M| | 3031 (1)| 00:00:37 | 23 | 23 |
|* 6 | TABLE ACCESS FULL | WC_PBSAPM | 599K| 27M| | 3031 (1)| 00:00:37 | 23 | 23 |
|* 7 | HASH JOIN RIGHT OUTER | | 401K| 299M| 34M| 198K (1)| 00:39:48 | | |
| 8 | PARTITION RANGE SINGLE | | 594K| 27M| | 3031 (1)| 00:00:37 | 22 | 22 |
|* 9 | TABLE ACCESS FULL | WC_PBSAPM | 594K| 27M| | 3031 (1)| 00:00:37 | 22 | 22 |
|* 10 | HASH JOIN RIGHT OUTER | | 401K| 281M| 33M| 180K (1)| 00:36:01 | | |
| 11 | PARTITION RANGE SINGLE | | 593K| 27M| | 3031 (1)| 00:00:37 | 21 | 21 |
|* 12 | TABLE ACCESS FULL | WC_PBSAPM | 593K| 27M| | 3031 (1)| 00:00:37 | 21 | 21 |
|* 13 | HASH JOIN RIGHT OUTER | | 401K| 263M| 33M| 162K (1)| 00:32:25 | | |
| 14 | PARTITION RANGE SINGLE | | 587K| 26M| | 3030 (1)| 00:00:37 | 20 | 20 |
|* 15 | TABLE ACCESS FULL | WC_PBSAPM | 587K| 26M| | 3030 (1)| 00:00:37 | 20 | 20 |
|* 16 | HASH JOIN RIGHT OUTER | | 401K| 244M| 33M| 144K (1)| 00:29:00 | | |
| 17 | PARTITION RANGE SINGLE | | 584K| 26M| | 3030 (1)| 00:00:37 | 18 | 18 |
|* 18 | TABLE ACCESS FULL | WC_PBSAPM | 584K| 26M| | 3030 (1)| 00:00:37 | 18 | 18 |
|* 19 | HASH JOIN RIGHT OUTER | | 401K| 226M| 33M| 128K (1)| 00:25:46 | | |
| 20 | PARTITION RANGE SINGLE | | 579K| 26M| | 3030 (1)| 00:00:37 | 19 | 19 |
|* 21 | TABLE ACCESS FULL | WC_PBSAPM | 579K| 26M| | 3030 (1)| 00:00:37 | 19 | 19 |
|* 22 | HASH JOIN RIGHT OUTER | | 401K| 208M| 32M| 113K (1)| 00:22:44 | | |
| 23 | PARTITION RANGE SINGLE | | 570K| 26M| | 3030 (1)| 00:00:37 | 17 | 17 |
|* 24 | TABLE ACCESS FULL | WC_PBSAPM | 570K| 26M| | 3030 (1)| 00:00:37 | 17 | 17 |
|* 25 | HASH JOIN RIGHT OUTER | | 401K| 189M| 32M| 99293 (1)| 00:19:52 | | |
| 26 | PARTITION RANGE SINGLE | | 570K| 26M| | 3030 (1)| 00:00:37 | 17 | 17 |
|* 27 | TABLE ACCESS FULL | WC_PBSAPM | 570K| 26M| | 3030 (1)| 00:00:37 | 17 | 17 |
|* 28 | HASH JOIN RIGHT OUTER | | 401K| 171M| 32M| 85908 (1)| 00:17:11 | | |
| 29 | PARTITION RANGE SINGLE | | 563K| 25M| | 3030 (1)| 00:00:37 | 15 | 15 |
|* 30 | TABLE ACCESS FULL | WC_PBSAPM | 563K| 25M| | 3030 (1)| 00:00:37 | 15 | 15 |
|* 31 | HASH JOIN RIGHT OUTER | | 401K| 153M| 32M| 73456 (1)| 00:14:42 | | |
| 32 | PARTITION RANGE SINGLE | | 561K| 25M| | 3030 (1)| 00:00:37 | 14 | 14 |
|* 33 | TABLE ACCESS FULL | WC_PBSAPM | 561K| 25M| | 3030 (1)| 00:00:37 | 14 | 14 |
|* 34 | HASH JOIN | | 401K| 134M| 34M| 61918 (1)| 00:12:24 | | |
| 35 | PARTITION RANGE SINGLE | | 605K| 27M| | 3314 (1)| 00:00:40 | 25 | 25 |
|* 36 | TABLE ACCESS FULL | WC_PBSAPM | 605K| 27M| | 3314 (1)| 00:00:40 | 25 | 25 |
|* 37 | HASH JOIN | | 401K| 116M| 19M| 50881 (1)| 00:10:11 | | |
| 38 | PART JOIN FILTER CREATE | :BF0000 | 607K| 12M| | 2893 (1)| 00:00:35 | | |
| 39 | PARTITION RANGE SINGLE | | 607K| 12M| | 2893 (1)| 00:00:35 | 12 | 12 |
|* 40 | TABLE ACCESS FULL | WC_PBSAP | 607K| 12M| | 2893 (1)| 00:00:35 | 12 | 12 |
|* 41 | HASH JOIN | | 401K| 107M| 20M| 41424 (1)| 00:08:18 | | |
| 42 | PART JOIN FILTER CREATE | :BF0001 | 605K| 13M| | 3315 (1)| 00:00:40 | | |
| 43 | PARTITION RANGE SINGLE | | 605K| 13M| | 3315 (1)| 00:00:40 | 25 | 25 |
|* 44 | TABLE ACCESS FULL | WC_PBSAI | 605K| 13M| | 3315 (1)| 00:00:40 | 25 | 25 |
|* 45 | HASH JOIN | | 401K| 99M| 37M| 31952 (1)| 00:06:24 | | |
| 46 | PART JOIN FILTER CREATE | :BF0002 | 605K| 30M| | 3314 (1)| 00:00:40 | | |
| 47 | PARTITION RANGE SINGLE | | 605K| 30M| | 3314 (1)| 00:00:40 | 25 | 25 |
|* 48 | TABLE ACCESS FULL | WC_PBSA | 605K| 30M| | 3314 (1)| 00:00:40 | 25 | 25 |
|* 49 | HASH JOIN | | 227K| 44M| 25M| 24424 (1)| 00:04:54 | | |
| 50 | PART JOIN FILTER CREATE | :BF0003 | 343K| 21M| | 1793 (1)| 00:00:22 | | |
| 51 | PARTITION RANGE SINGLE | | 343K| 21M| | 1793 (1)| 00:00:22 | 25 | 25 |
|* 52 | TABLE ACCESS FULL | WC_PBSCP | 343K| 21M| | 1793 (1)| 00:00:22 | 25 | 25 |
|* 53 | HASH JOIN | | 227K| 30M| 18M| 19722 (1)| 00:03:57 | | |
| 54 | PART JOIN FILTER CREATE | :BF0004 | 341K| 14M| | 2770 (2)| 00:00:34 | | |
| 55 | PARTITION RANGE SINGLE | | 341K| 14M| | 2770 (2)| 00:00:34 | 25 | 25 |
|* 56 | TABLE ACCESS FULL | WC_PBSCI | 341K| 14M| | 2770 (2)| 00:00:34 | 25 | 25 |
|* 57 | HASH JOIN | | 228K| 20M| 10M| 14877 (1)| 00:02:59 | | |
| 58 | VIEW | | 228K| 7574K| | 11454 (1)| 00:02:18 | | |
| 59 | HASH GROUP BY | | 228K| 12M| 41M| 11454 (1)| 00:02:18 | | |
|* 60 | HASH JOIN | | 605K| 32M| 21M| 6446 (1)| 00:01:18 | | |
| 61 | PARTITION RANGE SINGLE| | 607K| 14M| | 2899 (2)| 00:00:35 | 12 | 12 |
|* 62 | TABLE ACCESS FULL | WC_PBSAP | 607K| 14M| | 2899 (2)| 00:00:35 | 12 | 12 |
| 63 | PARTITION RANGE SINGLE| | 605K| 18M| | 1212 (2)| 00:00:15 | 25 | 25 |
|* 64 | INDEX FAST FULL SCAN | WC_PBSA_PK | 605K| 18M| | 1212 (2)| 00:00:15 | 25 | 25 |
| 65 | PARTITION RANGE SINGLE | | 342K| 20M| | 1720 (1)| 00:00:21 |KEY(AP)|KEY(AP)|
|* 66 | TABLE ACCESS FULL | WC_PBSC | 342K| 20M| | 1720 (1)| 00:00:21 | 25 | 25 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A2"."INON_NO"(+)="A1"."INON_NO")
3 - filter("A2"."STR_YM"(+)='201306')
4 - access("A3"."INON_NO"(+)="A1"."INON_NO")
6 - filter("A3"."STR_YM"(+)='201305')
7 - access("A4"."INON_NO"(+)="A1"."INON_NO")
9 - filter("A4"."STR_YM"(+)='201304')
10 - access("A5"."INON_NO"(+)="A1"."INON_NO")
12 - filter("A5"."STR_YM"(+)='201303')
13 - access("A6"."INON_NO"(+)="A1"."INON_NO")
15 - filter("A6"."STR_YM"(+)='201302')
16 - access("A8"."INON_NO"(+)="A1"."INON_NO")
18 - filter("A8"."STR_YM"(+)='201212')
19 - access("A7"."INON_NO"(+)="A1"."INON_NO")
21 - filter("A7"."STR_YM"(+)='201301')
22 - access("A10"."INON_NO"(+)="A1"."INON_NO")
24 - filter("A10"."STR_YM"(+)='201211')
25 - access("A9"."INON_NO"(+)="A1"."INON_NO")
27 - filter("A9"."STR_YM"(+)='201211')
28 - access("A11"."INON_NO"(+)="A1"."INON_NO")
30 - filter("A11"."STR_YM"(+)='201209')
31 - access("A12"."INON_NO"(+)="A1"."INON_NO")
33 - filter("A12"."STR_YM"(+)='201208')
34 - access("A1"."INON_NO"="D"."INON_NO")
36 - filter("A1"."STR_YM"='201307')
37 - access("E"."STR_YM"="A"."STR_YM" AND "E"."INON_NO"="D"."INON_NO")
40 - filter("E"."STR_YM"='201307')
41 - access("F"."STR_YM"="A"."STR_YM" AND "F"."INON_NO"="D"."INON_NO")
44 - filter("F"."STR_YM"='201307')
45 - access("D"."STR_YM"="A"."STR_YM" AND "D"."CS_NO"="A"."CS_NO")
48 - filter("D"."STR_YM"='201307')
49 - access("B"."STR_YM"="A"."STR_YM" AND "B"."CS_NO"="A"."CS_NO")
52 - filter("B"."STR_YM"='201307')
53 - access("C"."STR_YM"="A"."STR_YM" AND "C"."CS_NO"="A"."CS_NO")
56 - filter("C"."STR_YM"='201307')
57 - access("T"."CS_NO"="A"."CS_NO")
60 - access("P"."INON_NO"="T"."INON_NO" AND "P"."STR_YM"="T"."STR_YM")
62 - filter("P"."STR_YM"='201307')
64 - filter("T"."STR_YM"='201307')
쿼리 실행결과가 안 나왔을 때 실행 계획입니다.
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1188 | 13 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS OUTER | | 1 | 1188 | 13 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 1140 | 12 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS OUTER | | 1 | 1092 | 11 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS OUTER | | 1 | 1044 | 10 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS OUTER | | 1 | 996 | 9 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS OUTER | | 1 | 948 | 8 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS OUTER | | 1 | 900 | 7 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS OUTER | | 1 | 852 | 6 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 804 | 5 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS OUTER | | 1 | 756 | 4 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS OUTER | | 1 | 708 | 3 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 660 | 2 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1 | 567 | 2 (0)| 00:00:01 | | |
| 14 | NESTED LOOPS | | 1 | 545 | 1 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | 1 | 519 | 0 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 491 | 0 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS | | 1 | 445 | 0 (0)| 00:00:01 | | |
| 18 | NESTED LOOPS | | 1 | 325 | 0 (0)| 00:00:01 | | |
| 19 | PARTITION RANGE SINGLE | | 1 | 182 | 0 (0)| 00:00:01 | 26 | 26 |
| 20 | TABLE ACCESS BY LOCAL INDEX ROWID| WC_PBSCP | 1 | 182 | 0 (0)| 00:00:01 | 26 | 26 |
|* 21 | INDEX RANGE SCAN | WC_PBSCP_PK | 1 | | 0 (0)| 00:00:01 | 26 | 26 |
| 22 | PARTITION RANGE SINGLE | | 1 | 143 | 0 (0)| 00:00:01 | 26 | 26 |
| 23 | TABLE ACCESS BY LOCAL INDEX ROWID| WC_PBSC | 1 | 143 | 0 (0)| 00:00:01 | 26 | 26 |
|* 24 | INDEX UNIQUE SCAN | WC_PBSC_PK | 1 | | 0 (0)| 00:00:01 | 26 | 26 |
| 25 | PARTITION RANGE SINGLE | | 1 | 120 | 0 (0)| 00:00:01 | 26 | 26 |
| 26 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSCI | 1 | 120 | 0 (0)| 00:00:01 | 26 | 26 |
|* 27 | INDEX UNIQUE SCAN | WC_PBSCI_PK | 1 | | 0 (0)| 00:00:01 | 26 | 26 |
| 28 | PARTITION RANGE SINGLE | | 1 | 46 | 0 (0)| 00:00:01 | 26 | 26 |
| 29 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSA | 1 | 46 | 0 (0)| 00:00:01 | 26 | 26 |
|* 30 | INDEX RANGE SCAN | WC_PBSA_PK | 1 | | 0 (0)| 00:00:01 | 26 | 26 |
| 31 | PARTITION RANGE SINGLE | | 1 | 28 | 0 (0)| 00:00:01 | 26 | 26 |
| 32 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAI | 1 | 28 | 0 (0)| 00:00:01 | 26 | 26 |
|* 33 | INDEX UNIQUE SCAN | WC_PBSAI_PK | 1 | | 0 (0)| 00:00:01 | 26 | 26 |
| 34 | VIEW PUSHED PREDICATE | | 1 | 26 | 1 (0)| 00:00:01 | | |
|* 35 | FILTER | | | | | | | |
| 36 | SORT AGGREGATE | | 1 | 48 | | | | |
| 37 | NESTED LOOPS | | | | | | | |
| 38 | NESTED LOOPS | | 1 | 48 | 1 (0)| 00:00:01 | | |
| 39 | PARTITION RANGE SINGLE | | 1 | 23 | 0 (0)| 00:00:01 | 26 | 26 |
|* 40 | INDEX RANGE SCAN | WC_PBSA_PK | 1 | 23 | 0 (0)| 00:00:01 | 26 | 26 |
| 41 | PARTITION RANGE SINGLE | | 1 | | 1 (0)| 00:00:01 | 13 | 13 |
|* 42 | INDEX UNIQUE SCAN | WC_PBSAP_PK | 1 | | 1 (0)| 00:00:01 | 13 | 13 |
| 43 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAP | 1 | 25 | 1 (0)| 00:00:01 | 13 | 13 |
| 44 | PARTITION RANGE SINGLE | | 1 | 22 | 1 (0)| 00:00:01 | 13 | 13 |
| 45 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAP | 1 | 22 | 1 (0)| 00:00:01 | 13 | 13 |
|* 46 | INDEX UNIQUE SCAN | WC_PBSAP_PK | 1 | | 1 (0)| 00:00:01 | 13 | 13 |
| 47 | PARTITION RANGE SINGLE | | 1 | 93 | 0 (0)| 00:00:01 | 26 | 26 |
| 48 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 93 | 0 (0)| 00:00:01 | 26 | 26 |
|* 49 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 0 (0)| 00:00:01 | 26 | 26 |
| 50 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 15 | 15 |
| 51 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 15 | 15 |
|* 52 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 15 | 15 |
| 53 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 16 | 16 |
| 54 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 16 | 16 |
|* 55 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 16 | 16 |
| 56 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 19 | 19 |
| 57 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 19 | 19 |
|* 58 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 19 | 19 |
| 59 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 18 | 18 |
| 60 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 18 | 18 |
|* 61 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 18 | 18 |
| 62 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 18 | 18 |
| 63 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 18 | 18 |
|* 64 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 18 | 18 |
| 65 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 20 | 20 |
| 66 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 20 | 20 |
|* 67 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 20 | 20 |
| 68 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 21 | 21 |
| 69 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 21 | 21 |
|* 70 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 21 | 21 |
| 71 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 22 | 22 |
| 72 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 22 | 22 |
|* 73 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 22 | 22 |
| 74 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 23 | 23 |
| 75 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 23 | 23 |
|* 76 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 23 | 23 |
| 77 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 24 | 24 |
| 78 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 24 | 24 |
|* 79 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 24 | 24 |
| 80 | PARTITION RANGE SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 25 | 25 |
| 81 | TABLE ACCESS BY LOCAL INDEX ROWID | WC_PBSAPM | 1 | 48 | 1 (0)| 00:00:01 | 25 | 25 |
|* 82 | INDEX UNIQUE SCAN | WC_PBSAPM_PK | 1 | | 1 (0)| 00:00:01 | 25 | 25 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
21 - access("B"."STR_YM"='201308')
24 - access("A"."STR_YM"='201308' AND "B"."CS_NO"="A"."CS_NO")
27 - access("C"."STR_YM"='201308' AND "C"."CS_NO"="A"."CS_NO")
30 - access("D"."STR_YM"='201308' AND "D"."CS_NO"="A"."CS_NO")
filter("D"."CS_NO"="A"."CS_NO")
33 - access("F"."STR_YM"='201308' AND "F"."INON_NO"="D"."INON_NO")
35 - filter(COUNT(*)>0)
40 - access("T"."STR_YM"='201308' AND "T"."CS_NO"="A"."CS_NO")
filter("T"."CS_NO"="A"."CS_NO")
42 - access("P"."STR_YM"='201308' AND "P"."INON_NO"="T"."INON_NO")
46 - access("E"."STR_YM"='201308' AND "E"."INON_NO"="D"."INON_NO")
49 - access("A1"."STR_YM"='201308' AND "A1"."INON_NO"="D"."INON_NO")
52 - access("A12"."STR_YM"(+)='201209' AND "A12"."INON_NO"(+)="A1"."INON_NO")
55 - access("A11"."STR_YM"(+)='201210' AND "A11"."INON_NO"(+)="A1"."INON_NO")
58 - access("A8"."STR_YM"(+)='201301' AND "A8"."INON_NO"(+)="A1"."INON_NO")
61 - access("A9"."STR_YM"(+)='201212' AND "A9"."INON_NO"(+)="A1"."INON_NO")
64 - access("A10"."STR_YM"(+)='201212' AND "A10"."INON_NO"(+)="A1"."INON_NO")
67 - access("A7"."STR_YM"(+)='201302' AND "A7"."INON_NO"(+)="A1"."INON_NO")
70 - access("A6"."STR_YM"(+)='201303' AND "A6"."INON_NO"(+)="A1"."INON_NO")
73 - access("A5"."STR_YM"(+)='201304' AND "A5"."INON_NO"(+)="A1"."INON_NO")
76 - access("A4"."STR_YM"(+)='201305' AND "A4"."INON_NO"(+)="A1"."INON_NO")
79 - access("A3"."STR_YM"(+)='201306' AND "A3"."INON_NO"(+)="A1"."INON_NO")
위 쿼리에서 년월만 201307 이면 잘돌고 201308 이면 완전히 느려 집니다.
제가 오라클 Hint로 이것 저것 해봤는데 Hint에 낮설고 잘 모르겠더라구요.
해결 방법이 있을까요? 고수님들에 자문 부탁드리겠습니다.
|
