안녕하세요.

9/9일에 질문올렸는데 아무도 답글을 해주지 않으셔서 다시 질문 드립니다.

질문의 요지는 아래와 같습니다...

select 시에는 5~10초 정도에 결과가 보여지는 query가 있는데,

그 query 문위에 insert문을 넣으면 plan이 변경되면서 수행결과가 15분정도 소요된다는 것입니다.

이현상의 원인과 해결방법을 좀 알고 싶습니다.

(아시는 분들은 답글 좀 해주세요...ㅜ.ㅜ)

아래는 9/9 일날 올린 글을 조금 수정해서 다시 적어봤습니다.

=======================================================

현재 환경은 AIX5.2 , oracle9.2.0.5 이며 주기적으로 통계정보를 생성하고 있습니다.

특정select 을 수행했을 경우 그 결과가 5초안에 보여지지만..

그 특정select 문 위에 insert문을 넣어서 select/insert문장을 만들어 주면 plan이 완전히 달라져 버려서 수행시간이 15분 정도 소요됩니다.

참고로,

1.문제의 select문내의 table은 cascade option을 주고 full로 통계정보를 생성했으며,

2. select 건수는 2000건 전후 이며,

3. insert table에 제약조건이나 index는 없습니다.

제 생각으로는 select 해서 바로 insert해 버리기 때문에 plan에 영향이 없이 수초 않에 끝이나야

정상일것 같은데..

왜 이런현상이 발생하는 것인지요?

그리고 해결책에는 어떤것이 있을까요?

(query문을 올리려고 했으나.. 너무 길어서 오히려 못한결과를 보일것 같아서 생략했습니다.)

답변에 미리 감사드리며 글을 줄입니다.

그럼 수고하세요.

p.s 아시는 분은 답글좀 올려주세요. ㅜ.ㅜ

아래는 각각의 경우를 trace해본 결과 입니다.

********************************************************************************

*** 단순 select 구문

********************************************************************************

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 113 5.99 5.93 19479 96802 0 1671
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 115 6.05 6.01 19479 96802 0 1671

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1671 VIEW
1671 UNION-ALL
1218 HASH JOIN OUTER
1218 VIEW
1218 UNION-ALL
614 HASH JOIN OUTER
605 HASH JOIN
7 TABLE ACCESS BY INDEX ROWID PDW_LOG_WEEK_INFO
7 INDEX RANGE SCAN PDW_LOG_WEEK_INFO_N2 (object id 37694)
22884 TABLE ACCESS FULL PDW_ODS_PO_SUGG
431 VIEW
431 FILTER
480 SORT GROUP BY
578 TABLE ACCESS BY INDEX ROWID PDW_ODS_PO_INFO
1059 NESTED LOOPS
480 TABLE ACCESS BY INDEX ROWID PDW_ODS_PO_INFO
2766 INDEX RANGE SCAN PDW_ODS_PO_INFO_N8 (object id 41333)
578 INDEX RANGE SCAN PDW_ODS_PO_INFO_N3 (object id 38582)
604 HASH JOIN
677 TABLE ACCESS BY INDEX ROWID PDW_ODS_PO_INFO
2766 INDEX RANGE SCAN PDW_ODS_PO_INFO_N8 (object id 41333)
183195 VIEW
183195 SORT GROUP BY
245159 TABLE ACCESS FULL PDW_ODS_PO_INFO
1748 VIEW
1748 SORT GROUP BY
2414 HASH JOIN OUTER
2414 HASH JOIN OUTER
2414 HASH JOIN OUTER
2414 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2448 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
177 VIEW
177 SORT GROUP BY
375 HASH JOIN
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
117484 TABLE ACCESS FULL PDW_ODS_FWD_DETAIL
130 VIEW
130 SORT GROUP BY
260 HASH JOIN
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
113441 TABLE ACCESS FULL PDW_ODS_TXN_SHIPPING
6716 VIEW
6716 SORT GROUP BY
8382 TABLE ACCESS BY INDEX ROWID PDW_LOG_ALLOC_ORDER_RESULTS
8389 NESTED LOOPS
6 VIEW
6 SORT GROUP BY
23838 INDEX FAST FULL SCAN PDW_LOG_ALLOC_ORDER_RESULTS_N5 (object id 40660)
8382 INDEX RANGE SCAN PDW_LOG_ALLOC_ORDER_RESULTS_N1 (object id 37831)
453 SORT GROUP BY
496 VIEW
496 UNION-ALL
245 SORT GROUP BY
304 HASH JOIN OUTER
304 HASH JOIN OUTER
304 HASH JOIN OUTER
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
177 VIEW
177 SORT GROUP BY
375 HASH JOIN
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
117484 TABLE ACCESS FULL PDW_ODS_FWD_DETAIL
130 VIEW
130 SORT GROUP BY
260 HASH JOIN
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
113441 TABLE ACCESS FULL PDW_ODS_TXN_SHIPPING
6716 VIEW
6716 SORT GROUP BY
8382 TABLE ACCESS BY INDEX ROWID PDW_LOG_ALLOC_ORDER_RESULTS
8389 NESTED LOOPS
6 VIEW
6 SORT GROUP BY
23838 INDEX FAST FULL SCAN PDW_LOG_ALLOC_ORDER_RESULTS_N5 (object id 40660)
8382 INDEX RANGE SCAN PDW_LOG_ALLOC_ORDER_RESULTS_N1 (object id 37831)
251 SORT GROUP BY
251 TABLE ACCESS BY INDEX ROWID PDW_LOG_INF_COMM_SEND
1379 INDEX RANGE SCAN PDW_LOG_INF_COMM_SEND_N5 (object id 39762)

