Home » SQL & PL/SQL » SQL & PL/SQL » Explain plan differents for same query, resulting in longer query execution time (10g)
Explain plan differents for same query, resulting in longer query execution time [message #418096] Wed, 12 August 2009 23:00 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi
I am having problem where same query returns data in different times in two databses (development and uat environment)

Below are the the explain plan from both queries.

first explain plan returns data in ~15 mins, secon one takes ~40 mins


Both the queries source tables have almost same rows, only difference is with execution time.

I understand based on number of rows processed, it may lead to diffrent explain plan, thats fine, but the issue is the rows in the underlying tables is not that much different between both enviroments, and the execution time is almost 3 times more, which does not make much sense

Statistics are uptodate for all underlying tables used in this query, i have analyzed all tables / index using following commond

analyze table <> estimate statistics sample 30 percent;
analyze index <> estimate statistics sample 30 percent;


I even tried with the following, but made no differents

EXEC dbms_stats.gather_table_stats(ownname => '?', tabname => '?', estimate_percent => 30, method_opt => 'for all indexed columns size auto');



I browsed through the net; it suggests i check

1) show parameter  PGA_AGGREGATE_TARGET; -- both db instances returns same values
2) analyze all table -- even after analyzing, problem is not solved
3) check optimizer -- both have first_rows value
4) If no of indexes are same -- yes both same


What could be the possible root cause of this, im kind of running out of ideas on what else to look for. Thank you.

First

