Re: Create table performance in 11gR2
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 3 Apr 2014 18:27:10 -0700 (PDT)
Message-ID: <1396574830.54492.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>
Date: Thu, 3 Apr 2014 18:27:10 -0700 (PDT)
Message-ID: <1396574830.54492.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>
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(@"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_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 - 03:27:10 CEST