********************************************************************************

*** select / insert 구문

********************************************************************************
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.06 0 0 0 0
Execute 1 775.21 758.84 4975683 5405679 259 1671
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 775.27 758.90 4975683 5405679 259 1671

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1671 VIEW
1671 UNION-ALL
1218 HASH JOIN OUTER
1218 VIEW
1218 UNION-ALL
614 NESTED LOOPS OUTER
605 HASH JOIN
7 TABLE ACCESS BY INDEX ROWID PDW_LOG_WEEK_INFO
7 INDEX RANGE SCAN PDW_LOG_WEEK_INFO_N2 (object id 37694)
22884 TABLE ACCESS FULL PDW_ODS_PO_SUGG
422 VIEW PUSHED PREDICATE
422 HASH JOIN
471 TABLE ACCESS BY INDEX ROWID PDW_ODS_PO_INFO
475 INDEX RANGE SCAN PDW_ODS_PO_INFO_N5 (object id 39782)
76732096 VIEW
76732096 SORT GROUP BY
102395916 TABLE ACCESS FULL PDW_ODS_PO_INFO
604 HASH JOIN
677 TABLE ACCESS BY INDEX ROWID PDW_ODS_PO_INFO
2766 INDEX RANGE SCAN PDW_ODS_PO_INFO_N8 (object id 41333)
183195 VIEW
183195 SORT GROUP BY
245159 TABLE ACCESS FULL PDW_ODS_PO_INFO
1748 VIEW
1748 SORT GROUP BY
2414 HASH JOIN OUTER
2414 HASH JOIN OUTER
2414 HASH JOIN OUTER
2414 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2448 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
177 VIEW
177 SORT GROUP BY
375 HASH JOIN
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
117484 TABLE ACCESS FULL PDW_ODS_FWD_DETAIL
130 VIEW
130 SORT GROUP BY
260 HASH JOIN
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
113441 TABLE ACCESS FULL PDW_ODS_TXN_SHIPPING
6716 VIEW
6716 SORT GROUP BY
8382 TABLE ACCESS BY INDEX ROWID PDW_LOG_ALLOC_ORDER_RESULTS
8389 NESTED LOOPS
6 VIEW
6 SORT GROUP BY
23838 INDEX FAST FULL SCAN PDW_LOG_ALLOC_ORDER_RESULTS_N5 (object id 40660)
8382 INDEX RANGE SCAN PDW_LOG_ALLOC_ORDER_RESULTS_N1 (object id 37831)
453 SORT GROUP BY
496 VIEW
496 UNION-ALL
245 SORT GROUP BY
304 HASH JOIN OUTER
304 HASH JOIN OUTER
304 HASH JOIN OUTER
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
177 VIEW
177 SORT GROUP BY
375 HASH JOIN
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
117484 TABLE ACCESS FULL PDW_ODS_FWD_DETAIL
130 VIEW
130 SORT GROUP BY
260 HASH JOIN
304 TABLE ACCESS BY INDEX ROWID PDW_ODS_SALES_ORDER
2352 INDEX RANGE SCAN PDW_ODS_SALES_ORDER_N4 (object id 41327)
113441 TABLE ACCESS FULL PDW_ODS_TXN_SHIPPING
6716 VIEW
6716 SORT GROUP BY
8382 TABLE ACCESS BY INDEX ROWID PDW_LOG_ALLOC_ORDER_RESULTS
8389 NESTED LOOPS
6 VIEW
6 SORT GROUP BY
23838 INDEX FAST FULL SCAN PDW_LOG_ALLOC_ORDER_RESULTS_N5 (object id 40660)
8382 INDEX RANGE SCAN PDW_LOG_ALLOC_ORDER_RESULTS_N1 (object id 37831)
251 SORT GROUP BY
251 TABLE ACCESS BY INDEX ROWID PDW_LOG_INF_COMM_SEND
1379 INDEX RANGE SCAN PDW_LOG_INF_COMM_SEND_N5 (object id 39762)

********************************************************************************