PLAN_TABLE_OUTPUT

 
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                           | Rows  | Bytes | Cost  | TQ/Ins |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                                |  1296K|   739M|   613K|        |      |            |
|   1 |  PX COORDINATOR                                              |                                |       |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                                        | :TQ10021                       |  1296K|   739M|   613K|  Q1,21 | P->S | QC (RAND)  |
|   3 |    SORT GROUP BY                                             |                                |  1296K|   739M|   613K|  Q1,21 | PCWP |            |
|   4 |     PX RECEIVE                                               |                                |  1296K|   739M|   594K|  Q1,21 | PCWP |            |
|   5 |      PX SEND HASH                                            | :TQ10020                       |  1296K|   739M|   594K|  Q1,20 | P->P | HASH       |
|   6 |       HASH JOIN RIGHT OUTER BUFFERED                         |                                |  1296K|   739M|   594K|  Q1,20 | PCWP |            |
|   7 |        BUFFER SORT                                           |                                |       |       |       |  Q1,20 | PCWC |            |
|   8 |         PX RECEIVE                                           |                                |   569K|  7228K|  1604 |  Q1,20 | PCWP |            |
|   9 |          PX SEND HASH                                        | :TQ10014                       |   569K|  7228K|  1604 |        | S->P | HASH       |
|  10 |           TABLE ACCESS FULL                                  | ODS_CS_HZ_SR_CONTACT_POINTS    |   569K|  7228K|  1604 |        |      |            |
|  11 |        PX RECEIVE                                            |                                |  1294K|   722M|   592K|  Q1,20 | PCWP |            |
|  12 |         PX SEND HASH                                         | :TQ10019                       |  1294K|   722M|   592K|  Q1,19 | P->P | HASH       |
|  13 |          HASH JOIN RIGHT OUTER BUFFERED                      |                                |  1294K|   722M|   592K|  Q1,19 | PCWP |            |
|  14 |           BUFFER SORT                                        |                                |       |       |       |  Q1,19 | PCWC |            |
|  15 |            PX RECEIVE                                        |                                |   902K|  9695K|  1116 |  Q1,19 | PCWP |            |
|  16 |             PX SEND HASH                                     | :TQ10013                       |   902K|  9695K|  1116 |        | S->P | HASH       |
|  17 |              INDEX FAST FULL SCAN                            | ODS_HZ_PARTY_SITE_USES_N1      |   902K|  9695K|  1116 |        |      |            |
|  18 |           PX RECEIVE                                         |                                |  1294K|   708M|   591K|  Q1,19 | PCWP |            |
|  19 |            PX SEND HASH                                      | :TQ10018                       |  1294K|   708M|   591K|  Q1,18 | P->P | HASH       |
|  20 |             HASH JOIN RIGHT OUTER BUFFERED                   |                                |  1294K|   708M|   591K|  Q1,18 | PCWP |            |
|  21 |              BUFFER SORT                                     |                                |       |       |       |  Q1,18 | PCWC |            |
|  22 |               PX RECEIVE                                     |                                |   604K|    22M|  8557 |  Q1,18 | PCWP |            |
|  23 |                PX SEND HASH                                  | :TQ10012                       |   604K|    22M|  8557 |        | S->P | HASH       |
|  24 |                 TABLE ACCESS FULL                            | ODS_CS_INCIDENTS_ALL_TL        |   604K|    22M|  8557 |        |      |            |
|  25 |              PX RECEIVE                                      |                                |  1216K|   620M|   583K|  Q1,18 | PCWP |            |
|  26 |               PX SEND HASH                                   | :TQ10017                       |  1216K|   620M|   583K|  Q1,17 | P->P | HASH       |
|  27 |                BUFFER SORT                                   |                                |  1296K|   739M|   613K|  Q1,17 | PCWP |            |
|  28 |                 NESTED LOOPS OUTER                           |                                |  1216K|   620M|   583K|  Q1,17 | PCWP |            |
|  29 |                  NESTED LOOPS OUTER                          |                                |  1216K|   605M|   420K|  Q1,17 | PCWP |            |
|  30 |                   HASH JOIN RIGHT OUTER                      |                                |  1216K|   585M|   291K|  Q1,17 | PCWP |            |
|  31 |                    BUFFER SORT                               |                                |       |       |       |  Q1,17 | PCWC |            |
|  32 |                     PX RECEIVE                               |                                |   527K|  8246K|  1360 |  Q1,17 | PCWP |            |
|  33 |                      PX SEND HASH                            | :TQ10010                       |   527K|  8246K|  1360 |        | S->P | HASH       |
|  34 |                       TABLE ACCESS FULL                      | ODS_CS_INCIDENTS_ALL_B_A       |   527K|  8246K|  1360 |        |      |            |
|  35 |                    HASH JOIN RIGHT OUTER                     |                                |  1216K|   567M|   289K|  Q1,17 | PCWP |            |
|  36 |                     BUFFER SORT                              |                                |       |       |       |  Q1,17 | PCWC |            |
|  37 |                      PX RECEIVE                              |                                |   405K|  7130K| 73458 |  Q1,17 | PCWP |            |
|  38 |                       PX SEND HASH                           | :TQ10011                       |   405K|  7130K| 73458 |        | S->P | HASH       |
|  39 |                        VIEW                                  |                                |   405K|  7130K| 73458 |        |      |            |
|  40 |                         HASH UNIQUE                          |                                |   405K|    10M| 73458 |        |      |            |
|  41 |                          WINDOW SORT                         |                                |   405K|    10M| 73458 |        |      |            |
|  42 |                           HASH JOIN                          |                                |   405K|    10M| 62840 |        |      |            |
|  43 |                            TABLE ACCESS FULL                 | ODS_CS_ESTIMATE_DETAILS        |   404K|  6320K| 14042 |        |      |            |
|  44 |                            TABLE ACCESS FULL                 | ODS_ORA_COT_OE_ORDER_HEAD_ALL  |  2974K|    34M| 44515 |        |      |            |
|  45 |                     PX RECEIVE                               |                                |  1216K|   546M|   216K|  Q1,17 | PCWP |            |
|  46 |                      PX SEND HASH                            | :TQ10016                       |  1216K|   546M|   216K|  Q1,16 | P->P | HASH       |
|  47 |                       HASH JOIN RIGHT OUTER                  |                                |  1216K|   546M|   216K|  Q1,16 | PCWP |            |
|  48 |                        BUFFER SORT                           |                                |       |       |       |  Q1,16 | PCWC |            |
|  49 |                         PX RECEIVE                           |                                |   243K|  1191K|   210 |  Q1,16 | PCWP |            |
|  50 |                          PX SEND BROADCAST                   | :TQ10000                       |   243K|  1191K|   210 |        | S->P | BROADCAST  |
|  51 |                           INDEX FAST FULL SCAN               | ODS_ORA_COA_HZ_CUST_ACCTS_PK   |   243K|  1191K|   210 |        |      |            |
|  52 |                        HASH JOIN RIGHT OUTER                 |                                |  1216K|   540M|   216K|  Q1,16 | PCWP |            |
|  53 |                         BUFFER SORT                          |                                |       |       |       |  Q1,16 | PCWC |            |
|  54 |                          PX RECEIVE                          |                                | 64661 |  1262K| 28450 |  Q1,16 | PCWP |            |
|  55 |                           PX SEND BROADCAST                  | :TQ10001                       | 64661 |  1262K| 28450 |        | S->P | BROADCAST  |
|  56 |                            VIEW                              | index$_join$_009               | 64661 |  1262K| 28450 |        |      |            |
|  57 |                             HASH JOIN                        |                                |       |       |       |        |      |            |
|  58 |                              INDEX RANGE SCAN                | ODS_CS_INCIDENTS_AUDIT_B_N1    | 64661 |  1262K|   106K|        |      |            |
|  59 |                              INDEX FAST FULL SCAN            | ODS_CS_INCIDENTS_AUDIT_B_N2    | 64661 |  1262K| 13308 |        |      |            |
|  60 |                         HASH JOIN RIGHT OUTER                |                                |  1216K|   517M|   187K|  Q1,16 | PCWP |            |
|  61 |                          BUFFER SORT                         |                                |       |       |       |  Q1,16 | PCWC |            |
|  62 |                           PX RECEIVE                         |                                | 50908 |   994K| 28450 |  Q1,16 | PCWP |            |
|  63 |                            PX SEND BROADCAST                 | :TQ10002                       | 50908 |   994K| 28450 |        | S->P | BROADCAST  |
|  64 |                             VIEW                             | index$_join$_013               | 50908 |   994K| 28450 |        |      |            |
|  65 |                              HASH JOIN                       |                                |       |       |       |        |      |            |
|  66 |                               INDEX RANGE SCAN               | ODS_CS_INCIDENTS_AUDIT_B_N1    | 50908 |   994K|   106K|        |      |            |
|  67 |                               INDEX FAST FULL SCAN           | ODS_CS_INCIDENTS_AUDIT_B_N2    | 50908 |   994K| 13308 |        |      |            |
|  68 |                          HASH JOIN RIGHT OUTER               |                                |  1216K|   494M|   159K|  Q1,16 | PCWP |            |
|  69 |                           BUFFER SORT                        |                                |       |       |       |  Q1,16 | PCWC |            |
|  70 |                            PX RECEIVE                        |                                | 46807 |   914K| 28450 |  Q1,16 | PCWP |            |
|  71 |                             PX SEND BROADCAST                | :TQ10003                       | 46807 |   914K| 28450 |        | S->P | BROADCAST  |
|  72 |                              VIEW                            | index$_join$_006               | 46807 |   914K| 28450 |        |      |            |
|  73 |                               HASH JOIN                      |                                |       |       |       |        |      |            |
|  74 |                                INDEX RANGE SCAN              | ODS_CS_INCIDENTS_AUDIT_B_N1    | 46807 |   914K|   106K|        |      |            |
|  75 |                                INDEX FAST FULL SCAN          | ODS_CS_INCIDENTS_AUDIT_B_N2    | 46807 |   914K| 13308 |        |      |            |
|  76 |                           HASH JOIN RIGHT OUTER              |                                |  1216K|   470M|   130K|  Q1,16 | PCWP |            |
|  77 |                            BUFFER SORT                       |                                |       |       |       |  Q1,16 | PCWC |            |
|  78 |                             PX RECEIVE                       |                                | 46083 |   900K| 28450 |  Q1,16 | PCWP |            |
|  79 |                              PX SEND BROADCAST               | :TQ10004                       | 46083 |   900K| 28450 |        | S->P | BROADCAST  |
|  80 |                               VIEW                           | index$_join$_010               | 46083 |   900K| 28450 |        |      |            |
|  81 |                                HASH JOIN                     |                                |       |       |       |        |      |            |
|  82 |                                 INDEX RANGE SCAN             | ODS_CS_INCIDENTS_AUDIT_B_N1    | 46083 |   900K|   106K|        |      |            |
|  83 |                                 INDEX FAST FULL SCAN         | ODS_CS_INCIDENTS_AUDIT_B_N2    | 46083 |   900K| 13308 |        |      |            |
|  84 |                            HASH JOIN RIGHT OUTER             |                                |  1216K|   447M|   102K|  Q1,16 | PCWP |            |
|  85 |                             BUFFER SORT                      |                                |       |       |       |  Q1,16 | PCWC |            |
|  86 |                              PX RECEIVE                      |                                | 27746 |   541K| 28450 |  Q1,16 | PCWP |            |
|  87 |                               PX SEND BROADCAST              | :TQ10005                       | 27746 |   541K| 28450 |        | S->P | BROADCAST  |
|  88 |                                VIEW                          | index$_join$_011               | 27746 |   541K| 28450 |        |      |            |
|  89 |                                 HASH JOIN                    |                                |       |       |       |        |      |            |
|  90 |                                  INDEX RANGE SCAN            | ODS_CS_INCIDENTS_AUDIT_B_N1    | 27746 |   541K|   106K|        |      |            |
|  91 |                                  INDEX FAST FULL SCAN        | ODS_CS_INCIDENTS_AUDIT_B_N2    | 27746 |   541K| 13308 |        |      |            |
|  92 |                             HASH JOIN RIGHT OUTER            |                                |  1216K|   424M| 73800 |  Q1,16 | PCWP |            |
|  93 |                              BUFFER SORT                     |                                |       |       |       |  Q1,16 | PCWC |            |
|  94 |                               PX RECEIVE                     |                                | 11822 |   230K| 20734 |  Q1,16 | PCWP |            |
|  95 |                                PX SEND BROADCAST             | :TQ10006                       | 11822 |   230K| 20734 |        | S->P | BROADCAST  |
|  96 |                                 TABLE ACCESS BY INDEX ROWID  | ODS_CS_INCIDENTS_AUDIT_B       | 11822 |   230K| 20734 |        |      |            |
|  97 |                                  INDEX RANGE SCAN            | ODS_CS_INCIDENTS_AUDIT_B_N1    | 11822 |       |  9455 |        |      |            |
|  98 |                              HASH JOIN RIGHT OUTER           |                                |  1216K|   401M| 53065 |  Q1,16 | PCWP |            |
|  99 |                               BUFFER SORT                    |                                |       |       |       |  Q1,16 | PCWC |            |
| 100 |                                PX RECEIVE                    |                                |  4825 | 96500 | 14059 |  Q1,16 | PCWP |            |
| 101 |                                 PX SEND BROADCAST            | :TQ10007                       |  4825 | 96500 | 14059 |        | S->P | BROADCAST  |
| 102 |                                  TABLE ACCESS BY INDEX ROWID | ODS_CS_INCIDENTS_AUDIT_B       |  4825 | 96500 | 14059 |        |      |            |
| 103 |                                   INDEX RANGE SCAN           | ODS_CS_INCIDENTS_AUDIT_B_N1    |  4825 |       |  9455 |        |      |            |
| 104 |                               HASH JOIN RIGHT OUTER          |                                |  1216K|   378M| 39006 |  Q1,16 | PCWP |            |
| 105 |                                BUFFER SORT                   |                                |       |       |       |  Q1,16 | PCWC |            |
| 106 |                                 PX RECEIVE                   |                                |  4584 | 91680 | 13829 |  Q1,16 | PCWP |            |
| 107 |                                  PX SEND BROADCAST           | :TQ10008                       |  4584 | 91680 | 13829 |        | S->P | BROADCAST  |
| 108 |                                   TABLE ACCESS BY INDEX ROWID| ODS_CS_INCIDENTS_AUDIT_B       |  4584 | 91680 | 13829 |        |      |            |
| 109 |                                    INDEX RANGE SCAN          | ODS_CS_INCIDENTS_AUDIT_B_N1    |  4584 |       |  9455 |        |      |            |
| 110 |                                HASH JOIN RIGHT OUTER         |                                |  1216K|   354M| 25177 |  Q1,16 | PCWP |            |
| 111 |                                 PX RECEIVE                   |                                |  3199 | 41587 |     4 |  Q1,16 | PCWP |            |
| 112 |                                  PX SEND HASH                | :TQ10015                       |  3199 | 41587 |     4 |  Q1,15 | P->P | HASH       |
| 113 |                                   PX BLOCK ITERATOR          |                                |  3199 | 41587 |     4 |  Q1,15 | PCWC |            |
| 114 |                                    TABLE ACCESS FULL         | ODS_ORA_COA_APPS_FND_USER      |  3199 | 41587 |     4 |  Q1,15 | PCWP |            |
| 115 |                                 BUFFER SORT                  |                                |       |       |       |  Q1,16 | PCWC |            |
| 116 |                                  PX RECEIVE                  |                                |  1216K|   339M| 25172 |  Q1,16 | PCWP |            |
| 117 |                                   PX SEND HASH               | :TQ10009                       |  1216K|   339M| 25172 |        | S->P | HASH       |
| 118 |                                    HASH JOIN RIGHT OUTER     |                                |  1216K|   339M| 25172 |        |      |            |
| 119 |                                     TABLE ACCESS FULL        | ODS_CS_SR_OWNERS_V             |  2633 | 97421 |     8 |        |      |            |
| 120 |                                     HASH JOIN RIGHT OUTER    |                                |  1216K|   296M| 25159 |        |      |            |
| 121 |                                      TABLE ACCESS BY INDEX RO| ODS_CS_INCIDENTS_AUDIT_B       |  1206 | 24120 | 10606 |        |      |            |
| 122 |                                       INDEX RANGE SCAN       | ODS_CS_INCIDENTS_AUDIT_B_N1    |  1206 |       |  9455 |        |      |            |
| 123 |                                      HASH JOIN RIGHT OUTER   |                                |  1216K|   273M| 14548 |        |      |            |
| 124 |                                       INDEX RANGE SCAN       | PK_FND_LKP_VAL                 |   102 |  3060 |     8 |        |      |            |
| 125 |                                       HASH JOIN RIGHT OUTER  |                                |  1216K|   238M| 14535 |        |      |            |
| 126 |                                        INDEX RANGE SCAN      | PK_FND_LKP_VAL                 |   102 |  3060 |     8 |        |      |            |
| 127 |                                        HASH JOIN             |                                |  1216K|   204M| 14522 |        |      |            |
| 128 |                                         VIEW                 | ODS_ORA_COA_INCIDENT_STATUS_V  |    84 |   336 |     1 |        |      |            |
| 129 |                                          NESTED LOOPS        |                                |    84 |   924 |     1 |        |      |            |
| 130 |                                           INDEX FULL SCAN    | ODS_CS_INCIDENT_STATUSES_B_U1  |    84 |   336 |     1 |        |      |            |
| 131 |                                           INDEX UNIQUE SCAN  | ODS_CS_INCIDENT_STATUSES_TL_U1 |     1 |     7 |     0 |        |      |            |
| 132 |                                         HASH JOIN RIGHT OUTER|                                |  1216K|   199M| 14515 |        |      |            |
| 133 |                                          VIEW                | ODS_ORA_COA_INCIDENT_CAUSE_V   |    81 |   324 |     1 |        |      |            |
| 134 |                                           NESTED LOOPS       |                                |    81 |   891 |     1 |        |      |            |
| 135 |                                            INDEX FULL SCAN   | ODS_CS_INCIDENT_URGENCIES_B_U1 |    81 |   324 |     1 |        |      |            |
| 136 |                                            INDEX UNIQUE SCAN | ODS_CS_INCIDENT_URGNCS_TL_U1   |     1 |     7 |     0 |        |      |            |
| 137 |                                          HASH JOIN           |                                |  1216K|   194M| 14509 |        |      |            |
| 138 |                                           VIEW               | ODS_ORA_COA_INCIDENT_TYPES_V   |    31 |   124 |     1 |        |      |            |
| 139 |                                            NESTED LOOPS      |                                |    31 |   341 |     1 |        |      |            |
| 140 |                                             INDEX FULL SCAN  | ODS_CS_INCIDENT_TYPES_B_U1     |    31 |   124 |     1 |        |      |            |
| 141 |                                             INDEX UNIQUE SCAN| ODS_CS_INCIDENT_TYPES_TL_U1    |     1 |     7 |     0 |        |      |            |
| 142 |                                           HASH JOIN RIGHT OUT|                                |  1216K|   190M| 14503 |        |      |            |
| 143 |                                            INDEX FULL SCAN   | ODS_CS_INCIDENT_SEVERITY_TL_U1 |    12 |    72 |     1 |        |      |            |
| 144 |                                            HASH JOIN         |                                |  1163K|   175M| 14497 |        |      |            |
| 145 |                                             VIEW             | DIM_GLB_OPERATING_UNITS_V      |    10 |   130 |    12 |        |      |            |
| 146 |                                              HASH UNIQUE     |                                |    10 |  3910 |    12 |        |      |            |
| 147 |                                               VIEW           | DIM_GLB_ORGS_V                 |    10 |  3910 |    11 |        |      |            |
| 148 |                                                SORT UNIQUE   |                                |    10 |   740 |    11 |        |      |            |
| 149 |                                                 UNION-ALL    |                                |       |       |       |        |      |            |
| 150 |                                                  REMOTE      |                                |       |       |       |  DEV | R->S |            |
| 151 |                                                  TABLE ACCESS| NONORACLE_ORGANIZATION         |    10 |   740 |    10 |        |      |            |
| 152 |                                             TABLE ACCESS FULL| ODS_CS_INCIDENTS_ALL_B         |   581K|    80M| 14480 |        |      |            |
| 153 |                   TABLE ACCESS BY INDEX ROWID                | ODS_CSI_ITEM_INSTANCES         |     1 |    17 |     2 |  Q1,17 | PCWP |            |
| 154 |                    INDEX UNIQUE SCAN                         | ODS_CSI_ITEM_INSTANCES_U1      |     1 |       |     1 |  Q1,17 | PCWP |            |
| 155 |                  TABLE ACCESS BY INDEX ROWID                 | ODS_ORA_COT_OE_ORDER_LINES_ALL |     1 |    13 |     2 |  Q1,17 | PCWP |            |
| 156 |                   INDEX UNIQUE SCAN                          | ODS_ORA_COT_OE_ODER_LIN_ALL_PK |     1 |       |     1 |  Q1,17 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
 150 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A3"."SET_OF_BOOKS_ID","A3"."OPERATING_UNIT","A2"."NAME","A3"."ORGANIZATION_ID","A3"."ORGANIZATION_
         NAME","A3"."LEGAL_ENTITY","A1"."NAME",CASE "A3"."OPERATING_UNIT" WHEN 122 THEN 'Show `Non-Oracle` Entities' ELSE 'Exclude `Non-Oracle` Entities' 
         END ,"A2"."DATE_FROM","A2"."DATE_TO" FROM "APPS"."ORG_ORGANIZATION_DEFINITIONS" "A3","APPS"."HR_ORGANIZATION_UNITS" 
         "A2","APPS"."HR_ORGANIZATION_UNITS" "A1" WHERE "A3"."OPERATING_UNIT"="A2"."ORGANIZATION_ID" AND "A3"."LEGAL_ENTITY"="A1"."ORGANIZATION_ID" 
         (accessing 'DEV' )
 
 
