1366133.1 SQLHC 12.1.06 Report: sqlhc_20150304_114844_00pspjfdy328d_3_execution_plans.html

License    : T
Input      : 00pspjfdy328d
SIGNATURE  : 16432764016050952009
SIGNATUREF : 13626564656978028
RDBMS      : 11.2.0.3.0
Platform   : IBM/AIX RISC SYSTEM/6000
Database   : bdpdb
DBID       : 3936693823
Host       : bdpdbserver
Instance   : 1
CPU_Count  : 24
Num CPUs   : 24
Num Cores  : 6
Num Sockets:
Block Size : 16384
OFE        : 11.2.0.3
DYN_SAMP   : 2
EBS        : ""
SIEBEL     : ""
PSFT       : ""
Date       : 2015-03-04/11:48:44
User       : SYS

SQL Text

DELETE FROM DDS.CUP_ACCOUNT T1 WHERE EXISTS(   SELECT 1      FROM ODS.CUP_ACCOUNT T2    WHERE T2.BDP_ETL_DATE = '20150201'      AND T1.ACCT_NMBR = SUBSTR(T2.CARD_NMBR,1,13)      AND T1.CURR_CODE = TRIM(T2.CURR_CODE) )

Current Execution Plans (last execution)

Captured while still in memory. Metrics below are for the last execution of each child cursor.
If STATISTICS_LEVEL was set to ALL at the time of the hard-parse then A-Rows column is populated.

Current Execution Plans (all executions)

Captured while still in memory. Metrics below are an aggregate for all the execution of each child cursor.
If STATISTICS_LEVEL was set to ALL at the time of the hard-parse then A-Rows column is populated.

Historical Execution Plans

This section includes data captured by AWR. If this is a stand-by read-only database then the AWR information below is from the Primary database.
SQL_ID 00pspjfdy328d
--------------------
DELETE FROM DDS.CUP_ACCOUNT T1 WHERE EXISTS(   SELECT 1      FROM
ODS.CUP_ACCOUNT T2    WHERE T2.BDP_ETL_DATE = '20150201'      AND
T1.ACCT_NMBR = SUBSTR(T2.CARD_NMBR,1,13)      AND T1.CURR_CODE =
TRIM(T2.CURR_CODE) )

Plan hash value: 2786671412

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |                               |       |       |       |   199K(100)|          |
|   1 |  DELETE                | CUP_ACCOUNT                   |       |       |       |            |          |
|   2 |   HASH JOIN RIGHT SEMI |                               |  2172K|    93M|    90M|   199K (11)| 00:13:44 |
|   3 |    INDEX FAST FULL SCAN| IDX_CUP_ACCOUNT_EDT_CNMBR_CCD |  2445K|    62M|       |  1174  (51)| 00:00:05 |
|   4 |    TABLE ACCESS FULL   | CUP_ACCOUNT                   |    13M|   238M|       |   193K (11)| 00:13:19 |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3BA1AD7C
   3 - SEL$3BA1AD7C / T2@SEL$1
   4 - SEL$3BA1AD7C / T1@DEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('_optimizer_distinct_agg_transform' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3BA1AD7C")
      UNNEST(@"SEL$1")
      OUTLINE(@"DEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$3BA1AD7C" "T1"@"DEL$1")
      INDEX_FFS(@"SEL$3BA1AD7C" "T2"@"SEL$1" "IDX_CUP_ACCOUNT_EDT_CNMBR_CCD")
      LEADING(@"SEL$3BA1AD7C" "T1"@"DEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$3BA1AD7C" "T2"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$3BA1AD7C" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */


1366133.1 SQLHC 12.1.06 2015-03-04/11:48:44