아래의 sql문을 trace 를 분석한 결과입니다.
이 경우는 동일한 cd로 oracle을 설치하고
동일한 dmp 파일을 import 하여 사용하였습니다.
서버는 win2k 서비스팩4, ORACLE 9.2.0.1
단 차이는 사례1은 raid-5 구성이 되어 있고 사례2는 없다는
것입니다.
F1111_K TABLE 은 PK가 IDNO NUMBER(10) 으로 만 되어있고
다른 INDEX는 없습니다.
그리고 또 이상한 것은
사례1의 경우에
OPTIMIZER_MODE를 CHOOSE 로 할경우
SELECT IDNO FROM F1111_K WHERE IDNO = 146357 ;
=> FULL SCAN
OPTIMIZER_MODE를 RULE 로 할경우
=> IDNO 가 INDEX를 수행을 하는데
RULE MODE 에서
SELECT IDNO, S_K1 FROM F1111_K WHERE IDNO = 146357 ;
=> 이렇게 하면 INDEX를 수행하지 못합니다.
물론 사례2는 어떻게 하든 INDEX를 수행합니다.
이sql뿐 아니라 사례1부분이 전체적으로 속도가 느리고 index
수행을
제대로 못하고 있습니다. 더 많은 예를 보여드리고 싶으나
파일첨부가 안되니 안타깝네요..
혹시 이런 현상을 경험하셨거나 해결책을 아시는분께
답변부탁드립니다.
사례1 )
============================================================================
SELECT "F1111_K"."IDNO" , "F1111_K"."S_K1" ,
"F1111_K"."S_K2" , "F1111_K"."S_K3" , "F1111_K"."S_K4" ,
"F1111_K"."S_K5" , "F1111_K"."S_K6" ,
"F1111_K"."S_K7" , "F1111_K"."S_K8" , "F1111_K"."S_K9" ,
"F1111_K"."S_K10" , "F1111_K"."S_K11" ,
"F1111_K"."S_K12" , "F1111_K"."S_K13" ,
"F1111_K"."S_K14" , "F1111_K"."S_K15" ,
"F1111_K"."S_K16" , "F1111_K"."S_K17" ,
"F1111_K"."S_K18" , "F1111_K"."S_K19" ,
"F1111_K"."S_K20" , "F1111_K"."S_K21" ,
"F1111_K"."S_K22" , "F1111_K"."S_K23" ,
"F1111_K"."S_K24" , "F1111_K"."S_K25" ,
"F1111_K"."S_K26" , "F1111_K"."S_K27" ,
"F1111_K"."S_K28" , "F1111_K"."S_K29" ,
"F1111_K"."S_K30" , "F1111_K"."S_K31" ,
"F1111_K"."S_K32" , "F1111_K"."S_K33" ,
"F1111_K"."S_K34" , "F1111_K"."S_K35" ,
"F1111_K"."S_K36" , "F1111_K"."S_K37" ,
"F1111_K"."S_K38" , "F1111_K"."S_K39" ,
"F1111_K"."S_K40" , "F1111_K"."N_K1" , "F1111_K"."N_K2" ,
"F1111_K"."N_K3" , "F1111_K"."N_K4" ,
"F1111_K"."N_K5" , "F1111_K"."N_K6" , "F1111_K"."N_K7" ,
"F1111_K"."N_K8" , "F1111_K"."N_K9" ,
"F1111_K"."N_K10" , "F1111_K"."N_K11" ,
"F1111_K"."N_K12" , "F1111_K"."N_K13" ,
"F1111_K"."N_K14" , "F1111_K"."N_K15" ,
"F1111_K"."N_K16" , "F1111_K"."N_K17" ,
"F1111_K"."N_K18" , "F1111_K"."N_K19" ,
"F1111_K"."N_K20" , "F1111_K"."D_K1" , "F1111_K"."D_K2" ,
"F1111_K"."D_K3" , "F1111_K"."D_K4" ,
"F1111_K"."D_K5" , "F1111_K"."D_K6" , "F1111_K"."D_K7" ,
"F1111_K"."D_K8" , "F1111_K"."D_K9" ,
"F1111_K"."D_K10"
FROM "F1111_K"
WHERE ( "F1111_K"."IDNO" = 146357 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.03 0.02 1 1 0 0
Execute 1 0.00 0.00 0 3 0 0
Fetch 1 0.00 0.01 0 0 0 1
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 3 0.03 0.04 1 4 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (MODELF)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL F1111_K
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF ''F1111_K'' [:Q1470000]
SELECT /*+ NO_EXPAND ROWID(A1) */
A1."IDNO",A1."S_K1",A1."S_K2",
A1."S_K3",A1."S_K4",A1."S_K5",A1."S_K6",A1."S_K7",A1."S_K8",
A1."S_K9",A1."S_K10",A1."S_K11",A1."S_K12",A1."S_K13",
A1."S_K14",A1."S_K15",A1."S_K16",A1."S_K17",A1."S_K18",
A1."S_K19",A1."S_K20",A1."S_K21",A1."S_K22",A1."S_K23",
A1."S_K24",A1."S_K25",A1."S_K26",A1."S_K27",A1."S_K28",
A1."S_K29",A1."S_K30",A1."S_K31",A1."S_K32",A1."S_K33",
A1."S_K34",A1."S_K35",A1."S_K36",A1."S_K37",A1."S_K38",
A1."S_K39",A1."S_K40",A1."N_K1",A1."N_K2",A1."N_K3",A1."N_K4",
A1."N_K5",A1."N_K6",A1."N_K7",A1."N_K8",A1."N_K9",A1."N_K10",
A1."N_K11",A1."N_K12",A1."N_K13",A1."N_K14",A1."N_K15",
A1."N_K16",A1."N_K17",A1."N_K18",A1."N_K19",A1."N_K20",
A1."D_K1",A1."D_K2",A1."D_K3",A1."D_K4",A1."D_K5",A1."D_K6",
A1."D_K7",A1."D_K8",A1."D_K9",A1."D_K10" FROM "F1111_K"
PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1 WHERE A1."IDNO"=
146357
사례2 )
============================================================================
SELECT "F1111_K"."IDNO" , "F1111_K"."S_K1" ,
"F1111_K"."S_K2" , "F1111_K"."S_K3" , "F1111_K"."S_K4" ,
"F1111_K"."S_K5" , "F1111_K"."S_K6" ,
"F1111_K"."S_K7" , "F1111_K"."S_K8" , "F1111_K"."S_K9" ,
"F1111_K"."S_K10" , "F1111_K"."S_K11" ,
"F1111_K"."S_K12" , "F1111_K"."S_K13" ,
"F1111_K"."S_K14" , "F1111_K"."S_K15" ,
"F1111_K"."S_K16" , "F1111_K"."S_K17" ,
"F1111_K"."S_K18" , "F1111_K"."S_K19" ,
"F1111_K"."S_K20" , "F1111_K"."S_K21" ,
"F1111_K"."S_K22" , "F1111_K"."S_K23" ,
"F1111_K"."S_K24" , "F1111_K"."S_K25" ,
"F1111_K"."S_K26" , "F1111_K"."S_K27" ,
"F1111_K"."S_K28" , "F1111_K"."S_K29" ,
"F1111_K"."S_K30" , "F1111_K"."S_K31" ,
"F1111_K"."S_K32" , "F1111_K"."S_K33" ,
"F1111_K"."S_K34" , "F1111_K"."S_K35" ,
"F1111_K"."S_K36" , "F1111_K"."S_K37" ,
"F1111_K"."S_K38" , "F1111_K"."S_K39" ,
"F1111_K"."S_K40" , "F1111_K"."N_K1" , "F1111_K"."N_K2" ,
"F1111_K"."N_K3" , "F1111_K"."N_K4" ,
"F1111_K"."N_K5" , "F1111_K"."N_K6" , "F1111_K"."N_K7" ,
"F1111_K"."N_K8" , "F1111_K"."N_K9" ,
"F1111_K"."N_K10" , "F1111_K"."N_K11" ,
"F1111_K"."N_K12" , "F1111_K"."N_K13" ,
"F1111_K"."N_K14" , "F1111_K"."N_K15" ,
"F1111_K"."N_K16" , "F1111_K"."N_K17" ,
"F1111_K"."N_K18" , "F1111_K"."N_K19" ,
"F1111_K"."N_K20" , "F1111_K"."D_K1" , "F1111_K"."D_K2" ,
"F1111_K"."D_K3" , "F1111_K"."D_K4" ,
"F1111_K"."D_K5" , "F1111_K"."D_K6" , "F1111_K"."D_K7" ,
"F1111_K"."D_K8" , "F1111_K"."D_K9" ,
"F1111_K"."D_K10"
FROM "F1111_K"
WHERE ( "F1111_K"."IDNO" = 146357 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 100 (ASIA)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID F1111_K
1 INDEX UNIQUE SCAN F1111_K_PK (object id 59481)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS (BY INDEX ROWID) OF ''F1111_K''
1 INDEX (UNIQUE SCAN) OF ''F1111_K_PK'' (UNIQUE)
********************************************************************************