Note
-----
   - 'PLAN_TABLE' is old version




Second


PLAN_TABLE_OUTPUT

 
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                           | Rows  | Bytes | Cost  | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                                |  1304K|   665M|  1091K|        |      |
|   1 |  SORT GROUP BY                                 |                                |  1304K|   665M|  1091K|        |      |
|   2 |   HASH JOIN                                    |                                |  1304K|   665M|   842K|        |      |
|   3 |    VIEW                                        | DIM_GLB_OPERATING_UNITS_V      |     9 |   117 |    13 |        |      |
|   4 |     HASH UNIQUE                                |                                |     9 |  3519 |    13 |        |      |
|   5 |      VIEW                                      | DIM_GLB_ORGS_V                 |     9 |  3519 |    12 |        |      |
|   6 |       SORT UNIQUE                              |                                |     9 |   648 |    12 |        |      |
|   7 |        UNION-ALL                               |                                |       |       |       |        |      |
|   8 |         REMOTE                                 |                                |       |       |       |    UAT | R->S |
|   9 |         TABLE ACCESS FULL                      | NONORACLE_ORGANIZATION         |     9 |   648 |    11 |        |      |
|  10 |    HASH JOIN RIGHT OUTER                       |                                |   724K|   360M|   842K|        |      |
|  11 |     INDEX RANGE SCAN                           | PK_FND_LKP_VAL                 |    12 |   324 |     3 |        |      |
|  12 |     HASH JOIN RIGHT OUTER                      |                                |   724K|   342M|   842K|        |      |
|  13 |      INDEX RANGE SCAN                          | PK_FND_LKP_VAL                 |    12 |   324 |     3 |        |      |
|  14 |      HASH JOIN RIGHT OUTER                     |                                |   724K|   323M|   842K|        |      |
|  15 |       VIEW                                     |                                |   408K|  7176K| 74258 |        |      |
|  16 |        HASH UNIQUE                             |                                |   408K|    10M| 74258 |        |      |
|  17 |         WINDOW SORT                            |                                |   408K|    10M| 74258 |        |      |
|  18 |          HASH JOIN                             |                                |   408K|    10M| 64092 |        |      |
|  19 |           TABLE ACCESS FULL                    | ODS_CS_ESTIMATE_DETAILS        |   408K|  6379K| 14032 |        |      |
|  20 |           TABLE ACCESS FULL                    | ODS_ORA_COT_OE_ORDER_HEAD_ALL  |  3136K|    29M| 45894 |        |      |
|  21 |       HASH JOIN RIGHT OUTER                    |                                |   724K|   310M|   750K|        |      |
|  22 |        TABLE ACCESS FULL                       | ODS_CS_HZ_SR_CONTACT_POINTS    |   304K|  2969K|  1572 |        |      |
|  23 |        HASH JOIN RIGHT OUTER                   |                                |   724K|   304M|   731K|        |      |
|  24 |         VIEW                                   | ODS_ORA_COA_INCIDENT_CAUSE_V   |    81 |   243 |     1 |        |      |
|  25 |          NESTED LOOPS                          |                                |    81 |   648 |     1 |        |      |
|  26 |           INDEX FULL SCAN                      | ODS_CS_INCIDENT_URGENCIES_B_U1 |    81 |   243 |     1 |        |      |
|  27 |           INDEX UNIQUE SCAN                    | ODS_CS_INCIDENT_URGNCS_TL_U1   |     1 |     5 |     0 |        |      |
|  28 |         HASH JOIN RIGHT OUTER                  |                                |   660K|   275M|   731K|        |      |
|  29 |          TABLE ACCESS FULL                     | ODS_CS_INCIDENTS_ALL_B_A       |   368K|  4675K|  1350 |        |      |
|  30 |          HASH JOIN RIGHT OUTER                 |                                |   660K|   267M|   715K|        |      |
|  31 |           TABLE ACCESS BY INDEX ROWID          | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  32 |            INDEX RANGE SCAN                    | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  33 |           HASH JOIN RIGHT OUTER                |                                |   660K|   257M|   694K|        |      |
|  34 |            TABLE ACCESS BY INDEX ROWID         | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  35 |             INDEX RANGE SCAN                   | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  36 |            HASH JOIN RIGHT OUTER               |                                |   660K|   247M|   673K|        |      |
|  37 |             TABLE ACCESS BY INDEX ROWID        | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  38 |              INDEX RANGE SCAN                  | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  39 |             HASH JOIN RIGHT OUTER              |                                |   660K|   236M|   652K|        |      |
|  40 |              TABLE ACCESS BY INDEX ROWID       | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  41 |               INDEX RANGE SCAN                 | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  42 |              HASH JOIN RIGHT OUTER             |                                |   660K|   226M|   631K|        |      |
|  43 |               TABLE ACCESS BY INDEX ROWID      | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  44 |                INDEX RANGE SCAN                | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  45 |               HASH JOIN RIGHT OUTER            |                                |   660K|   216M|   610K|        |      |
|  46 |                TABLE ACCESS BY INDEX ROWID     | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  47 |                 INDEX RANGE SCAN               | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  48 |                HASH JOIN RIGHT OUTER           |                                |   660K|   206M|   589K|        |      |
|  49 |                 TABLE ACCESS BY INDEX ROWID    | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  50 |                  INDEX RANGE SCAN              | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  51 |                 HASH JOIN RIGHT OUTER          |                                |   660K|   196M|   568K|        |      |
|  52 |                  TABLE ACCESS BY INDEX ROWID   | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  53 |                   INDEX RANGE SCAN             | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  54 |                  HASH JOIN RIGHT OUTER         |                                |   660K|   186M|   547K|        |      |
|  55 |                   TABLE ACCESS BY INDEX ROWID  | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  56 |                    INDEX RANGE SCAN            | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  57 |                   HASH JOIN RIGHT OUTER        |                                |   660K|   176M|   527K|        |      |
|  58 |                    TABLE ACCESS FULL           | ODS_CS_SR_OWNERS_V             |  2651 | 90134 |     8 |        |      |
|  59 |                    HASH JOIN RIGHT OUTER       |                                |   660K|   155M|   527K|        |      |
|  60 |                     TABLE ACCESS FULL          | ODS_ORA_COA_APPS_FND_USER      |  4486 | 49346 |    74 |        |      |
|  61 |                     HASH JOIN RIGHT OUTER      |                                |   660K|   148M|   527K|        |      |
|  62 |                      INDEX FAST FULL SCAN      | ODS_ORA_COA_HZ_CUST_ACCTS_PK   |   264K|  1034K|   261 |        |      |
|  63 |                      HASH JOIN RIGHT OUTER     |                                |   660K|   145M|   518K|        |      |
|  64 |                       INDEX FULL SCAN          | ODS_CS_INCIDENT_SEVERITY_TL_U1 |    12 |    48 |     1 |        |      |
|  65 |                       HASH JOIN OUTER          |                                |   605K|   131M|   518K|        |      |
|  66 |                        HASH JOIN OUTER         |                                |   605K|   124M|   277K|        |      |
|  67 |                         HASH JOIN RIGHT OUTER  |                                |   605K|   114M| 36372 |        |      |
|  68 |                          TABLE ACCESS FULL     | ODS_CS_INCIDENTS_ALL_TL        |   605K|    22M|  8523 |        |      |
|  69 |                          HASH JOIN RIGHT OUTER |                                |   581K|    88M| 21132 |        |      |
|  70 |                           INDEX FAST FULL SCAN | ODS_HZ_PARTY_SITE_USES_N1      |   930K|  7272K|  1110 |        |      |
|  71 |                           HASH JOIN            |                                |   581K|    83M| 14175 |        |      |
|  72 |                            VIEW                | ODS_ORA_COA_INCIDENT_STATUS_V  |    81 |   243 |     1 |        |      |
|  73 |                             NESTED LOOPS       |                                |    81 |   648 |     1 |        |      |
|  74 |                              INDEX FULL SCAN   | ODS_CS_INCIDENT_STATUSES_B_U1  |    81 |   243 |     1 |        |      |
|  75 |                              INDEX UNIQUE SCAN | ODS_CS_INCIDENT_STATUSES_TL_U1 |     1 |     5 |     0 |        |      |
|  76 |                            HASH JOIN           |                                |   581K|    82M| 14171 |        |      |
|  77 |                             VIEW               | ODS_ORA_COA_INCIDENT_TYPES_V   |    31 |    93 |     1 |        |      |
|  78 |                              NESTED LOOPS      |                                |    31 |   248 |     1 |        |      |
|  79 |                               INDEX FULL SCAN  | ODS_CS_INCIDENT_TYPES_B_U1     |    31 |    93 |     1 |        |      |
|  80 |                               INDEX UNIQUE SCAN| ODS_CS_INCIDENT_TYPES_TL_U1    |     1 |     5 |     0 |        |      |
|  81 |                             TABLE ACCESS FULL  | ODS_CS_INCIDENTS_ALL_B         |   581K|    80M| 14168 |        |      |
|  82 |                         TABLE ACCESS FULL      | ODS_CSI_ITEM_INSTANCES         |    18M|   305M|   205K|        |      |
|  83 |                        TABLE ACCESS FULL       | ODS_ORA_COT_OE_ORDER_LINES_ALL |  8092K|    92M|   224K|        |      |
---------------------------------------------------------------------------------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   8 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A3"."SET_OF_BOOKS_ID","A3"."OPERATING_UNIT","A2"."NAME","A3"."ORGANIZAT
       ION_ID","A3"."ORGANIZATION_NAME","A3"."LEGAL_ENTITY","A1"."NAME",'Exclude `Non-Oracle` 
       Entities',"A2"."DATE_FROM","A2"."DATE_TO" FROM "APPS"."ORG_ORGANIZATION_DEFINITIONS" "A3","APPS"."HR_ORGANIZATION_UNITS" 
       "A2","APPS"."HR_ORGANIZATION_UNITS" "A1" WHERE "A3"."OPERATING_UNIT"="A2"."ORGANIZATION_ID" AND 
       "A3"."LEGAL_ENTITY"="A1"."ORGANIZATION_ID" (accessing 'UAT' )
 
 
Note
-----
   - 'PLAN_TABLE' is old version


Re: Explain plan differents for same query, resulting in longer query execution time [message #418101 is a reply to message #418096] Wed, 12 August 2009 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
why are the format & content of post PLAN different?
Re: Explain plan differents for same query, resulting in longer query execution time [message #418105 is a reply to message #418101] Wed, 12 August 2009 23:32 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Could you elobrate, i pasted the explan plan of the exact same query shown in 2 enviromenent using

     SELECT plan_table_output
   FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));    
Re: Explain plan differents for same query, resulting in longer query execution time [message #418107 is a reply to message #418096] Wed, 12 August 2009 23:35 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Could you elobrate
The number of columns in the posted PLANs & labels are different!

From my perspective comparing apples to tennis shoes.

Yes, the plans are different.
Re: Explain plan differents for same query, resulting in longer query execution time [message #418116 is a reply to message #418107] Thu, 13 August 2009 01:26 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
I have re-extracted the plans, as follows

First

PLAN_TABLE_OUTPUT

 
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                           | Rows  | Bytes | Cost  | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                                |  2048K|    44G|   742K|        |      |
|   1 |  PX COORDINATOR                                              |                                |       |       |       |        |      |
|   2 |   PX SEND QC (RANDOM)                                        | :TQ10037                       |  2048K|    44G|   742K|        |      |
|   3 |    HASH JOIN RIGHT OUTER BUFFERED                            |                                |  2048K|    44G|   742K|        |      |
|   4 |     BUFFER SORT                                              |                                |       |       |       |        |      |
|   5 |      PX RECEIVE                                              |                                |   153 |  1683 |     3 |        |      |
|   6 |       PX SEND BROADCAST                                      | :TQ10019                       |   153 |  1683 |     3 |        |      |
|   7 |        VIEW                                                  | index$_join$_005               |   153 |  1683 |     3 |        |      |
|   8 |         HASH JOIN                                            |                                |       |       |       |        |      |
|   9 |          INDEX FAST FULL SCAN                                | EDW_ORA_COA_REMEDY_CODE_PK     |   153 |  1683 |     1 |        |      |
|  10 |          INDEX FAST FULL SCAN                                | EDW_ORA_COA_REMEDY_CODE_UK     |   153 |  1683 |     1 |        |      |
|  11 |     HASH JOIN RIGHT OUTER                                    |                                |  2048K|    44G|   742K|        |      |
|  12 |      BUFFER SORT                                             |                                |       |       |       |        |      |
|  13 |       PX RECEIVE                                             |                                |    63 |   819 |     3 |        |      |
|  14 |        PX SEND BROADCAST                                     | :TQ10020                       |    63 |   819 |     3 |        |      |
|  15 |         VIEW                                                 | index$_join$_004               |    63 |   819 |     3 |        |      |
|  16 |          HASH JOIN                                           |                                |       |       |       |        |      |
|  17 |           INDEX FAST FULL SCAN                               | EDW_ORA_COA_PROBLEM_CODE_PK    |    63 |   819 |     1 |        |      |
|  18 |           INDEX FAST FULL SCAN                               | EDW_ORA_COA_PROBLEM_CODE_UK    |    63 |   819 |     1 |        |      |
|  19 |      HASH JOIN RIGHT OUTER                                   |                                |  2048K|    44G|   742K|        |      |
|  20 |       BUFFER SORT                                            |                                |       |       |       |        |      |
|  21 |        PX RECEIVE                                            |                                |     7 |   126 |     3 |        |      |
|  22 |         PX SEND BROADCAST                                    | :TQ10021                       |     7 |   126 |     3 |        |      |
|  23 |          VIEW                                                | index$_join$_009               |     7 |   126 |     3 |        |      |
|  24 |           HASH JOIN                                          |                                |       |       |       |        |      |
|  25 |            INDEX FAST FULL SCAN                              | EDW_ORA_COA_FAULT_OCCUR_NK     |     7 |   126 |     1 |        |      |
|  26 |            INDEX FAST FULL SCAN                              | EDW_ORA_COA_FAULT_OCCUR_UK     |     7 |   126 |     1 |        |      |
|  27 |       HASH JOIN RIGHT OUTER                                  |                                |  2048K|    44G|   742K|        |      |
|  28 |        BUFFER SORT                                           |                                |       |       |       |        |      |
|  29 |         PX RECEIVE                                           |                                |     7 |   112 |     3 |        |      |
|  30 |          PX SEND BROADCAST                                   | :TQ10022                       |     7 |   112 |     3 |        |      |
|  31 |           VIEW                                               | index$_join$_007               |     7 |   112 |     3 |        |      |
|  32 |            HASH JOIN                                         |                                |       |       |       |        |      |
|  33 |             INDEX FAST FULL SCAN                             | EDW_ORA_COA_ESC_TO_QA_NK       |     7 |   112 |     1 |        |      |
|  34 |             INDEX FAST FULL SCAN                             | EDW_ORA_COA_ESC_TO_QA_UK       |     7 |   112 |     1 |        |      |
|  35 |        HASH JOIN RIGHT OUTER                                 |                                |  2048K|    44G|   742K|        |      |
|  36 |         BUFFER SORT                                          |                                |       |       |       |        |      |
|  37 |          PX RECEIVE                                          |                                |     5 |    80 |     3 |        |      |
|  38 |           PX SEND BROADCAST                                  | :TQ10023                       |     5 |    80 |     3 |        |      |
|  39 |            VIEW                                              | index$_join$_006               |     5 |    80 |     3 |        |      |
|  40 |             HASH JOIN                                        |                                |       |       |       |        |      |
|  41 |              INDEX FAST FULL SCAN                            | EDW_EDW_ORA_COA_DEATH_INJRY_NK |     5 |    80 |     1 |        |      |
|  42 |              INDEX FAST FULL SCAN                            | EDW_EDW_ORA_COA_DEATH_INJRY_UK |     5 |    80 |     1 |        |      |
|  43 |         HASH JOIN RIGHT OUTER                                |                                |  2048K|    44G|   742K|        |      |
|  44 |          PX RECEIVE                                          |                                |   953K|  1050M| 33379 |        |      |
|  45 |           PX SEND HASH                                       | :TQ10035                       |   953K|  1050M| 33379 |        |      |
|  46 |            VIEW                                              | ODS_RESMED_CQA_COMPLAINT_V     |   953K|  1050M| 33379 |        |      |
|  47 |             VIEW                                             |                                |   953K|  1062M| 33379 |        |      |
|  48 |              WINDOW SORT PUSHED RANK                         |                                |   953K|  1050M| 33379 |        |      |
|  49 |               PX RECEIVE                                     |                                |   953K|  1050M| 33379 |        |      |
|  50 |                PX SEND HASH                                  | :TQ10033                       |   953K|  1050M| 33379 |        |      |
|  51 |                 WINDOW CHILD PUSHED RANK                     |                                |   953K|  1050M| 33379 |        |      |
|  52 |                  VIEW                                        |                                |   953K|  1050M|  6373 |        |      |
|  53 |                   UNION-ALL                                  |                                |       |       |       |        |      |
|  54 |                    HASH UNIQUE                               |                                |   476K|    64M|  3186 |        |      |
|  55 |                     PX RECEIVE                               |                                |   476K|    64M|  1423 |        |      |
|  56 |                      PX SEND HASH                            | :TQ10030                       |   476K|    64M|  1423 |        |      |
|  57 |                       HASH JOIN RIGHT OUTER                  |                                |   476K|    64M|  1423 |        |      |
|  58 |                        PX RECEIVE                            |                                |  3199 | 41587 |     4 |        |      |
|  59 |                         PX SEND HASH                         | :TQ10027                       |  3199 | 41587 |     4 |        |      |
|  60 |                          PX BLOCK ITERATOR                   |                                |  3199 | 41587 |     4 |        |      |
|  61 |                           TABLE ACCESS FULL                  | ODS_ORA_COA_APPS_FND_USER      |  3199 | 41587 |     4 |        |      |
|  62 |                        BUFFER SORT                           |                                |       |       |       |        |      |
|  63 |                         PX RECEIVE                           |                                |   476K|    58M|  1418 |        |      |
|  64 |                          PX SEND HASH                        | :TQ10013                       |   476K|    58M|  1418 |        |      |
|  65 |                           HASH JOIN RIGHT OUTER              |                                |   476K|    58M|  1418 |        |      |
|  66 |                            VIEW                              |                                |  3506 |   321K|   713 |        |      |
|  67 |                             HASH JOIN OUTER                  |                                |  3506 |   359K|   713 |        |      |
|  68 |                              TABLE ACCESS FULL               | ODS_ORA_COA_QA_PLANS           |     1 |    24 |     3 |        |      |
|  69 |                              TABLE ACCESS FULL               | ODS_ORA_COT_QA_RESULTS         | 73618 |  5823K|   709 |        |      |
|  70 |                            HASH JOIN                         |                                | 14022 |   479K|   703 |        |      |
|  71 |                             TABLE ACCESS FULL                | ODS_ORA_COA_QA_PLANS           |     4 |    96 |     3 |        |      |
|  72 |                             TABLE ACCESS FULL                | ODS_ORA_COT_QA_RESULTS         | 73618 |   790K|   699 |        |      |
|  73 |                    HASH UNIQUE                               |                                |   476K|    64M|  3186 |        |      |
|  74 |                     PX RECEIVE                               |                                |   476K|    64M|  1423 |        |      |
|  75 |                      PX SEND HASH                            | :TQ10031                       |   476K|    64M|  1423 |        |      |
|  76 |                       HASH JOIN RIGHT OUTER                  |                                |   476K|    64M|  1423 |        |      |
|  77 |                        PX RECEIVE                            |                                |  3199 | 41587 |     4 |        |      |
|  78 |                         PX SEND HASH                         | :TQ10028                       |  3199 | 41587 |     4 |        |      |
|  79 |                          PX BLOCK ITERATOR                   |                                |  3199 | 41587 |     4 |        |      |
|  80 |                           TABLE ACCESS FULL                  | ODS_ORA_COA_APPS_FND_USER      |  3199 | 41587 |     4 |        |      |
|  81 |                        BUFFER SORT                           |                                |       |       |       |        |      |
|  82 |                         PX RECEIVE                           |                                |   476K|    58M|  1418 |        |      |
|  83 |                          PX SEND HASH                        | :TQ10014                       |   476K|    58M|  1418 |        |      |
|  84 |                           HASH JOIN RIGHT OUTER              |                                |   476K|    58M|  1418 |        |      |
|  85 |                            VIEW                              |                                |  3506 |   321K|   713 |        |      |
|  86 |                             HASH JOIN OUTER                  |                                |  3506 |   359K|   713 |        |      |
|  87 |                              TABLE ACCESS FULL               | ODS_ORA_COA_QA_PLANS           |     1 |    24 |     3 |        |      |
|  88 |                              TABLE ACCESS FULL               | ODS_ORA_COT_QA_RESULTS         | 73618 |  5823K|   709 |        |      |
|  89 |                            HASH JOIN                         |                                | 14022 |   479K|   703 |        |      |
|  90 |                             TABLE ACCESS FULL                | ODS_ORA_COA_QA_PLANS           |     4 |    96 |     3 |        |      |
|  91 |                             TABLE ACCESS FULL                | ODS_ORA_COT_QA_RESULTS         | 73618 |   790K|   699 |        |      |
|  92 |          PX RECEIVE                                          |                                |  1296K|    26G|   613K|        |      |
|  93 |           PX SEND HASH                                       | :TQ10036                       |  1296K|    26G|   613K|        |      |
|  94 |            HASH JOIN RIGHT OUTER                             |                                |  1296K|    26G|   613K|        |      |
|  95 |             BUFFER SORT                                      |                                |       |       |       |        |      |
|  96 |              PX RECEIVE                                      |                                |     7 |    84 |     3 |        |      |
|  97 |               PX SEND BROADCAST                              | :TQ10017                       |     7 |    84 |     3 |        |      |
|  98 |                VIEW                                          | index$_join$_008               |     7 |    84 |     3 |        |      |
|  99 |                 HASH JOIN                                    |                                |       |       |       |        |      |
| 100 |                  INDEX FAST FULL SCAN                        | EDW_ORA_COA_COMP_SRC_NK        |     7 |    84 |     1 |        |      |
| 101 |                  INDEX FAST FULL SCAN                        | EDW_ORA_COA_COMP_SRC_UK        |     7 |    84 |     1 |        |      |
| 102 |             HASH JOIN RIGHT OUTER                            |                                |  1296K|    26G|   613K|        |      |
| 103 |              BUFFER SORT                                     |                                |       |       |       |        |      |
| 104 |               PX RECEIVE                                     |                                |     3 |    33 |     3 |        |      |
| 105 |                PX SEND BROADCAST                             | :TQ10018                       |     3 |    33 |     3 |        |      |
| 106 |                 VIEW                                         | index$_join$_010               |     3 |    33 |     3 |        |      |
| 107 |                  HASH JOIN                                   |                                |       |       |       |        |      |
| 108 |                   INDEX FAST FULL SCAN                       | EDW_ORA_COA_WARRANTY_NK        |     3 |    33 |     1 |        |      |
| 109 |                   INDEX FAST FULL SCAN                       | EDW_ORA_COA_WARRANTY_UK        |     3 |    33 |     1 |        |      |
| 110 |              VIEW                                            | ODS_RESMED_CCS_REQUESTS_V      |  1296K|    26G|   613K|        |      |
| 111 |               SORT GROUP BY                                  |                                |  1296K|   739M|   613K|        |      |
| 112 |                PX RECEIVE                                    |                                |  1296K|   739M|   594K|        |      |
| 113 |                 PX SEND HASH                                 | :TQ10034                       |  1296K|   739M|   594K|        |      |
| 114 |                  HASH JOIN RIGHT OUTER BUFFERED              |                                |  1296K|   739M|   594K|        |      |
| 115 |                   BUFFER SORT                                |                                |       |       |       |        |      |
| 116 |                    PX RECEIVE                                |                                |   569K|  7228K|  1604 |        |      |
| 117 |                     PX SEND HASH                             | :TQ10016                       |   569K|  7228K|  1604 |        |      |
| 118 |                      TABLE ACCESS FULL                       | ODS_CS_HZ_SR_CONTACT_POINTS    |   569K|  7228K|  1604 |        |      |
| 119 |                   PX RECEIVE                                 |                                |  1294K|   722M|   592K|        |      |
| 120 |                    PX SEND HASH                              | :TQ10032                       |  1294K|   722M|   592K|        |      |
| 121 |                     HASH JOIN RIGHT OUTER BUFFERED           |                                |  1294K|   722M|   592K|        |      |
| 122 |                      BUFFER SORT                             |                                |       |       |       |        |      |
| 123 |                       PX RECEIVE                             |                                |   902K|  9695K|  1116 |        |      |
| 124 |                        PX SEND HASH                          | :TQ10015                       |   902K|  9695K|  1116 |        |      |
| 125 |                         INDEX FAST FULL SCAN                 | ODS_HZ_PARTY_SITE_USES_N1      |   902K|  9695K|  1116 |        |      |
| 126 |                      PX RECEIVE                              |                                |  1294K|   708M|   591K|        |      |
| 127 |                       PX SEND HASH                           | :TQ10029                       |  1294K|   708M|   591K|        |      |
| 128 |                        HASH JOIN RIGHT OUTER BUFFERED        |                                |  1294K|   708M|   591K|        |      |
| 129 |                         BUFFER SORT                          |                                |       |       |       |        |      |
| 130 |                          PX RECEIVE                          |                                |   604K|    22M|  8557 |        |      |
| 131 |                           PX SEND HASH                       | :TQ10012                       |   604K|    22M|  8557 |        |      |
| 132 |                            TABLE ACCESS FULL                 | ODS_CS_INCIDENTS_ALL_TL        |   604K|    22M|  8557 |        |      |
| 133 |                         PX RECEIVE                           |                                |  1216K|   620M|   583K|        |      |
| 134 |                          PX SEND HASH                        | :TQ10026                       |  1216K|   620M|   583K|        |      |
| 135 |                           BUFFER SORT                        |                                |  2048K|    44G|       |        |      |
| 136 |                            NESTED LOOPS OUTER                |                                |  1216K|   620M|   583K|        |      |
| 137 |                             NESTED LOOPS OUTER               |                                |  1216K|   605M|   420K|        |      |
| 138 |                              HASH JOIN RIGHT OUTER           |                                |  1216K|   585M|   291K|        |      |
| 139 |                               BUFFER SORT                    |                                |       |       |       |        |      |
| 140 |                                PX RECEIVE                    |                                |   527K|  8246K|  1360 |        |      |
| 141 |                                 PX SEND HASH                 | :TQ10010                       |   527K|  8246K|  1360 |        |      |
| 142 |                                  TABLE ACCESS FULL           | ODS_CS_INCIDENTS_ALL_B_A       |   527K|  8246K|  1360 |        |      |
| 143 |                               HASH JOIN RIGHT OUTER          |                                |  1216K|   567M|   289K|        |      |
| 144 |                                BUFFER SORT                   |                                |       |       |       |        |      |
| 145 |                                 PX RECEIVE                   |                                |   405K|  7130K| 73458 |        |      |
| 146 |                                  PX SEND HASH                | :TQ10011                       |   405K|  7130K| 73458 |        |      |
| 147 |                                   VIEW                       |                                |   405K|  7130K| 73458 |        |      |
| 148 |                                    HASH UNIQUE               |                                |   405K|    10M| 73458 |        |      |
| 149 |                                     WINDOW SORT              |                                |   405K|    10M| 73458 |        |      |
| 150 |                                      HASH JOIN               |                                |   405K|    10M| 62840 |        |      |
| 151 |                                       TABLE ACCESS FULL      | ODS_CS_ESTIMATE_DETAILS        |   404K|  6320K| 14042 |        |      |
| 152 |                                       TABLE ACCESS FULL      | ODS_ORA_COT_OE_ORDER_HEAD_ALL  |  2974K|    34M| 44515 |        |      |
| 153 |                                PX RECEIVE                    |                                |  1216K|   546M|   216K|        |      |
| 154 |                                 PX SEND HASH                 | :TQ10025                       |  1216K|   546M|   216K|        |      |
| 155 |                                  HASH JOIN RIGHT OUTER       |                                |  1216K|   546M|   216K|        |      |
| 156 |                                   BUFFER SORT                |                                |       |       |       |        |      |
| 157 |                                    PX RECEIVE                |                                |   243K|  1191K|   210 |        |      |
| 158 |                                     PX SEND BROADCAST        | :TQ10000                       |   243K|  1191K|   210 |        |      |
| 159 |                                      INDEX FAST FULL SCAN    | ODS_ORA_COA_HZ_CUST_ACCTS_PK   |   243K|  1191K|   210 |        |      |
| 160 |                                   HASH JOIN RIGHT OUTER      |                                |  1216K|   540M|   216K|        |      |
| 161 |                                    BUFFER SORT               |                                |       |       |       |        |      |
| 162 |                                     PX RECEIVE               |                                | 64661 |  1262K| 28450 |        |      |
| 163 |                                      PX SEND BROADCAST       | :TQ10001                       | 64661 |  1262K| 28450 |        |      |
| 164 |                                       VIEW                   | index$_join$_032               | 64661 |  1262K| 28450 |        |      |
| 165 |                                        HASH JOIN             |                                |       |       |       |        |      |
| 166 |                                         INDEX RANGE SCAN     | ODS_CS_INCIDENTS_AUDIT_B_N1    | 64661 |  1262K|   106K|        |      |
| 167 |                                         INDEX FAST FULL SCAN | ODS_CS_INCIDENTS_AUDIT_B_N2    | 64661 |  1262K| 13308 |        |      |
| 168 |                                    HASH JOIN RIGHT OUTER     |                                |  1216K|   517M|   187K|        |      |
| 169 |                                     BUFFER SORT              |                                |       |       |       |        |      |
| 170 |                                      PX RECEIVE              |                                | 50908 |   994K| 28450 |        |      |
| 171 |                                       PX SEND BROADCAST      | :TQ10002                       | 50908 |   994K| 28450 |        |      |
| 172 |                                        VIEW                  | index$_join$_036               | 50908 |   994K| 28450 |        |      |
| 173 |                                         HASH JOIN            |                                |       |       |       |        |      |
| 174 |                                          INDEX RANGE SCAN    | ODS_CS_INCIDENTS_AUDIT_B_N1    | 50908 |   994K|   106K|        |      |
| 175 |                                          INDEX FAST FULL SCAN| ODS_CS_INCIDENTS_AUDIT_B_N2    | 50908 |   994K| 13308 |        |      |
| 176 |                                     HASH JOIN RIGHT OUTER    |                                |  1216K|   494M|   159K|        |      |
| 177 |                                      BUFFER SORT             |                                |       |       |       |        |      |
| 178 |                                       PX RECEIVE             |                                | 46807 |   914K| 28450 |        |      |
| 179 |                                        PX SEND BROADCAST     | :TQ10003                       | 46807 |   914K| 28450 |        |      |
| 180 |                                         VIEW                 | index$_join$_029               | 46807 |   914K| 28450 |        |      |
| 181 |                                          HASH JOIN           |                                |       |       |       |        |      |
| 182 |                                           INDEX RANGE SCAN   | ODS_CS_INCIDENTS_AUDIT_B_N1    | 46807 |   914K|   106K|        |      |
| 183 |                                           INDEX FAST FULL SCA| ODS_CS_INCIDENTS_AUDIT_B_N2    | 46807 |   914K| 13308 |        |      |
| 184 |                                      HASH JOIN RIGHT OUTER   |                                |  1216K|   470M|   130K|        |      |
| 185 |                                       BUFFER SORT            |                                |       |       |       |        |      |
| 186 |                                        PX RECEIVE            |                                | 46083 |   900K| 28450 |        |      |
| 187 |                                         PX SEND BROADCAST    | :TQ10004                       | 46083 |   900K| 28450 |        |      |
| 188 |                                          VIEW                | index$_join$_033               | 46083 |   900K| 28450 |        |      |
| 189 |                                           HASH JOIN          |                                |       |       |       |        |      |
| 190 |                                            INDEX RANGE SCAN  | ODS_CS_INCIDENTS_AUDIT_B_N1    | 46083 |   900K|   106K|        |      |
| 191 |                                            INDEX FAST FULL SC| ODS_CS_INCIDENTS_AUDIT_B_N2    | 46083 |   900K| 13308 |        |      |
| 192 |                                       HASH JOIN RIGHT OUTER  |                                |  1216K|   447M|   102K|        |      |
| 193 |                                        BUFFER SORT           |                                |       |       |       |        |      |
| 194 |                                         PX RECEIVE           |                                | 27746 |   541K| 28450 |        |      |
| 195 |                                          PX SEND BROADCAST   | :TQ10005                       | 27746 |   541K| 28450 |        |      |
| 196 |                                           VIEW               | index$_join$_034               | 27746 |   541K| 28450 |        |      |
| 197 |                                            HASH JOIN         |                                |       |       |       |        |      |
| 198 |                                             INDEX RANGE SCAN | ODS_CS_INCIDENTS_AUDIT_B_N1    | 27746 |   541K|   106K|        |      |
| 199 |                                             INDEX FAST FULL S| ODS_CS_INCIDENTS_AUDIT_B_N2    | 27746 |   541K| 13308 |        |      |
| 200 |                                        HASH JOIN RIGHT OUTER |                                |  1216K|   424M| 73800 |        |      |
| 201 |                                         BUFFER SORT          |                                |       |       |       |        |      |
| 202 |                                          PX RECEIVE          |                                | 11822 |   230K| 20734 |        |      |
| 203 |                                           PX SEND BROADCAST  | :TQ10006                       | 11822 |   230K| 20734 |        |      |
| 204 |                                            TABLE ACCESS BY IN| ODS_CS_INCIDENTS_AUDIT_B       | 11822 |   230K| 20734 |        |      |
| 205 |                                             INDEX RANGE SCAN | ODS_CS_INCIDENTS_AUDIT_B_N1    | 11822 |       |  9455 |        |      |
| 206 |                                         HASH JOIN RIGHT OUTER|                                |  1216K|   401M| 53065 |        |      |
| 207 |                                          BUFFER SORT         |                                |       |       |       |        |      |
| 208 |                                           PX RECEIVE         |                                |  4825 | 96500 | 14059 |        |      |
| 209 |                                            PX SEND BROADCAST | :TQ10007                       |  4825 | 96500 | 14059 |        |      |
| 210 |                                             TABLE ACCESS BY I| ODS_CS_INCIDENTS_AUDIT_B       |  4825 | 96500 | 14059 |        |      |
| 211 |                                              INDEX RANGE SCAN| ODS_CS_INCIDENTS_AUDIT_B_N1    |  4825 |       |  9455 |        |      |
| 212 |                                          HASH JOIN RIGHT OUTE|                                |  1216K|   378M| 39006 |        |      |
| 213 |                                           BUFFER SORT        |                                |       |       |       |        |      |
| 214 |                                            PX RECEIVE        |                                |  4584 | 91680 | 13829 |        |      |
| 215 |                                             PX SEND BROADCAST| :TQ10008                       |  4584 | 91680 | 13829 |        |      |
| 216 |                                              TABLE ACCESS BY | ODS_CS_INCIDENTS_AUDIT_B       |  4584 | 91680 | 13829 |        |      |
| 217 |                                               INDEX RANGE SCA| ODS_CS_INCIDENTS_AUDIT_B_N1    |  4584 |       |  9455 |        |      |
| 218 |                                           HASH JOIN RIGHT OUT|                                |  1216K|   354M| 25177 |        |      |
| 219 |                                            PX RECEIVE        |                                |  3199 | 41587 |     4 |        |      |
| 220 |                                             PX SEND HASH     | :TQ10024                       |  3199 | 41587 |     4 |        |      |
| 221 |                                              PX BLOCK ITERATO|                                |  3199 | 41587 |     4 |        |      |
| 222 |                                               TABLE ACCESS FU| ODS_ORA_COA_APPS_FND_USER      |  3199 | 41587 |     4 |        |      |
| 223 |                                            BUFFER SORT       |                                |       |       |       |        |      |
| 224 |                                             PX RECEIVE       |                                |  1216K|   339M| 25172 |        |      |
| 225 |                                              PX SEND HASH    | :TQ10009                       |  1216K|   339M| 25172 |        |      |
| 226 |                                               HASH JOIN RIGHT|                                |  1216K|   339M| 25172 |        |      |
| 227 |                                                TABLE ACCESS F| ODS_CS_SR_OWNERS_V             |  2633 | 97421 |     8 |        |      |
| 228 |                                                HASH JOIN RIGH|                                |  1216K|   296M| 25159 |        |      |
| 229 |                                                 TABLE ACCESS | ODS_CS_INCIDENTS_AUDIT_B       |  1206 | 24120 | 10606 |        |      |
| 230 |                                                  INDEX RANGE | ODS_CS_INCIDENTS_AUDIT_B_N1    |  1206 |       |  9455 |        |      |
| 231 |                                                 HASH JOIN RIG|                                |  1216K|   273M| 14548 |        |      |
| 232 |                                                  INDEX RANGE | PK_FND_LKP_VAL                 |   102 |  3060 |     8 |        |      |
| 233 |                                                  HASH JOIN RI|                                |  1216K|   238M| 14535 |        |      |
| 234 |                                                   INDEX RANGE| PK_FND_LKP_VAL                 |   102 |  3060 |     8 |        |      |
| 235 |                                                   HASH JOIN  |                                |  1216K|   204M| 14522 |        |      |
| 236 |                                                    VIEW      | ODS_ORA_COA_INCIDENT_STATUS_V  |    84 |   336 |     1 |        |      |
| 237 |                                                     NESTED LO|                                |    84 |   924 |     1 |        |      |
| 238 |                                                      INDEX FU| ODS_CS_INCIDENT_STATUSES_B_U1  |    84 |   336 |     1 |        |      |
| 239 |                                                      INDEX UN| ODS_CS_INCIDENT_STATUSES_TL_U1 |     1 |     7 |     0 |        |      |
| 240 |                                                    HASH JOIN |                                |  1216K|   199M| 14515 |        |      |
| 241 |                                                     VIEW     | ODS_ORA_COA_INCIDENT_CAUSE_V   |    81 |   324 |     1 |        |      |
| 242 |                                                      NESTED L|                                |    81 |   891 |     1 |        |      |
| 243 |                                                       INDEX F| ODS_CS_INCIDENT_URGENCIES_B_U1 |    81 |   324 |     1 |        |      |
| 244 |                                                       INDEX U| ODS_CS_INCIDENT_URGNCS_TL_U1   |     1 |     7 |     0 |        |      |
| 245 |                                                     HASH JOIN|                                |  1216K|   194M| 14509 |        |      |
| 246 |                                                      VIEW    | ODS_ORA_COA_INCIDENT_TYPES_V   |    31 |   124 |     1 |        |      |
| 247 |                                                       NESTED |                                |    31 |   341 |     1 |        |      |
| 248 |                                                        INDEX | ODS_CS_INCIDENT_TYPES_B_U1     |    31 |   124 |     1 |        |      |
| 249 |                                                        INDEX | ODS_CS_INCIDENT_TYPES_TL_U1    |     1 |     7 |     0 |        |      |
| 250 |                                                      HASH JOI|                                |  1216K|   190M| 14503 |        |      |
| 251 |                                                       INDEX F| ODS_CS_INCIDENT_SEVERITY_TL_U1 |    12 |    72 |     1 |        |      |
| 252 |                                                       HASH JO|                                |  1163K|   175M| 14497 |        |      |
| 253 |                                                        VIEW  | DIM_GLB_OPERATING_UNITS_V      |    10 |   130 |    12 |        |      |
| 254 |                                                         HASH |                                |    10 |  3910 |    12 |        |      |
| 255 |                                                          VIEW| DIM_GLB_ORGS_V                 |    10 |  3910 |    11 |        |      |
| 256 |                                                           SOR|                                |    10 |   740 |    11 |        |      |
| 257 |                                                            UN|                                |       |       |       |        |      |
| 258 |                                                             R|                                |       |       |       |  SPROJ | R->S |
| 259 |                                                             T| NONORACLE_ORGANIZATION         |    10 |   740 |    10 |        |      |
| 260 |                                                        TABLE | ODS_CS_INCIDENTS_ALL_B         |   581K|    80M| 14480 |        |      |
| 261 |                              TABLE ACCESS BY INDEX ROWID     | ODS_CSI_ITEM_INSTANCES         |     1 |    17 |     2 |        |      |
| 262 |                               INDEX UNIQUE SCAN              | ODS_CSI_ITEM_INSTANCES_U1      |     1 |       |     1 |        |      |
| 263 |                             TABLE ACCESS BY INDEX ROWID      | ODS_ORA_COT_OE_ORDER_LINES_ALL |     1 |    13 |     2 |        |      |
| 264 |                              INDEX UNIQUE SCAN               | ODS_ORA_COT_OE_ODER_LIN_ALL_PK |     1 |       |     1 |        |      |
-----------------------------------------------------------------------------------------------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
 258 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A3"."SET_OF_BOOKS_ID","A3"."OPERATING_UNIT","A2"."NAME","A3"."ORGANIZATION_ID","A3"."
         ORGANIZATION_NAME","A3"."LEGAL_ENTITY","A1"."NAME",CASE "A3"."OPERATING_UNIT" WHEN 122 THEN 'Show `Non-Oracle` Entities' ELSE 'Exclude 
         `Non-Oracle` Entities' END ,"A2"."DATE_FROM","A2"."DATE_TO" FROM "APPS"."ORG_ORGANIZATION_DEFINITIONS" 
         "A3","APPS"."HR_ORGANIZATION_UNITS" "A2","APPS"."HR_ORGANIZATION_UNITS" "A1" WHERE "A3"."OPERATING_UNIT"="A2"."ORGANIZATION_ID" AND 
         "A3"."LEGAL_ENTITY"="A1"."ORGANIZATION_ID" (accessing 'SPROJ' )
 
 
Note
-----
   - 'PLAN_TABLE' is old version





Second



PLAN_TABLE_OUTPUT

 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                               | Name                           | Rows  | Bytes | Cost  | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                        |                                |  1304K|    28G|  2581K|        |      |
|   1 |  HASH JOIN RIGHT OUTER                                  |                                |  1304K|    28G|  2581K|        |      |
|   2 |   VIEW                                                  | index$_join$_005               |   154 |  1694 |     3 |        |      |
|   3 |    HASH JOIN                                            |                                |       |       |       |        |      |
|   4 |     INDEX FAST FULL SCAN                                | EDW_ORA_COA_REMEDY_CODE_PK     |   154 |  1694 |     1 |        |      |
|   5 |     INDEX FAST FULL SCAN                                | EDW_ORA_COA_REMEDY_CODE_UK     |   154 |  1694 |     1 |        |      |
|   6 |   HASH JOIN RIGHT OUTER                                 |                                |  1304K|    28G|  2581K|        |      |
|   7 |    VIEW                                                 | index$_join$_004               |    79 |  1027 |     3 |        |      |
|   8 |     HASH JOIN                                           |                                |       |       |       |        |      |
|   9 |      INDEX FAST FULL SCAN                               | EDW_ORA_COA_PROBLEM_CODE_PK    |    79 |  1027 |     1 |        |      |
|  10 |      INDEX FAST FULL SCAN                               | EDW_ORA_COA_PROBLEM_CODE_UK    |    79 |  1027 |     1 |        |      |
|  11 |    HASH JOIN RIGHT OUTER                                |                                |  1304K|    28G|  2581K|        |      |
|  12 |     VIEW                                                | index$_join$_009               |     7 |   126 |     3 |        |      |
|  13 |      HASH JOIN                                          |                                |       |       |       |        |      |
|  14 |       INDEX FAST FULL SCAN                              | EDW_ORA_COA_FAULT_OCCUR_NK     |     7 |   126 |     1 |        |      |
|  15 |       INDEX FAST FULL SCAN                              | EDW_ORA_COA_FAULT_OCCUR_UK     |     7 |   126 |     1 |        |      |
|  16 |     HASH JOIN RIGHT OUTER                               |                                |  1304K|    28G|  2581K|        |      |
|  17 |      VIEW                                               | index$_join$_007               |     7 |   112 |     3 |        |      |
|  18 |       HASH JOIN                                         |                                |       |       |       |        |      |
|  19 |        INDEX FAST FULL SCAN                             | EDW_ORA_COA_ESC_TO_QA_NK       |     7 |   112 |     1 |        |      |
|  20 |        INDEX FAST FULL SCAN                             | EDW_ORA_COA_ESC_TO_QA_UK       |     7 |   112 |     1 |        |      |
|  21 |      HASH JOIN RIGHT OUTER                              |                                |  1304K|    28G|  2581K|        |      |
|  22 |       VIEW                                              | index$_join$_006               |     5 |    80 |     3 |        |      |
|  23 |        HASH JOIN                                        |                                |       |       |       |        |      |
|  24 |         INDEX FAST FULL SCAN                            | EDW_EDW_ORA_COA_DEATH_INJRY_NK |     5 |    80 |     1 |        |      |
|  25 |         INDEX FAST FULL SCAN                            | EDW_EDW_ORA_COA_DEATH_INJRY_UK |     5 |    80 |     1 |        |      |
|  26 |       HASH JOIN RIGHT OUTER                             |                                |  1304K|    28G|  2581K|        |      |
|  27 |        VIEW                                             | ODS_RESMED_CQA_COMPLAINT_V     | 22472 |    24M| 13187 |        |      |
|  28 |         VIEW                                            |                                | 22472 |    25M| 13187 |        |      |
|  29 |          WINDOW SORT PUSHED RANK                        |                                | 22472 |    24M| 13187 |        |      |
|  30 |           VIEW                                          |                                | 22472 |    24M|  4022 |        |      |
|  31 |            UNION-ALL                                    |                                |       |       |       |        |      |
|  32 |             HASH UNIQUE                                 |                                | 11236 |  1470K|  2011 |        |      |
|  33 |              HASH JOIN RIGHT OUTER                      |                                | 11236 |  1470K|  1444 |        |      |
|  34 |               TABLE ACCESS FULL                         | ODS_ORA_COA_APPS_FND_USER      |  4486 | 49346 |    74 |        |      |
|  35 |               HASH JOIN RIGHT OUTER                     |                                | 11236 |  1349K|  1369 |        |      |
|  36 |                VIEW                                     |                                |  2809 |   249K|   689 |        |      |
|  37 |                 HASH JOIN OUTER                         |                                |  2809 |   274K|   689 |        |      |
|  38 |                  TABLE ACCESS FULL                      | ODS_ORA_COA_QA_PLANS           |     1 |    23 |     4 |        |      |
|  39 |                  TABLE ACCESS FULL                      | ODS_ORA_COT_QA_RESULTS         | 75845 |  5703K|   684 |        |      |
|  40 |                HASH JOIN                                |                                | 11236 |   351K|   679 |        |      |
|  41 |                 TABLE ACCESS FULL                       | ODS_ORA_COA_QA_PLANS           |     4 |    92 |     4 |        |      |
|  42 |                 TABLE ACCESS FULL                       | ODS_ORA_COT_QA_RESULTS         | 75845 |   666K|   674 |        |      |
|  43 |             HASH UNIQUE                                 |                                | 11236 |  1470K|  2011 |        |      |
|  44 |              HASH JOIN RIGHT OUTER                      |                                | 11236 |  1470K|  1444 |        |      |
|  45 |               TABLE ACCESS FULL                         | ODS_ORA_COA_APPS_FND_USER      |  4486 | 49346 |    74 |        |      |
|  46 |               HASH JOIN RIGHT OUTER                     |                                | 11236 |  1349K|  1369 |        |      |
|  47 |                VIEW                                     |                                |  2809 |   249K|   689 |        |      |
|  48 |                 HASH JOIN OUTER                         |                                |  2809 |   274K|   689 |        |      |
|  49 |                  TABLE ACCESS FULL                      | ODS_ORA_COA_QA_PLANS           |     1 |    23 |     4 |        |      |
|  50 |                  TABLE ACCESS FULL                      | ODS_ORA_COT_QA_RESULTS         | 75845 |  5703K|   684 |        |      |
|  51 |                HASH JOIN                                |                                | 11236 |   351K|   679 |        |      |
|  52 |                 TABLE ACCESS FULL                       | ODS_ORA_COA_QA_PLANS           |     4 |    92 |     4 |        |      |
|  53 |                 TABLE ACCESS FULL                       | ODS_ORA_COT_QA_RESULTS         | 75845 |   666K|   674 |        |      |
|  54 |        HASH JOIN RIGHT OUTER                            |                                |  1304K|    26G|  1091K|        |      |
|  55 |         VIEW                                            | index$_join$_008               |     7 |    98 |     3 |        |      |
|  56 |          HASH JOIN                                      |                                |       |       |       |        |      |
|  57 |           INDEX FAST FULL SCAN                          | EDW_ORA_COA_COMP_SRC_NK        |     7 |    98 |     1 |        |      |
|  58 |           INDEX FAST FULL SCAN                          | EDW_ORA_COA_COMP_SRC_UK        |     7 |    98 |     1 |        |      |
|  59 |         HASH JOIN RIGHT OUTER                           |                                |  1304K|    26G|  1091K|        |      |
|  60 |          VIEW                                           | index$_join$_010               |     3 |    33 |     3 |        |      |
|  61 |           HASH JOIN                                     |                                |       |       |       |        |      |
|  62 |            INDEX FAST FULL SCAN                         | EDW_ORA_COA_WARRANTY_NK        |     3 |    33 |     1 |        |      |
|  63 |            INDEX FAST FULL SCAN                         | EDW_ORA_COA_WARRANTY_UK        |     3 |    33 |     1 |        |      |
|  64 |          VIEW                                           | ODS_RESMED_CCS_REQUESTS_V      |  1304K|    26G|  1091K|        |      |
|  65 |           SORT GROUP BY                                 |                                |  1304K|   665M|  1091K|        |      |
|  66 |            HASH JOIN                                    |                                |  1304K|   665M|   842K|        |      |
|  67 |             VIEW                                        | DIM_GLB_OPERATING_UNITS_V      |     9 |   117 |    13 |        |      |
|  68 |              HASH UNIQUE                                |                                |     9 |  3519 |    13 |        |      |
|  69 |               VIEW                                      | DIM_GLB_ORGS_V                 |     9 |  3519 |    12 |        |      |
|  70 |                SORT UNIQUE                              |                                |     9 |   648 |    12 |        |      |
|  71 |                 UNION-ALL                               |                                |       |       |       |        |      |
|  72 |                  REMOTE                                 |                                |       |       |       |    UAT | R->S |
|  73 |                  TABLE ACCESS FULL                      | NONORACLE_ORGANIZATION         |     9 |   648 |    11 |        |      |
|  74 |             HASH JOIN RIGHT OUTER                       |                                |   724K|   360M|   842K|        |      |
|  75 |              INDEX RANGE SCAN                           | PK_FND_LKP_VAL                 |    12 |   324 |     3 |        |      |
|  76 |              HASH JOIN RIGHT OUTER                      |                                |   724K|   342M|   842K|        |      |
|  77 |               INDEX RANGE SCAN                          | PK_FND_LKP_VAL                 |    12 |   324 |     3 |        |      |
|  78 |               HASH JOIN RIGHT OUTER                     |                                |   724K|   323M|   842K|        |      |
|  79 |                VIEW                                     |                                |   408K|  7176K| 74258 |        |      |
|  80 |                 HASH UNIQUE                             |                                |   408K|    10M| 74258 |        |      |
|  81 |                  WINDOW SORT                            |                                |   408K|    10M| 74258 |        |      |
|  82 |                   HASH JOIN                             |                                |   408K|    10M| 64092 |        |      |
|  83 |                    TABLE ACCESS FULL                    | ODS_CS_ESTIMATE_DETAILS        |   408K|  6379K| 14032 |        |      |
|  84 |                    TABLE ACCESS FULL                    | ODS_ORA_COT_OE_ORDER_HEAD_ALL  |  3136K|    29M| 45894 |        |      |
|  85 |                HASH JOIN RIGHT OUTER                    |                                |   724K|   310M|   750K|        |      |
|  86 |                 TABLE ACCESS FULL                       | ODS_CS_HZ_SR_CONTACT_POINTS    |   304K|  2969K|  1572 |        |      |
|  87 |                 HASH JOIN RIGHT OUTER                   |                                |   724K|   304M|   731K|        |      |
|  88 |                  VIEW                                   | ODS_ORA_COA_INCIDENT_CAUSE_V   |    81 |   243 |     1 |        |      |
|  89 |                   NESTED LOOPS                          |                                |    81 |   648 |     1 |        |      |
|  90 |                    INDEX FULL SCAN                      | ODS_CS_INCIDENT_URGENCIES_B_U1 |    81 |   243 |     1 |        |      |
|  91 |                    INDEX UNIQUE SCAN                    | ODS_CS_INCIDENT_URGNCS_TL_U1   |     1 |     5 |     0 |        |      |
|  92 |                  HASH JOIN RIGHT OUTER                  |                                |   660K|   275M|   731K|        |      |
|  93 |                   TABLE ACCESS FULL                     | ODS_CS_INCIDENTS_ALL_B_A       |   368K|  4675K|  1350 |        |      |
|  94 |                   HASH JOIN RIGHT OUTER                 |                                |   660K|   267M|   715K|        |      |
|  95 |                    TABLE ACCESS BY INDEX ROWID          | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  96 |                     INDEX RANGE SCAN                    | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
|  97 |                    HASH JOIN RIGHT OUTER                |                                |   660K|   257M|   694K|        |      |
|  98 |                     TABLE ACCESS BY INDEX ROWID         | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
|  99 |                      INDEX RANGE SCAN                   | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
| 100 |                     HASH JOIN RIGHT OUTER               |                                |   660K|   247M|   673K|        |      |
| 101 |                      TABLE ACCESS BY INDEX ROWID        | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
| 102 |                       INDEX RANGE SCAN                  | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
| 103 |                      HASH JOIN RIGHT OUTER              |                                |   660K|   236M|   652K|        |      |
| 104 |                       TABLE ACCESS BY INDEX ROWID       | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
| 105 |                        INDEX RANGE SCAN                 | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
| 106 |                       HASH JOIN RIGHT OUTER             |                                |   660K|   226M|   631K|        |      |
| 107 |                        TABLE ACCESS BY INDEX ROWID      | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
| 108 |                         INDEX RANGE SCAN                | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
| 109 |                        HASH JOIN RIGHT OUTER            |                                |   660K|   216M|   610K|        |      |
| 110 |                         TABLE ACCESS BY INDEX ROWID     | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
| 111 |                          INDEX RANGE SCAN               | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
| 112 |                         HASH JOIN RIGHT OUTER           |                                |   660K|   206M|   589K|        |      |
| 113 |                          TABLE ACCESS BY INDEX ROWID    | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
| 114 |                           INDEX RANGE SCAN              | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
| 115 |                          HASH JOIN RIGHT OUTER          |                                |   660K|   196M|   568K|        |      |
| 116 |                           TABLE ACCESS BY INDEX ROWID   | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
| 117 |                            INDEX RANGE SCAN             | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
| 118 |                           HASH JOIN RIGHT OUTER         |                                |   660K|   186M|   547K|        |      |
| 119 |                            TABLE ACCESS BY INDEX ROWID  | ODS_CS_INCIDENTS_AUDIT_B       | 13759 |   214K| 20903 |        |      |
| 120 |                             INDEX RANGE SCAN            | ODS_CS_INCIDENTS_AUDIT_B_N1    | 13759 |       |  6144 |        |      |
| 121 |                            HASH JOIN RIGHT OUTER        |                                |   660K|   176M|   527K|        |      |
| 122 |                             TABLE ACCESS FULL           | ODS_CS_SR_OWNERS_V             |  2651 | 90134 |     8 |        |      |
| 123 |                             HASH JOIN RIGHT OUTER       |                                |   660K|   155M|   527K|        |      |
| 124 |                              TABLE ACCESS FULL          | ODS_ORA_COA_APPS_FND_USER      |  4486 | 49346 |    74 |        |      |
| 125 |                              HASH JOIN RIGHT OUTER      |                                |   660K|   148M|   527K|        |      |
| 126 |                               INDEX FAST FULL SCAN      | ODS_ORA_COA_HZ_CUST_ACCTS_PK   |   264K|  1034K|   261 |        |      |
| 127 |                               HASH JOIN RIGHT OUTER     |                                |   660K|   145M|   518K|        |      |
| 128 |                                INDEX FULL SCAN          | ODS_CS_INCIDENT_SEVERITY_TL_U1 |    12 |    48 |     1 |        |      |
| 129 |                                HASH JOIN OUTER          |                                |   605K|   131M|   518K|        |      |
| 130 |                                 HASH JOIN OUTER         |                                |   605K|   124M|   277K|        |      |
| 131 |                                  HASH JOIN RIGHT OUTER  |                                |   605K|   114M| 36372 |        |      |
| 132 |                                   TABLE ACCESS FULL     | ODS_CS_INCIDENTS_ALL_TL        |   605K|    22M|  8523 |        |      |
| 133 |                                   HASH JOIN RIGHT OUTER |                                |   581K|    88M| 21132 |        |      |
| 134 |                                    INDEX FAST FULL SCAN | ODS_HZ_PARTY_SITE_USES_N1      |   930K|  7272K|  1110 |        |      |
| 135 |                                    HASH JOIN            |                                |   581K|    83M| 14175 |        |      |
| 136 |                                     VIEW                | ODS_ORA_COA_INCIDENT_STATUS_V  |    81 |   243 |     1 |        |      |
| 137 |                                      NESTED LOOPS       |                                |    81 |   648 |     1 |        |      |
| 138 |                                       INDEX FULL SCAN   | ODS_CS_INCIDENT_STATUSES_B_U1  |    81 |   243 |     1 |        |      |
| 139 |                                       INDEX UNIQUE SCAN | ODS_CS_INCIDENT_STATUSES_TL_U1 |     1 |     5 |     0 |        |      |
| 140 |                                     HASH JOIN           |                                |   581K|    82M| 14171 |        |      |
| 141 |                                      VIEW               | ODS_ORA_COA_INCIDENT_TYPES_V   |    31 |    93 |     1 |        |      |
| 142 |                                       NESTED LOOPS      |                                |    31 |   248 |     1 |        |      |
| 143 |                                        INDEX FULL SCAN  | ODS_CS_INCIDENT_TYPES_B_U1     |    31 |    93 |     1 |        |      |
| 144 |                                        INDEX UNIQUE SCAN| ODS_CS_INCIDENT_TYPES_TL_U1    |     1 |     5 |     0 |        |      |
| 145 |                                      TABLE ACCESS FULL  | ODS_CS_INCIDENTS_ALL_B         |   581K|    80M| 14168 |        |      |
| 146 |                                  TABLE ACCESS FULL      | ODS_CSI_ITEM_INSTANCES         |    18M|   305M|   205K|        |      |
| 147 |                                 TABLE ACCESS FULL       | ODS_ORA_COT_OE_ORDER_LINES_ALL |  8092K|    92M|   224K|        |      |
------------------------------------------------------------------------------------------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
  72 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A3"."SET_OF_BOOKS_ID","A3"."OPERATING_UNIT","A2"."NAME","A3"."ORGANIZATION_ID","
        A3"."ORGANIZATION_NAME","A3"."LEGAL_ENTITY","A1"."NAME",'Exclude `Non-Oracle` Entities',"A2"."DATE_FROM","A2"."DATE_TO" FROM 
        "APPS"."ORG_ORGANIZATION_DEFINITIONS" "A3","APPS"."HR_ORGANIZATION_UNITS" "A2","APPS"."HR_ORGANIZATION_UNITS" "A1" WHERE 
        "A3"."OPERATING_UNIT"="A2"."ORGANIZATION_ID" AND "A3"."LEGAL_ENTITY"="A1"."ORGANIZATION_ID" (accessing 'UAT' )
 
 
Note
-----
   - 'PLAN_TABLE' is old version

[Updated on: Thu, 13 August 2009 01:29]

Report message to a moderator

Previous Topic: Skipping Records in a cursor loop
Next Topic: do i need to pass values in single quotes in IN clause while passing to procedure
Goto Forum:
  


Current Time: Sat Dec 03 14:25:34 CST 2016

Total time taken to generate the page: 0.08788 seconds