RE: Create table performance in 11gR2
Date: Thu, 3 Apr 2014 23:05:41 +0000
Message-ID: <3E9FC3C66B6DD445A50671ECBA1F423EACA78A4E_at_plt-exch-01.Itradenetwork.com>
David,
Here is the explain in 9i. The 9i version is 9.2.0.8 and stats are current in both 9.2.0.8 and 11.2.0.4.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1006606 Card=15855471 Bytes=2013644817) 1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'POS_REPORT_DETAIL' (Cost=1006606 Card
=15855471 Bytes=2013644817)
2 1 INDEX (RANGE SCAN) OF 'POS_REPORT_DETAIL_IX4' (NON-UNIQUE) (Cost=89043 Card=
15855471)
Thanks
Bheem Aitha
Sr. Oracle, MySQL and Teradata DBA
iTradenetwork
From: David Fitzjarrell [mailto:oratune_at_yahoo.com]
Sent: Thursday, April 03, 2014 3:16 PM
To: Bheemsen Aitha; Dominic Brooks
Cc: ORACLE-L
Subject: Re: Create table performance in 11gR2
Do you have the plans from the 9i database? Which release of 9i is it? Are the statistics current in the 11.2.0.4 database?
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Thursday, April 3, 2014 4:13 PM, Bheemsen Aitha <baitha_at_itradenetwork.com<mailto:baitha_at_itradenetwork.com>> wrote:
An here is the explain plan for CTAS.
14:24:09 TPCOM_at_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_at_ITNQA>
Thanks
Bheem Aitha
Sr. Oracle, MySQL and Teradata DBA
iTradenetwork
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] 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_at_SEL$1 2 - SEL$1 / POS_REPORT_DETAIL_at_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(_at_"SEL$1")
INDEX_RS_ASC(_at_"SEL$1" "POS_REPORT_DETAIL"_at_"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_at_hotmail.com]
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 04 2014 - 01:05:41 CEST