|
제가 이걸 다른사람이 하는걸 얼추봐서 그런가.;;
분명히 제대로 했다고 하는데, 처음에는 제대로된 소스가 나오다가요..
SELECT CPDM.ITEM_CD
, RIM_1.ITEM_NM ITEM_NM
, CPDM.MODEL_CD
, CPDM.PROC_ITEM_CD
, RIM_2.ITEM_NM PROC_ITEM_NM
, CPS.PROD_QTY
, CPDM.PROC_CD
, CPS.CC_CD CC_CD
, CPDM.MAT_ITEM_CD
, RIM_3.ITEM_NM MAT_ITEM_NM
, (CASE WHEN NVL(SUM_MAT.SUM_MAT_AMT, 0) = 0 THEN 0
ELSE CPDM.MAT_AMT / NVL(SUM_MAT.SUM_MAT_AMT, 0) END) MAT_RATE
, CPS.MAT_CONV_RATE MAT_CONV_RATE
FROM CCM_PROD_DTL_MAT CPDM
, RTI_ITEM_MASTER RIM_1
, RTI_ITEM_MASTER RIM_2
, RTI_ITEM_MASTER RIM_3
, CCM_PROD_SUM CPS
, (
SELECT CPDM.MAT_ITEM_CD
, SUM(CPDM.MAT_AMT) SUM_MAT_AMT
FROM CCM_PROD_DTL_MAT CPDM
WHERE CPDM.ENTP_CD = '11'
AND CPDM.COSTING_YM = '200905'
AND CPDM.ITEM_DIV IN ('30','40')
GROUP BY CPDM.MAT_ITEM_CD ) SUM_MAT
WHERE CPDM.ITEM_CD = RIM_1.ITEM_CD
AND CPDM.PROC_ITEM_CD = RIM_2.ITEM_CD
AND CPDM.MAT_ITEM_CD = RIM_3.ITEM_CD
AND CPDM.MAT_ITEM_CD = SUM_MAT.MAT_ITEM_CD (+)
AND CPDM.ENTP_CD = CPS.ENTP_CD (+)
AND CPDM.COSTING_YM = CPS.COSTING_YM (+)
AND CPDM.ITEM_CD = CPS.ITEM_CD (+)
AND CPDM.MODEL_CD = CPS.MODEL_CD (+)
AND CPDM.PROC_ITEM_CD = CPS.PROC_ITEM_CD (+)
AND CPDM.PROC_CD = CPS.PROC_CD (+)
AND CPDM.ENTP_CD = '11'
AND CPDM.COSTING_YM = '200905'
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 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (FAMILY)
Rows Row Source Operation
------- ---------------------------------------------------
0 MERGE JOIN OUTER
1 SORT JOIN
0 NESTED LOOPS OUTER
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID CCM_PROD_DTL_MAT
1 INDEX RANGE SCAN (object id 34208)
0 TABLE ACCESS BY INDEX ROWID RTI_ITEM_MASTER
0 INDEX UNIQUE SCAN (object id 35509)
0 TABLE ACCESS BY INDEX ROWID RTI_ITEM_MASTER
0 INDEX UNIQUE SCAN (object id 35509)
0 TABLE ACCESS BY INDEX ROWID RTI_ITEM_MASTER
0 INDEX UNIQUE SCAN (object id 35509)
0 TABLE ACCESS BY INDEX ROWID CCM_PROD_SUM
0 INDEX RANGE SCAN (object id 34210)
0 SORT JOIN
0 VIEW
0 SORT GROUP BY
0 TABLE ACCESS BY INDEX ROWID CCM_PROD_DTL_MAT
0 INDEX RANGE SCAN (object id 34208)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 MERGE JOIN (OUTER)
1 SORT (JOIN)
0 NESTED LOOPS (OUTER)
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS (BY INDEX ROWID) OF 'CCM_PROD_DTL_MAT'
1 INDEX (RANGE SCAN) OF 'PK_CCM_PROD_DTL_MAT'
(UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'RTI_ITEM_MASTER'
0 INDEX (UNIQUE SCAN) OF 'PK_RTI_ITEM_MASTER'
(UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'RTI_ITEM_MASTER'
0 INDEX (UNIQUE SCAN) OF 'PK_RTI_ITEM_MASTER' (UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'RTI_ITEM_MASTER'
0 INDEX (UNIQUE SCAN) OF 'PK_RTI_ITEM_MASTER' (UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'CCM_PROD_SUM'
0 INDEX (RANGE SCAN) OF 'CCM_PROD_SUM_IDX01' (NON-UNIQUE)
0 SORT (JOIN)
0 VIEW
0 SORT (GROUP BY)
0 TABLE ACCESS (BY INDEX ROWID) OF 'CCM_PROD_DTL_MAT'
0 INDEX (RANGE SCAN) OF 'PK_CCM_PROD_DTL_MAT' (UNIQUE)
********************************************************************************
DELETE FROM PLAN_TABLE
WHERE
STATEMENT_ID=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 2 32 22
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 2 32 22
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (FAMILY)
Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE PLAN_TABLE
1 TABLE ACCESS FULL PLAN_TABLE
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT GOAL: CHOOSE
1 DELETE OF 'PLAN_TABLE'
1 TABLE ACCESS (FULL) OF 'PLAN_TABLE'
********************************************************************************
EXPLAIN PLAN SET STATEMENT_ID='PLUS40077' FOR SELECT CPDM.ITEM_CD
, RIM_1.ITEM_NM ITEM_NM
, CPDM.MODEL_CD
, CPDM.PROC_ITEM_CD
, RIM_2.ITEM_NM PROC_ITEM_NM
, CPS.PROD_QTY
, CPDM.PROC_CD
, CPS.CC_CD CC_CD
, CPDM.MAT_ITEM_CD
, RIM_3.ITEM_NM MAT_ITEM_NM
, (CASE WHEN NVL(SUM_MAT.SUM_MAT_AMT, 0) = 0 THEN 0
ELSE CPDM.MAT_AMT / NVL(SUM_MAT.SUM_MAT_AMT, 0) END) MAT_RATE
, CPS.MAT_CONV_RATE MAT_CONV_RATE
FROM CCM_PROD_DTL_MAT CPDM
, RTI_ITEM_MASTER RIM_1
, RTI_ITEM_MASTER RIM_2
, RTI_ITEM_MASTER RIM_3
, CCM_PROD_SUM CPS
, (
SELECT CPDM.MAT_ITEM_CD
, SUM(CPDM.MAT_AMT) SUM_MAT_AMT
FROM CCM_PROD_DTL_MAT CPDM
WHERE CPDM.ENTP_CD = '11'
AND CPDM.COSTING_YM = '200905'
AND CPDM.ITEM_DIV IN ('30','40')
GROUP BY CPDM.MAT_ITEM_CD ) SUM_MAT
WHERE CPDM.ITEM_CD = RIM_1.ITEM_CD
AND CPDM.PROC_ITEM_CD = RIM_2.ITEM_CD
AND CPDM.MAT_ITEM_CD = RIM_3.ITEM_CD
AND CPDM.MAT_ITEM_CD = SUM_MAT.MAT_ITEM_CD (+)
AND CPDM.ENTP_CD = CPS.ENTP_CD (+)
AND CPDM.COSTING_YM = CPS.COSTING_YM (+)
AND CPDM.ITEM_CD = CPS.ITEM_CD (+)
AND CPDM.MODEL_CD = CPS.MODEL_CD (+)
AND CPDM.PROC_ITEM_CD = CPS.PROC_ITEM_CD (+)
AND CPDM.PROC_CD = CPS.PROC_CD (+)
AND CPDM.ENTP_CD = '11'
AND CPDM.COSTING_YM = '200905'
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (FAMILY)
Rows Row Source Operation
------- ---------------------------------------------------
0 MERGE JOIN OUTER
0 SORT JOIN
0 NESTED LOOPS OUTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID CCM_PROD_DTL_MAT
0 INDEX RANGE SCAN (object id 34208)
0 TABLE ACCESS BY INDEX ROWID RTI_ITEM_MASTER
0 INDEX UNIQUE SCAN (object id 35509)
0 TABLE ACCESS BY INDEX ROWID RTI_ITEM_MASTER
0 INDEX UNIQUE SCAN (object id 35509)
0 TABLE ACCESS BY INDEX ROWID RTI_ITEM_MASTER
0 INDEX UNIQUE SCAN (object id 35509)
0 TABLE ACCESS BY INDEX ROWID CCM_PROD_SUM
0 INDEX RANGE SCAN (object id 34210)
0 SORT JOIN
0 VIEW
0 SORT GROUP BY
0 TABLE ACCESS BY INDEX ROWID CCM_PROD_DTL_MAT
0 INDEX RANGE SCAN (object id 34208)
********************************************************************************
select user#
from
sys.user$ where name = 'OUTLN'
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 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN (object id 41)
********************************************************************************
insert into plan_table (statement_id, timestamp, operation, options,
object_node, object_owner, object_name, object_instance, object_type,
search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
bytes, other_tag, partition_start, partition_stop, partition_id,
distribution )
values
(:1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
:20,:21,:22)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 22 0.00 0.00 0 1 26 22
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 0.00 0.00 0 1 26 22
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (FAMILY) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
********************************************************************************
select o.name, u.name
from
sys.obj$ o, sys.user$ u where obj# = :1 and owner# = user#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 30 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 30 0 6
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
6 NESTED LOOPS
6 TABLE ACCESS BY INDEX ROWID OBJ$
6 INDEX UNIQUE SCAN (object id 33)
6 TABLE ACCESS CLUSTER USER$
6 INDEX UNIQUE SCAN (object id 11)
********************************************************************************
SELECT ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,LPAD(' ',2*(LEVEL-1))
||OPERATION||DECODE(OTHER_TAG,NULL,'','*')||DECODE(OPTIONS,NULL,'','
('||OPTIONS||')')||DECODE(OBJECT_NAME,NULL,'',' OF '''||OBJECT_NAME||'''')
||DECODE(OBJECT_TYPE,NULL,'',' ('||OBJECT_TYPE||')')||DECODE(ID,0,
DECODE(OPTIMIZER,NULL,'',' Optimizer='||OPTIMIZER))||DECODE(COST,NULL,'','
(Cost='||COST||DECODE(CARDINALITY,NULL,'',' Card='||CARDINALITY)
||DECODE(BYTES,NULL,'',' Bytes='||BYTES)||')') PLAN_PLUS_EXP,OBJECT_NODE
OBJECT_NODE_PLUS_EXP
FROM
PLAN_TABLE START WITH ID=0 AND STATEMENT_ID=:1 CONNECT BY PRIOR ID=PARENT_ID
AND STATEMENT_ID=:1 ORDER BY ID,POSITION
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 3 0.00 0.00 0 45 92 22
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 45 92 22
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (FAMILY)
Rows Row Source Operation
------- ---------------------------------------------------
22 SORT ORDER BY
22 CONNECT BY
2 TABLE ACCESS FULL PLAN_TABLE
1 TABLE ACCESS BY USER ROWID PLAN_TABLE
43 TABLE ACCESS FULL PLAN_TABLE
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
22 SORT (ORDER BY)
22 CONNECT BY
2 TABLE ACCESS (FULL) OF 'PLAN_TABLE'
1 TABLE ACCESS (BY USER ROWID) OF 'PLAN_TABLE'
43 TABLE ACCESS (FULL) OF 'PLAN_TABLE'
********************************************************************************
SELECT ID ID_PLUS_EXP,OTHER_TAG OTHER_TAG_PLUS_EXP,OTHER OTHER_PLUS_EXP
FROM
PLAN_TABLE WHERE STATEMENT_ID=:1 AND OTHER_TAG IS NOT NULL ORDER BY ID
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 1 4 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 4 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (FAMILY)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS FULL PLAN_TABLE
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 TABLE ACCESS (FULL) OF 'PLAN_TABLE'
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 2 32 22
Fetch 5 0.00 0.00 0 49 96 22
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 0.00 0.00 0 51 128 44
Misses in library cache during parse: 5
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 29 0.00 0.00 0 1 26 22
Fetch 7 0.00 0.00 0 32 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39 0.00 0.00 0 33 26 29
Misses in library cache during parse: 3
7 user SQL statements in session.
2 internal SQL statements in session.
9 SQL statements in session.
5 statements EXPLAINed in this session.
********************************************************************************
Trace file: D:\oracle\admin\miae\udump\ORA12452.TRC
Trace file compatibility: 8.00.04
Sort options: default
2 sessions in tracefile.
8 user SQL statements in trace file.
2 internal SQL statements in trace file.
9 SQL statements in trace file.
8 unique SQL statements in trace file.
5 SQL statements EXPLAINed using schema:
FAMILY.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
230 lines in trace file.
이런식으로 빨간색 부분처럼요, 처음 쿼리에는 포함도 안된 delete 나 insert가 포함되고,
plan_table이라는거가 들어가있고, 막 usr# 이런식으로 뒤에 샾이 있는 경우도 생기네요 ;;
책을 보면 분명히 풀테이블이다 뭐 이런식으로만 나오던데, 저런건 왜 나오는거고, 어떤 역할을 하는건가요;;?
|
