Big Difference in Select and Create Table As Select Traces
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Jan 2007 15:56:18 -0800
Our Peopesoft folks are going through the upgrade process. One of the updates
takes forever, one of the things I tried was to materialize the subquery in the
update. I was not able to do so.
Here is the subquery being run as a query
SELECT DISTINCT J1.EMPLID
FROM PS_JOB J1 ,PS_SAL_RATECD_TBL S1, PS_COMPENSATION
WHERE J1.EMPLID = PS_COMPENSATION.EMPLID AND
J1.EMPL_RCD = PS_COMPENSATION.EMPL_RCD AND
J1.EFFDT = PS_COMPENSATION.EFFDT AND
J1.EFFSEQ = PS_COMPENSATION.EFFSEQ AND
S1.SETID = J1.SETID_SALARY AND
S1.SAL_ADMIN_PLAN = J1.SAL_ADMIN_PLAN
AND S1.GRADE = J1.GRADE
AND S1.STEP = J1.STEP AND
S1.EFFDT = ( SELECT MAX(S2.EFFDT)
FROM PS_SAL_RATECD_TBL S2
WHERE S2.SETID = J1.SETID_SALARY AND
S2.SAL_ADMIN_PLAN = J1.SAL_ADMIN_PLAN AND
S2.GRADE = J1.GRADE AND
S2.STEP = J1.STEP AND
S2.EFFDT <= J1.EFFDT) AND
S1.COMP_RATECD = PS_COMPENSATION.COMP_RATECD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.20 0.18 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 63 7.74 10.17 8932 3189 0 923
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 65 7.94 10.36 8932 3189 0 923
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48 (ORACLE)
Rows Row Source Operation
------- ---------------------------------------------------
923 HASH UNIQUE (cr=3189 pr=8932 pw=8932 time=10166810 us)
7691 FILTER (cr=3189 pr=8932 pw=8932 time=10847080 us)
72798 HASH GROUP BY (cr=3189 pr=8932 pw=8932 time=10085197 us)
415120 HASH JOIN (cr=3189 pr=7882 pw=7882 time=8460323 us)
415825 HASH JOIN (cr=2972 pr=644 pw=644 time=1410302 us)
42651 HASH JOIN (cr=2934 pr=0 pw=0 time=181414 us)
2759 TABLE ACCESS FULL PS_SAL_RATECD_TBL (cr=38 pr=0 pw=0 time=181 us)
31863 TABLE ACCESS FULL PS_JOB (cr=2896 pr=0 pw=0 time=95775 us)
2759 TABLE ACCESS FULL PS_SAL_RATECD_TBL (cr=38 pr=0 pw=0 time=188 us)
31641 INDEX FAST FULL SCAN PS_COMPENSATION (cr=217 pr=0 pw=0 time=187
us)(object id 136687
)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
923 HASH (UNIQUE)
7691 FILTER
72798 HASH (GROUP BY)
415120 HASH JOIN
415825 HASH JOIN
42651 HASH JOIN
2759 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PS_SAL_RATECD_TBL' (TABLE)
31863 TABLE ACCESS MODE: ANALYZED (FULL) OF 'PS_JOB'
(TABLE)
2759 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PS_SAL_RATECD_TBL' (TABLE)
31641 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'PS_COMPENSATION' (INDEX (UNIQUE))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 63 0.00 0.00
direct path write temp 1196 0.02 3.25
direct path read temp 1196 0.01 0.33
SQL*Net message from client 63 0.00 0.04
********************************************************************************
alter session set events '10046 trace name off'
Here's what happens when I try to materialize it and aborted that attempt
create table ps_compensation2 nologging as
SELECT DISTINCT J1.EMPLID
FROM PS_JOB J1 ,PS_SAL_RATECD_TBL S1, PS_COMPENSATION
WHERE J1.EMPLID = PS_COMPENSATION.EMPLID AND
J1.EMPL_RCD = PS_COMPENSATION.EMPL_RCD AND
J1.EFFDT = PS_COMPENSATION.EFFDT AND
J1.EFFSEQ = PS_COMPENSATION.EFFSEQ AND
S1.SETID = J1.SETID_SALARY AND
S1.SAL_ADMIN_PLAN = J1.SAL_ADMIN_PLAN
AND S1.GRADE = J1.GRADE
AND S1.STEP = J1.STEP AND
S1.EFFDT = ( SELECT MAX(S2.EFFDT)
FROM PS_SAL_RATECD_TBL S2
WHERE S2.SETID = J1.SETID_SALARY AND
S2.SAL_ADMIN_PLAN = J1.SAL_ADMIN_PLAN AND
S2.GRADE = J1.GRADE AND
S2.STEP = J1.STEP AND
S2.EFFDT <= J1.EFFDT) AND
S1.COMP_RATECD = PS_COMPENSATION.COMP_RATECD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.10 0 0 0 0
Execute 1 257.30 574.49 97535 238 17 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 257.38 574.60 97535 238 17 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48 (ORACLE)
Rows Execution Plan
------- ---------------------------------------------------
0 CREATE TABLE STATEMENT MODE: ALL_ROWS
0 LOAD AS SELECT OF 'PS_COMPENSATION2'
0 HASH (UNIQUE)
0 NESTED LOOPS
0 NESTED LOOPS
0 HASH JOIN
0 VIEW OF 'VW_SQ_1' (VIEW)
0 HASH (GROUP BY)
0 MERGE JOIN
0 SORT (JOIN)
0 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'PS_SAL_RATECD_TBL' (INDEX (UNIQUE))
0 SORT (JOIN)
0 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'PSDJOB' (INDEX)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'PS_JOB'
(TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PS_SAL_RATECD_TBL' (INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PS_COMPENSATION'
(INDEX (UNIQUE))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 4 0.00 0.00
direct path write temp 55978 0.13 190.68
direct path read temp 13934 0.16 173.02
log file sync 1 0.00 0.00
SQL*Net break/reset to client 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.01 0.01
********************************************************************************
Note the different query path. Also the great rise in direct path * waits.
Has anyone else seen anything like this? Any ideas?
This is 10.2.0.2.0
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
