Re: Create table performance in 11gR2
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 3 Apr 2014 21:50:17 -0700 (PDT)
Message-ID: <1396587017.75976.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>
Date: Thu, 3 Apr 2014 21:50:17 -0700 (PDT)
Message-ID: <1396587017.75976.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>
parallel_adaptive_multi_user is a hog in 11.2.0.2 and later releases; it's a good bet that's the reason the parallel performance is so sluggish. Please read the article I posted in my last response as it describes the newer parallel statement management mechanism in 11.2.0.2 and how to configure it. David Fitzjarrell Primary author, "Oracle Exadata Survival Guide" On Thursday, April 3, 2014 10:06 PM, Bheemsen Aitha <baitha_at_itradenetwork.com> wrote: I am running it in parallel because my source table is a partitioned table. Here are my 11g settings. SQL> show parameter parallel NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ fast_start_parallel_rollback string LOW parallel_adaptive_multi_user boolean TRUE parallel_automatic_tuning boolean FALSE parallel_degree_limit string CPU parallel_degree_policy string MANUAL parallel_execution_message_size integer 16384 parallel_force_local boolean FALSE parallel_instance_group string parallel_io_cap_enabled boolean FALSE parallel_max_servers integer 32 parallel_min_percent integer 0 parallel_min_servers integer 0 parallel_min_time_threshold string AUTO parallel_server boolean FALSE parallel_server_instances integer 1 parallel_servers_target integer 32 parallel_threads_per_cpu integer 2 recovery_parallelism integer 0 Thanks Bheem Aitha Sr. Oracle, MySQL and Teradata DBA iTradenetwork From:David Fitzjarrell [mailto:oratune_at_yahoo.com] Sent: Thursday, April 03, 2014 8:18 PM To: Bheemsen Aitha; Dominic Brooks Cc: ORACLE-L Subject: Re: Create table performance in 11gR2 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@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"@"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 - 06:50:17 CEST