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