Comparing the same SELECT in CTAS, INSERT and SELECT
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 28 Oct 2006 00:12:03 +0800
Oracle 9.2.0.5 RAC on 32-bit Linux
Comparing the same SELECT used in a CTAS, an INSERT and a SELECT-only
operation,
I find that when it is used in the INSERT, Row Source Operations are very high
and there seems to a Full Table scan which is not evident in the Explain Plan.
(note that I "CTRL-C"d the INSERT statement after "waiting" for 45minutes)
The target table TEST_SL has no indexes.
The source table does have indexes, seperately on CLAIM_TIME and LOT_ID.
I'm not yet comfortable with the Inline View and the GROUP BY on CLAIM_TIME.
For the CTAS :
tkprof of the 10046 Trace
CREATE /*+ CTAS */ TABLE hemant.TEST_SL AS
SELECT SOURCE_TABLE.LOT_ID,
SOURCE_TABLE.STORE_TIME,
SOURCE_TABLE.CLAIM_TIME,
SOURCE_TABLE.MOVE_TYPE,
SOURCE_TABLE.OPE_CATEGORY,
SOURCE_TABLE.OPE_NO,
SOURCE_TABLE.PREV_OPE_NO,
SOURCE_TABLE.MAINPD_ID,
SOURCE_TABLE.PREV_MAINPD_ID,
SOURCE_TABLE_SUMMARY.EVENT_COUNT,
0 EVENT_SEQ,
SOURCE_TABLE.OPE_CATEGORY TERM_OPE_CATEGORY
FROM
SOURCE_SCHEMA.SOURCE_TABLE,
(SELECT LOT_ID, CLAIM_TIME, COUNT (*) EVENT_COUNT
FROM SOURCE_SCHEMA.SOURCE_TABLE
GROUP BY LOT_ID, CLAIM_TIME) SOURCE_TABLE_SUMMARY
WHERE
(SOURCE_TABLE.LOT_ID = SOURCE_TABLE_SUMMARY.LOT_ID)
AND (SOURCE_TABLE.CLAIM_TIME = SOURCE_TABLE_SUMMARY.CLAIM_TIME)
AND (SOURCE_TABLE.CLAIM_TIME >= TO_DATE ('05-OCT-2006 07',
'DD-MON-YYYY HH24'))
AND (SOURCE_TABLE.CLAIM_TIME < TO_DATE ('05-OCT-2006 08',
'DD-MON-YYYY HH24'))
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.46 12.90 1301 28730 161
2374
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 2 0.46 12.90 1301 28730 161
2374
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (HEMANT)
Rows Execution Plan
------- ---------------------------------------------------
0 CREATE TABLE STATEMENT GOAL: CHOOSE
0 LOAD AS SELECT
0 SORT (GROUP BY)
0 NESTED LOOPS
0 TABLE ACCESS (BY INDEX ROWID) OF 'SOURCE_TABLE'
0 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
0 AND-EQUAL
0 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX02' (NON-UNIQUE)
0 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache pin 1 0.00 0.00
row cache lock 18 0.00 0.00
control file sequential read 4 0.00 0.00
db file sequential read 1301 0.03 12.26
global cache cr request 726 0.04 0.18
global cache s to x 1 0.00 0.00
global cache open x 5 0.00 0.00
direct path write 2 0.00 0.00
rdbms ipc reply 1 0.00 0.00
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 4.66 4.66
********************************************************************************
10053 Trace
QUERY
explain plan for
CREATE /*+ CTAS */ TABLE hemant.TEST_SL AS
SELECT SOURCE_TABLE.LOT_ID,
SOURCE_TABLE.STORE_TIME,
SOURCE_TABLE.CLAIM_TIME,
SOURCE_TABLE.MOVE_TYPE,
SOURCE_TABLE.OPE_CATEGORY,
SOURCE_TABLE.OPE_NO,
SOURCE_TABLE.PREV_OPE_NO,
SOURCE_TABLE.MAINPD_ID,
SOURCE_TABLE.PREV_MAINPD_ID,
SOURCE_TABLE_SUMMARY.EVENT_COUNT,
0 EVENT_SEQ,
SOURCE_TABLE.OPE_CATEGORY TERM_OPE_CATEGORY
FROM
SOURCE_SCHEMA.SOURCE_TABLE,
(SELECT LOT_ID, CLAIM_TIME, COUNT (*) EVENT_COUNT
FROM SOURCE_SCHEMA.SOURCE_TABLE
GROUP BY LOT_ID, CLAIM_TIME) SOURCE_TABLE_SUMMARY
WHERE
(SOURCE_TABLE.LOT_ID = SOURCE_TABLE_SUMMARY.LOT_ID)
AND (SOURCE_TABLE.CLAIM_TIME = SOURCE_TABLE_SUMMARY.CLAIM_TIME)
AND (SOURCE_TABLE.CLAIM_TIME >= TO_DATE ('05-OCT-2006 07',
'DD-MON-YYYY HH24'))
AND (SOURCE_TABLE.CLAIM_TIME < TO_DATE ('05-OCT-2006 08',
'DD-MON-YYYY HH24'))
CREATE TABLE STATEME 0
LOAD AS SELECT 1
SORT GROUP BY 2 1
NESTED LOOPS 3 2
TABLE ACCESS SOURCE_TABLE BY INDEX ROWID 4 3
INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 5 4
AND-EQUAL 6 3
INDEX SOURCE_TABLE_INDEX02 RANGE SCAN 7 6
INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 8 6
=========================================================================
For the INSERT :
tkprof of the 10046 Trace :
INSERT /*+ INSERT */ INTO TEST_SL
SELECT SOURCE_TABLE.LOT_ID,
SOURCE_TABLE.STORE_TIME,
SOURCE_TABLE.CLAIM_TIME,
SOURCE_TABLE.MOVE_TYPE,
SOURCE_TABLE.OPE_CATEGORY,
SOURCE_TABLE.OPE_NO,
SOURCE_TABLE.PREV_OPE_NO,
SOURCE_TABLE.MAINPD_ID,
SOURCE_TABLE.PREV_MAINPD_ID,
SOURCE_TABLE_SUMMARY.EVENT_COUNT,
0 EVENT_SEQ,
SOURCE_TABLE.OPE_CATEGORY TERM_OPE_CATEGORY
FROM
SOURCE_SCHEMA.SOURCE_TABLE,
(SELECT LOT_ID, CLAIM_TIME, COUNT (*) EVENT_COUNT
FROM SOURCE_SCHEMA.SOURCE_TABLE
GROUP BY LOT_ID, CLAIM_TIME) SOURCE_TABLE_SUMMARY
WHERE
(SOURCE_TABLE.LOT_ID = SOURCE_TABLE_SUMMARY.LOT_ID)
AND (SOURCE_TABLE.CLAIM_TIME = SOURCE_TABLE_SUMMARY.CLAIM_TIME)
AND (SOURCE_TABLE.CLAIM_TIME >= TO_DATE ('05-OCT-2006 07',
'DD-MON-YYYY HH24'))
AND (SOURCE_TABLE.CLAIM_TIME < TO_DATE ('05-OCT-2006 08',
'DD-MON-YYYY HH24'))
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 507.13 2722.39 2538409 4009254 77
0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 2 507.13 2722.39 2538409 4009254 77
0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (HEMANT)
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS
118481 VIEW
118481 SORT GROUP BY
29968053 TABLE ACCESS FULL OBJ#(269240)
0 TABLE ACCESS BY INDEX ROWID OBJ#(269240)
134801 AND-EQUAL
401316 INDEX RANGE SCAN OBJ#(300586) (object id 300586)
271535 INDEX RANGE SCAN OBJ#(269815) (object id 269815)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
0 SORT (GROUP BY)
118481 NESTED LOOPS
118481 TABLE ACCESS (BY INDEX ROWID) OF 'SOURCE_TABLE'
29968053 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
0 AND-EQUAL
134801 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX02' (NON-UNIQUE)
401316 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
global cache cr request 1198153 0.09 40.66
db file sequential read 161657 0.22 1177.80
db file scattered read 140046 0.33 973.73
latch free 83 0.02 0.23
db file parallel read 2 0.29 0.46
direct path write 4 0.00 0.00
direct path read 4464 0.00 0.02
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 1.69 1.69
********************************************************************************
10053 Trace :
QUERY
explain plan for
INSERT /*+ INSERT */ INTO TEST_SL
SELECT SOURCE_TABLE.LOT_ID,
SOURCE_TABLE.STORE_TIME,
SOURCE_TABLE.CLAIM_TIME,
SOURCE_TABLE.MOVE_TYPE,
SOURCE_TABLE.OPE_CATEGORY,
SOURCE_TABLE.OPE_NO,
SOURCE_TABLE.PREV_OPE_NO,
SOURCE_TABLE.MAINPD_ID,
SOURCE_TABLE.PREV_MAINPD_ID,
SOURCE_TABLE_SUMMARY.EVENT_COUNT,
0 EVENT_SEQ,
SOURCE_TABLE.OPE_CATEGORY TERM_OPE_CATEGORY
FROM
SOURCE_SCHEMA.SOURCE_TABLE,
(SELECT LOT_ID, CLAIM_TIME, COUNT (*) EVENT_COUNT
FROM SOURCE_SCHEMA.SOURCE_TABLE
GROUP BY LOT_ID, CLAIM_TIME) SOURCE_TABLE_SUMMARY
WHERE
(SOURCE_TABLE.LOT_ID = SOURCE_TABLE_SUMMARY.LOT_ID)
AND (SOURCE_TABLE.CLAIM_TIME = SOURCE_TABLE_SUMMARY.CLAIM_TIME)
AND (SOURCE_TABLE.CLAIM_TIME >= TO_DATE ('05-OCT-2006 07',
'DD-MON-YYYY HH24'))
AND (SOURCE_TABLE.CLAIM_TIME < TO_DATE ('05-OCT-2006 08',
'DD-MON-YYYY HH24'))
INSERT STATEMENT 0
SORT GROUP BY 1
NESTED LOOPS 2 1
TABLE ACCESS SOURCE_TABLE BY INDEX ROWID 3 2
INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 4 3
AND-EQUAL 5 2
INDEX SOURCE_TABLE_INDEX02 RANGE SCAN 6 5
INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 7 5
=========================================================================
For the SELECT only :
10046 Trace :
SELECT /*+ Pure SELECT */ SOURCE_TABLE.LOT_ID,
SOURCE_TABLE.STORE_TIME,
SOURCE_TABLE.CLAIM_TIME,
SOURCE_TABLE.MOVE_TYPE,
SOURCE_TABLE.OPE_CATEGORY,
SOURCE_TABLE.OPE_NO,
SOURCE_TABLE.PREV_OPE_NO,
SOURCE_TABLE.MAINPD_ID,
SOURCE_TABLE.PREV_MAINPD_ID,
SOURCE_TABLE_SUMMARY.EVENT_COUNT,
0 EVENT_SEQ,
SOURCE_TABLE.OPE_CATEGORY TERM_OPE_CATEGORY
FROM
SOURCE_SCHEMA.SOURCE_TABLE,
(SELECT LOT_ID, CLAIM_TIME, COUNT (*) EVENT_COUNT
FROM SOURCE_SCHEMA.SOURCE_TABLE
GROUP BY LOT_ID, CLAIM_TIME) SOURCE_TABLE_SUMMARY
WHERE
(SOURCE_TABLE.LOT_ID = SOURCE_TABLE_SUMMARY.LOT_ID)
AND (SOURCE_TABLE.CLAIM_TIME = SOURCE_TABLE_SUMMARY.CLAIM_TIME)
AND (SOURCE_TABLE.CLAIM_TIME >= TO_DATE ('05-OCT-2006 07',
'DD-MON-YYYY HH24'))
AND (SOURCE_TABLE.CLAIM_TIME < TO_DATE ('05-OCT-2006 08',
'DD-MON-YYYY HH24'))
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 160 0.33 0.32 0 28603 0
2374
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 162 0.33 0.32 0 28603 0
2374
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (HEMANT)
Rows Row Source Operation
------- ---------------------------------------------------
2374 SORT GROUP BY
3494 NESTED LOOPS
2374 TABLE ACCESS BY INDEX ROWID OBJ#(269240)
2374 INDEX RANGE SCAN OBJ#(269815) (object id 269815)
3494 AND-EQUAL
7467 INDEX RANGE SCAN OBJ#(300586) (object id 300586)
7153 INDEX RANGE SCAN OBJ#(269815) (object id 269815)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
2374 SORT (GROUP BY)
3494 NESTED LOOPS
2374 TABLE ACCESS (BY INDEX ROWID) OF 'SOURCE_TABLE'
2374 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
3494 AND-EQUAL
7467 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX02' (NON-UNIQUE)
7153 INDEX (RANGE SCAN) OF 'SOURCE_TABLE_INDEX01' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 160 0.00 0.00
SQL*Net message from client 160 3.57 18.64
********************************************************************************
10053 Trace :
QUERY
explain plan for
SELECT /*+ Pure SELECT */ SOURCE_TABLE.LOT_ID,
SOURCE_TABLE.STORE_TIME,
SOURCE_TABLE.CLAIM_TIME,
SOURCE_TABLE.MOVE_TYPE,
SOURCE_TABLE.OPE_CATEGORY,
SOURCE_TABLE.OPE_NO,
SOURCE_TABLE.PREV_OPE_NO,
SOURCE_TABLE.MAINPD_ID,
SOURCE_TABLE.PREV_MAINPD_ID,
SOURCE_TABLE_SUMMARY.EVENT_COUNT,
0 EVENT_SEQ,
SOURCE_TABLE.OPE_CATEGORY TERM_OPE_CATEGORY
FROM
SOURCE_SCHEMA.SOURCE_TABLE,
(SELECT LOT_ID, CLAIM_TIME, COUNT (*) EVENT_COUNT
FROM SOURCE_SCHEMA.SOURCE_TABLE
GROUP BY LOT_ID, CLAIM_TIME) SOURCE_TABLE_SUMMARY
WHERE
(SOURCE_TABLE.LOT_ID = SOURCE_TABLE_SUMMARY.LOT_ID)
AND (SOURCE_TABLE.CLAIM_TIME = SOURCE_TABLE_SUMMARY.CLAIM_TIME)
AND (SOURCE_TABLE.CLAIM_TIME >= TO_DATE ('05-OCT-2006 07',
'DD-MON-YYYY HH24'))
AND (SOURCE_TABLE.CLAIM_TIME < TO_DATE ('05-OCT-2006 08',
'DD-MON-YYYY HH24'))
SELECT STATEMENT 0
SORT GROUP BY 1
NESTED LOOPS 2 1
TABLE ACCESS SOURCE_TABLE BY INDEX ROWID 3 2
INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 4 3
AND-EQUAL 5 2
INDEX SOURCE_TABLE_INDEX02 RANGE SCAN 6 5
INDEX SOURCE_TABLE_INDEX01 RANGE SCAN 7 5
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
--
//www.freelists.org/webpage/oracle-l
Other related posts:
