RE: Create table performance in 11gR2
- From: Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>
- To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- Date: Thu, 3 Apr 2014 21:27:01 +0000
An here is the explain plan for CTAS.
14:24:09 TPCOM@ITNQA> SELECT *
14:26:07 2 FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1836469873
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | |
16M| 1971M| 1129K (1)| 03:45:51 | | | | |
|
| 1 | PX COORDINATOR | |
| | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 |
16M| 1971M| 1064K (1)| 03:32:52 | | | Q1,01 | P->S | QC (RAND)
|
| 3 | LOAD AS SELECT | POS_REPORT_DETAIL_TT |
| | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | |
16M| 1971M| 1064K (1)| 03:32:52 | | | Q1,01 | PCWP |
|
| 5 | PX SEND ROUND-ROBIN | :TQ10000 |
16M| 1971M| 1064K (1)| 03:32:52 | | | | S->P | RND-ROBIN
|
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| POS_REPORT_DETAIL |
16M| 1971M| 1064K (1)| 03:32:52 | ROWID | ROWID | | |
|
|* 7 | INDEX RANGE SCAN | POS_REPORT_DETAIL_IX4 |
16M| | 96080 (1)| 00:19:13 | | | | |
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("OPMEMBERCOMPANYID"=21193)
19 rows selected.
14:26:08 TPCOM@ITNQA>
Thanks
Bheem Aitha
Sr. Oracle, MySQL and Teradata DBA
iTradenetwork
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Bheemsen Aitha
Sent: Thursday, April 03, 2014 2:25 PM
To: Dominic Brooks
Cc: ORACLE-L
Subject: RE: Create table performance in 11gR2
Here is the DBMS_XPLAN for the underlying select statement.
SQL> select * from table ( dbms_xplan.display_cursor ('090nz57xsk6zt',0,
'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 090nz57xsk6zt, child number 0
-------------------------------------
select DETAILID , OPMEMBERCOMPANYCATALOGID ,
BRANCHID , DISTMEMBERCOMPANYID ,
POS_UNIT_ID , VEN_POS_MV_ID ,
CUSTINVOICEDATE , CUSTINVOICENUMBER ,
POS_PRODUCT_ID , RBT_PROCESSED_DATE ,
MFG_POS_MV_ID , OPMEMBERCOMPANYID ,
DISTMEMBERCOMPANYCATALOGID , STATUSNAME ,
DISTCATCHWEIGHTITEMFLAG , SOLDCASES ,
SOLDWEIGHT , SOLDDOLLARS ,
LAST_UPDATED_DATE , LASTPROCESSEDDATE , CLOSED
, CUSTOMERID , VDA_CLOSED from
pos_report_detail where opmembercompanyid = :"SYS_B_0"
Plan hash value: 638285578
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1064K(100)| | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| POS_REPORT_DETAIL | 16M|
1971M| 1064K (1)| 03:32:52 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | POS_REPORT_DETAIL_IX4 | 16M|
| 96080 (1)| 00:19:13 | | |
----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / POS_REPORT_DETAIL@SEL$1
2 - SEL$1 / POS_REPORT_DETAIL@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('star_transformation_enabled' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "POS_REPORT_DETAIL"@"SEL$1"
("POS_REPORT_DETAIL"."OPMEMBERCOMPANYID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OPMEMBERCOMPANYID"=:SYS_B_0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DETAILID"[NUMBER,22], "DISTMEMBERCOMPANYID"[NUMBER,22],
"OPMEMBERCOMPANYID"[NUMBER,22],
"BRANCHID"[NUMBER,22], "DISTMEMBERCOMPANYCATALOGID"[NUMBER,22],
"CUSTOMERID"[VARCHAR2,20],
"CUSTINVOICENUMBER"[VARCHAR2,20], "CUSTINVOICEDATE"[DATE,7],
"DISTCATCHWEIGHTITEMFLAG"[NUMBER,22],
"SOLDCASES"[NUMBER,22], "SOLDWEIGHT"[NUMBER,22],
"SOLDDOLLARS"[NUMBER,22], "POS_UNIT_ID"[NUMBER,22],
"POS_PRODUCT_ID"[NUMBER,22], "VEN_POS_MV_ID"[NUMBER,22],
"MFG_POS_MV_ID"[NUMBER,22], "STATUSNAME"[VARCHAR2,15],
"OPMEMBERCOMPANYCATALOGID"[NUMBER,22], "CLOSED"[NUMBER,22],
"LASTPROCESSEDDATE"[DATE,7],
"LAST_UPDATED_DATE"[DATE,7], "RBT_PROCESSED_DATE"[DATE,7],
"VDA_CLOSED"[NUMBER,22]
2 - "POS_REPORT_DETAIL".ROWID[ROWID,10], "OPMEMBERCOMPANYID"[NUMBER,22]
65 rows selected.
SQL>
Thanks
Bheem Aitha
Sr. Oracle, MySQL and Teradata DBA
iTradenetwork
From: Dominic Brooks [mailto:dombrooks@xxxxxxxxxxx]
Sent: Thursday, April 03, 2014 1:57 PM
To: Bheemsen Aitha
Cc: ORACLE-L
Subject: Re: Create table performance in 11gR2
You've given an explain statement of a select not of the CTAS.
They may or may not be the same - quite possibly not given your CTAS is asking
for parallel 4.
You really need to confirm the actual execution plan in your 11.2.0.4 via
DBMS_XPLAN.DISPLAY_CURSOR and ideally supplement this with information from
real-time SQL monitoring via SELECT DBMS_SQLTUNE.REPORT_MONITOR(sql_id) FROM
DUAL or from sample waits from ASH (both of which have extra licensing
requirements).
Or even better do an extended SQL trace.
Hope this helps
Dominic
Other related posts:
