Re: Create table performance in 11gR2
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 3 Apr 2014 20:17:41 -0700 (PDT)
Message-ID: <1396581461.96984.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>
Date: Thu, 3 Apr 2014 20:17:41 -0700 (PDT)
Message-ID: <1396581461.96984.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>
I understand that you have it running in parallel, my question is why? And a second question is: which parallel init parameters are set in that database? 11.2 has a 'better' parallel mechanism you can use but it takes a little bit of setup to get it working. The parallel_adaptive_multi_user parameter isn't the best to have set in 11.2.0.2 and later releases; I wrote an article on it here: http://www.databasejournal.com/features/oracle/oracle-parallel-processing-new-and-improved.html This may help improve your parallel performance. David Fitzjarrell Primary author, "Oracle Exadata Survival Guide" On Thursday, April 3, 2014 8:34 PM, Bheemsen Aitha <baitha_at_itradenetwork.com> wrote: 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_at_yahoo.com] 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_at_itradenetwork.com> 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_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> 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] 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 - 05:17:41 CEST