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>


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-l
Received on Fri Apr 04 2014 - 03:27:10 CEST

Original text of this message