RE: Big Difference in Select and Create Table As Select Traces
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Jan 2007 10:07:17 -0800
No, neither of those things worked. Things are getting curiouser and
curiouser: I tried using create materialized view instead of create table,
and I accidentally did so as sys. The materialized view was created in a few
seconds. However if I as sys, tried to do a CTAS instead of create
materialized view, the statement hangs. So I thought I'd try create
materialized view as the correct user, i.e., not as sys. The create
materialized view for the non-sys user also hung.
From the sys trace file in which the materialized view was created, here is the
statement actually creating and populating the underlying table
CREATE TABLE "SYS"."PS_COMPENSATION2" 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.04 0.06 0 0 0 0
Execute 1 1.69 2.19 1262 9266 18 923
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.73 2.25 1262 9266 18 923
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
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 84 0.10 0.57
direct path read temp 84 0.00 0.00
db file sequential read 2 0.00 0.00
direct path write 3 0.00 0.00
********************************************************************************
Here is what happens if I try the create materialized view as user Oracle.
Again this is the attemptto create the underlying table and populate it. The
statement hangs
CREATE TABLE "ORACLE"."PS_COMPENSATION2" 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.07 0.07 0 0 0 0
Execute 1 211.22 316.44 2464 143 16 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 211.29 316.52 2464 143 16 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48 (ORACLE) (recursive depth: 1)
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 NESTED LOOPS
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 (BY USER ROWID) 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 47063 0.17 131.03
direct path read temp 353 0.12 8.34
********************************************************************************
I thought, perhaps it has something to do with tablespaces, but If I login as
sys and tell it to create the
Materialized view in another tablespace ...
create materialized view ps_compensation2 tablespace dba_admin_data 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
The materialized view is created. However, if I as sys run
create materialized view oracle.ps_compensation2 tablespace dba_admin_data 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
The statement never completes. Lots of time spent waiting on direct path write
temp.
I hope this is clear.
Ian
-----Original Message-----
From: Shivaswamy Raghunath [mailto:shivaswamykr@xxxxxxxxx]
Sent: Thursday, January 04, 2007 6:24 PM
To: MacGregor, Ian A.
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Big Difference in Select and Create Table As Select Traces
Recently I had more or less similar situation. Select will complete in
seconds.. CTAS with the same Slecet will run for hours.
I had a TAR and was successfully resolved.
I think, this could be a case of issues of CBO with View merging and UNNEST.
Essentially, CBO does not force the code to perform cost-based transaction
transformations for CREATE statements.
You may try two options:
1. Use UNNEST hint in the subquery. [SELECT /*+ UNNEST */ MAX(S2.EFFDT)]
2. OR At session level set "_optimizer_cost_based_transformation" to off and
try the same query, without hint.
We have a document now based on my TAR : Note:399077.1. You may want to refer
to it.
I am curios to know if either of them helps you.
Shiva
O
--
//www.freelists.org/webpage/oracle-l
Other related posts:
