RE: Create table performance in 11gR2
- From: Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>
- To: David Fitzjarrell <oratune@xxxxxxxxx>, Dominic Brooks <dombrooks@xxxxxxxxxxx>
- Date: Fri, 4 Apr 2014 02:33:02 +0000
Thanks David for looking at this. Actually I have parallel on the CTAS only and
not on the select statement itself. See below.
CREATE TABLE POS_REPORT_DETAIL_TT
TABLESPACE ITNTT2
nologging parallel 4
as
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 = 21193;
Thanks
Bheem Aitha
Sr. Oracle, MySQL and Teradata DBA
iTradenetwork
From: David Fitzjarrell [mailto:oratune@xxxxxxxxx]
Sent: Thursday, April 03, 2014 6:27 PM
To: Bheemsen Aitha; Dominic Brooks
Cc: ORACLE-L
Subject: Re: Create table performance in 11gR2
I have to ask if there's a reason you're running this in parallel, as it seems
to be increasing the cost (time) of the create table statement. The plan from
9.2.0.8 doesn't show any parallel slaves.
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Thursday, April 3, 2014 5:06 PM, Bheemsen Aitha
<baitha@xxxxxxxxxxxxxxxxx<mailto:baitha@xxxxxxxxxxxxxxxxx>> wrote:
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@xxxxxxxxx]
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@xxxxxxxxxxxxxxxxx<mailto:baitha@xxxxxxxxxxxxxxxxx>> wrote:
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>
[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:
