RE: Create table performance in 11gR2

From: Bheemsen Aitha <baitha_at_itradenetwork.com>
Date: Thu, 3 Apr 2014 21:25:21 +0000
Message-ID: <3E9FC3C66B6DD445A50671ECBA1F423EACA78753_at_plt-exch-01.Itradenetwork.com>

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-l

Received on Thu Apr 03 2014 - 23:25:21 CEST