Home » RDBMS Server » Performance Tuning » Whast is connect$_by$_pump$ in explain plan (Oracle Database 11g Release 11.2.0.3.0 - 64bit )
Whast is connect$_by$_pump$ in explain plan [message #653200] Tue, 28 June 2016 23:14 Go to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Hi,

My Oracle version is for local

Local:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Prod:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

and for production

I have one query running 8 minute straight on production but took 7 seconds on local.
the two plans from local and production is below in same sequence

Plan hash value: 3312192782
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                      |                             |     1 |  2357 |    70   (6)| 00:00:01 |
|*  1 |  HASH JOIN SEMI                                       |                             |     1 |  2357 |    70   (6)| 00:00:01 |
|*  2 |   HASH JOIN OUTER                                     |                             |     1 |  2155 |    43   (3)| 00:00:01 |
|*  3 |    HASH JOIN                                          |                             |     1 |   127 |    41   (3)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL                                 | MS_ISM_STAGE_ACTION_MAPPING |     1 |    29 |     3   (0)| 00:00:01 |
|   5 |     VIEW                                              |                             |   570 | 55860 |    37   (0)| 00:00:01 |
|   6 |      MERGE JOIN CARTESIAN                             |                             |   570 | 15960 |    37   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL                               | SI_LOCALES                  |    15 |    90 |     3   (0)| 00:00:01 |
|   8 |       BUFFER SORT                                     |                             |    38 |   836 |    34   (0)| 00:00:01 |
|   9 |        TABLE ACCESS FULL                              | MS_ISM_ACTIONS              |    38 |   836 |     2   (0)| 00:00:01 |
|  10 |    TABLE ACCESS FULL                                  | MS_ISM_ACTIONS_TL           |     1 |  2028 |     2   (0)| 00:00:01 |
|  11 |   VIEW                                                | VW_NSO_2                    |     1 |   202 |    27  (12)| 00:00:01 |
|  12 |    MERGE JOIN CARTESIAN                               |                             |     1 |  2109 |    27  (12)| 00:00:01 |
|  13 |     NESTED LOOPS                                      |                             |       |       |            |          |
|  14 |      NESTED LOOPS                                     |                             |     1 |  2109 |    16  (13)| 00:00:01 |
|  15 |       NESTED LOOPS                                    |                             |     2 |  4154 |    15  (14)| 00:00:01 |
|  16 |        NESTED LOOPS                                   |                             |     1 |  2067 |    14  (15)| 00:00:01 |
|  17 |         NESTED LOOPS                                  |                             |     1 |  2062 |    13  (16)| 00:00:01 |
|  18 |          NESTED LOOPS                                 |                             |     1 |  2052 |    12  (17)| 00:00:01 |
|  19 |           NESTED LOOPS                                |                             |     1 |  2037 |    11  (19)| 00:00:01 |
|  20 |            VIEW                                       | SI_FLOW_DOWN_ORG_V          |     1 |  2015 |    11  (28)| 00:00:01 |
|  21 |             TEMP TABLE TRANSFORMATION                 |                             |       |       |            |          |
|  22 |              LOAD AS SELECT                           | SYS_TEMP_0FD9D6873_D29F1E9  |       |       |            |          |
|* 23 |               FILTER                                  |                             |       |       |            |          |
|* 24 |                CONNECT BY NO FILTERING WITH START-WITH|                             |       |       |            |          |
|  25 |                 TABLE ACCESS FULL                     | SI_ORG_ENTITIES_T           |    39 |  1248 |     4   (0)| 00:00:01 |
|* 26 |              HASH JOIN                                |                             |     1 |  2067 |     6  (34)| 00:00:01 |
|  27 |               VIEW                                    | VW_SQ_1                     |    39 |  1014 |     3  (34)| 00:00:01 |
|  28 |                HASH GROUP BY                          |                             |    39 |   702 |     3  (34)| 00:00:01 |
|  29 |                 VIEW                                  |                             |    39 |   702 |     2   (0)| 00:00:01 |
|  30 |                  TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6873_D29F1E9  |    39 |  9750 |     2   (0)| 00:00:01 |
|  31 |               VIEW                                    |                             |    39 | 79599 |     2   (0)| 00:00:01 |
|  32 |                TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6873_D29F1E9  |    39 |  9750 |     2   (0)| 00:00:01 |
|* 33 |            TABLE ACCESS BY INDEX ROWID                | SI_USERS_T                  |     1 |    22 |     1   (0)| 00:00:01 |
|* 34 |             INDEX RANGE SCAN                          | SI_USERS_T_N2               |     1 |       |     1   (0)| 00:00:01 |
|* 35 |           INDEX FULL SCAN                             | SI_ORG_ROLES_N3             |     2 |    30 |     1   (0)| 00:00:01 |
|* 36 |          INDEX RANGE SCAN                             | SI_USER_ORG_ROLES_N2        |     1 |    10 |     1   (0)| 00:00:01 |
|* 37 |         INDEX RANGE SCAN                              | SI_ROLES_N2                 |     1 |     5 |     1   (0)| 00:00:01 |
|* 38 |        INDEX RANGE SCAN                               | SI_ROLE_ACTIVITIES_N2       |    14 |   140 |     1   (0)| 00:00:01 |
|* 39 |       INDEX RANGE SCAN                                | SI_ACTIVITIES_N1            |     1 |       |     1   (0)| 00:00:01 |
|* 40 |      TABLE ACCESS BY INDEX ROWID                      | SI_ACTIVITIES_T             |     1 |    32 |     1   (0)| 00:00:01 |
|  41 |     BUFFER SORT                                       |                             |     1 |       |    26  (12)| 00:00:01 |
|  42 |      VIEW                                             |                             |     1 |       |    11  (10)| 00:00:01 |
|  43 |       SORT AGGREGATE                                  |                             |     1 |    83 |            |          |
|* 44 |        HASH JOIN                                      |                             |     1 |    83 |    11  (10)| 00:00:01 |
|* 45 |         HASH JOIN                                     |                             |     1 |    66 |     7  (15)| 00:00:01 |
|* 46 |          TABLE ACCESS FULL                            | MS_QS_PARAMETER_TYPES       |     1 |    34 |     3   (0)| 00:00:01 |
|* 47 |          TABLE ACCESS FULL                            | MS_QS_PARAMETER_NAMES       |     2 |    64 |     3   (0)| 00:00:01 |
|* 48 |         TABLE ACCESS FULL                             | MS_QS_PARAMETER_VALUES      |   175 |  2975 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ACTIVITY_NAME"=TRIM("A"."ROLE_ACTIVITY"))
   2 - access("T"."ACTION_ID"="TL"."ACTION_ID"(+) AND "T"."LOCALE_ID"="TL"."LOCALE_ID"(+))
   3 - access("A"."ACTION_ID"="T"."ACTION_ID")
   4 - filter("A"."CURRENT_STAGE"=20 AND "A"."PREVIOUS_STAGE"=4 AND 
              "MS_ISM_HELPER"."CHECK_ACTIONS"(TO_CHAR("A"."ACTION_ID"),'4','20',NULL,'ISSUE - 100311','ACTION - 
              100399','9r75','MS_ISM_ACTION')=1 AND "MS_ISM_HELPER"."CONFIG_PARAMETERS_ACTFILTER"(TO_CHAR("A"."ACTION_ID"),'ACTION - 
              100399')=1)
   7 - filter("ENABLED_FLAG"='Y')
  23 - filter(NVL("END_DATE",SYSDATE@!)>=SYSDATE@!)
  24 - access("PARENT_ORG_ENTITY_ID"=PRIOR "ORG_ENTITY_ID")
       filter("ORG_ENTITY_ID" IS NOT NULL)
  26 - access("LVL"="MAX(LVL)" AND "ITEM_0"="O1"."ORG_ENTITY_ID")
  33 - filter(NVL("END_DATE",SYSDATE@!)>=SYSDATE@!)
  34 - access("H"."ENTERPRISE_ID"="ENTERPRISE_ID" AND "USER_NAME"='9r75')
  35 - filter("H"."PATH" LIKE '%:'||TO_CHAR("G"."ORG_ENTITY_ID")||':%')
  36 - access("G"."ORG_ROLE_ID"="E"."ORG_ROLE_ID" AND "USER_ID"="E"."USER_ID")
  37 - access("R"."ROLE_ID"="G"."ROLE_ID")
  38 - access("A"."ROLE_ID"="R"."ROLE_ID")
  39 - access("A"."ACTIVITY_ID"="C"."ACTIVITY_ID")
  40 - filter("C"."ACTIVITY_NAME" LIKE 'ISM%')
  44 - access("PNAME"."PARAMETER_ID"="PVALUE"."PARAMETER_ID" AND "PTYPE"."PARAMETER_TYPE_ID"="PVALUE"."PARAMETER_TYPE_ID")
  45 - access("PTYPE"."PARAMETER_TYPE_ID"="PNAME"."PARAMETER_TYPE_ID")
  46 - filter(UPPER("PTYPE"."PARAMETER_TYPE")='MS APPS USER NAME DISPLAY FORMAT')
  47 - filter(UPPER("PNAME"."PARAMETER_NAME")='DISPLAY_FORMAT')
  48 - filter("PVALUE"."ORG_ENTITY_ID"=1)

Production Plan

Plan hash value: 1995170138
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                             |     1 |  2357 |    39  (13)| 00:00:01 |
|*  1 |  HASH JOIN SEMI                             |                             |     1 |  2357 |    39  (13)| 00:00:01 |
|*  2 |   HASH JOIN OUTER                           |                             |     1 |  2155 |    12   (9)| 00:00:01 |
|*  3 |    HASH JOIN                                |                             |     1 |   127 |    10  (10)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL                       | MS_ISM_STAGE_ACTION_MAPPING |     1 |    29 |     3   (0)| 00:00:01 |
|   5 |     VIEW                                    |                             |    37 |  3626 |     6   (0)| 00:00:01 |
|   6 |      MERGE JOIN CARTESIAN                   |                             |    37 |  1036 |     6   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL                     | SI_LOCALES                  |     1 |     6 |     3   (0)| 00:00:01 |
|   8 |       BUFFER SORT                           |                             |    37 |   814 |     3   (0)| 00:00:01 |
|   9 |        TABLE ACCESS FULL                    | MS_ISM_ACTIONS              |    37 |   814 |     3   (0)| 00:00:01 |
|  10 |    TABLE ACCESS FULL                        | MS_ISM_ACTIONS_TL           |     1 |  2028 |     2   (0)| 00:00:01 |
|  11 |   VIEW                                      | VW_NSO_2                    |     1 |   202 |    27  (15)| 00:00:01 |
|  12 |    MERGE JOIN CARTESIAN                     |                             |     1 |  2105 |    27  (15)| 00:00:01 |
|  13 |     NESTED LOOPS                            |                             |       |       |            |          |
|  14 |      NESTED LOOPS                           |                             |     1 |  2105 |    17  (18)| 00:00:01 |
|  15 |       NESTED LOOPS                          |                             |     1 |  2075 |    16  (19)| 00:00:01 |
|  16 |        NESTED LOOPS                         |                             |     1 |  2065 |    15  (20)| 00:00:01 |
|  17 |         NESTED LOOPS                        |                             |     1 |  2060 |    14  (22)| 00:00:01 |
|  18 |          NESTED LOOPS                       |                             |     1 |  2050 |    13  (24)| 00:00:01 |
|  19 |           NESTED LOOPS                      |                             |     1 |  2038 |    12  (25)| 00:00:01 |
|  20 |            VIEW                             | SI_FLOW_DOWN_ORG_V          |     1 |  2015 |    13  (39)| 00:00:01 |
|  21 |             TEMP TABLE TRANSFORMATION       |                             |       |       |            |          |
|  22 |              LOAD AS SELECT                 | SYS_TEMP_0FD9D66DE_279E550D |       |       |            |          |
|* 23 |               FILTER                        |                             |       |       |            |          |
|* 24 |                CONNECT BY WITH FILTERING    |                             |       |       |            |          |
|  25 |                 TABLE ACCESS BY INDEX ROWID | SI_ORG_ENTITIES_T           |    34 |   986 |     1   (0)| 00:00:01 |
|* 26 |                  INDEX FULL SCAN            | SI_ORG_ENTITIES_N1          |    34 |       |     1   (0)| 00:00:01 |
|  27 |                 MERGE JOIN                  |                             |    89 |  3738 |     4  (25)| 00:00:01 |
|  28 |                  TABLE ACCESS BY INDEX ROWID| SI_ORG_ENTITIES_T           |    34 |   986 |     2   (0)| 00:00:01 |
|  29 |                   INDEX FULL SCAN           | SI_ORG_ENTITIES_N3          |    34 |       |     1   (0)| 00:00:01 |
|* 30 |                  SORT JOIN                  |                             |    34 |   442 |     2  (50)| 00:00:01 |
|  31 |                   CONNECT BY PUMP           |                             |       |       |            |          |
|* 32 |              HASH JOIN                      |                             |     1 |  2067 |     6  (34)| 00:00:01 |
|  33 |               VIEW                          | VW_SQ_1                     |    34 |   884 |     3  (34)| 00:00:01 |
|  34 |                HASH GROUP BY                |                             |    34 |   578 |     3  (34)| 00:00:01 |
|  35 |                 VIEW                        |                             |    34 |   578 |     2   (0)| 00:00:01 |
|  36 |                  TABLE ACCESS FULL          | SYS_TEMP_0FD9D66DE_279E550D |    34 |  8500 |     2   (0)| 00:00:01 |
|  37 |               VIEW                          |                             |    34 | 69394 |     2   (0)| 00:00:01 |
|  38 |                TABLE ACCESS FULL            | SYS_TEMP_0FD9D66DE_279E550D |    34 |  8500 |     2   (0)| 00:00:01 |
|* 39 |            TABLE ACCESS BY INDEX ROWID      | SI_USERS_T                  |     1 |    23 |     1   (0)| 00:00:01 |
|* 40 |             INDEX RANGE SCAN                | SI_USERS_T_N2               |     1 |       |     1   (0)| 00:00:01 |
|* 41 |           INDEX FULL SCAN                   | SI_ORG_ROLES_N3             |     2 |    24 |     1   (0)| 00:00:01 |
|* 42 |          INDEX RANGE SCAN                   | SI_USER_ORG_ROLES_N2        |     1 |    10 |     1   (0)| 00:00:01 |
|* 43 |         INDEX RANGE SCAN                    | SI_ROLES_N2                 |     1 |     5 |     1   (0)| 00:00:01 |
|* 44 |        INDEX RANGE SCAN                     | SI_ROLE_ACTIVITIES_N2       |    14 |   140 |     1   (0)| 00:00:01 |
|* 45 |       INDEX RANGE SCAN                      | SI_ACTIVITIES_N1            |     1 |       |     1   (0)| 00:00:01 |
|* 46 |      TABLE ACCESS BY INDEX ROWID            | SI_ACTIVITIES_T             |     1 |    30 |     1   (0)| 00:00:01 |
|  47 |     BUFFER SORT                             |                             |     1 |       |    26  (16)| 00:00:01 |
|  48 |      VIEW                                   |                             |     1 |       |    10  (10)| 00:00:01 |
|  49 |       SORT AGGREGATE                        |                             |     1 |    82 |            |          |
|* 50 |        HASH JOIN                            |                             |     1 |    82 |    10  (10)| 00:00:01 |
|* 51 |         HASH JOIN                           |                             |     1 |    65 |     7  (15)| 00:00:01 |
|* 52 |          TABLE ACCESS FULL                  | MS_QS_PARAMETER_TYPES       |     1 |    34 |     3   (0)| 00:00:01 |
|* 53 |          TABLE ACCESS FULL                  | MS_QS_PARAMETER_NAMES       |     2 |    62 |     3   (0)| 00:00:01 |
|* 54 |         TABLE ACCESS FULL                   | MS_QS_PARAMETER_VALUES      |   174 |  2958 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ACTIVITY_NAME"=TRIM("A"."ROLE_ACTIVITY"))
   2 - access("T"."ACTION_ID"="TL"."ACTION_ID"(+) AND "T"."LOCALE_ID"="TL"."LOCALE_ID"(+))
   3 - access("A"."ACTION_ID"="T"."ACTION_ID")
   4 - filter("A"."CURRENT_STAGE"=20 AND "A"."PREVIOUS_STAGE"=4 AND 
              "MS_ISM_HELPER"."CHECK_ACTIONS"(TO_CHAR("A"."ACTION_ID"),'4','20',NULL,'ISSUE - 100311','ACTION - 
              100399','9r75','MS_ISM_ACTION')=1 AND "MS_ISM_HELPER"."CONFIG_PARAMETERS_ACTFILTER"(TO_CHAR("A"."ACTION_ID"),'ACTIO
              N - 100399')=1)
   7 - filter("ENABLED_FLAG"='Y')
  23 - filter(NVL("END_DATE",SYSDATE@!)>=SYSDATE@!)
  24 - access("PARENT_ORG_ENTITY_ID"=PRIOR "ORG_ENTITY_ID")
  26 - filter("ORG_ENTITY_ID" IS NOT NULL)
  30 - access("connect$_by$_pump$_025"."PRIOR OE.ORG_ENTITY_ID "="PARENT_ORG_ENTITY_ID")
       filter("connect$_by$_pump$_025"."PRIOR OE.ORG_ENTITY_ID "="PARENT_ORG_ENTITY_ID")
  32 - access("LVL"="MAX(LVL)" AND "ITEM_0"="O1"."ORG_ENTITY_ID")
  39 - filter(NVL("END_DATE",SYSDATE@!)>=SYSDATE@!)
  40 - access("H"."ENTERPRISE_ID"="ENTERPRISE_ID" AND "USER_NAME"='9r75')
  41 - filter("H"."PATH" LIKE '%:'||TO_CHAR("G"."ORG_ENTITY_ID")||':%')
  42 - access("G"."ORG_ROLE_ID"="E"."ORG_ROLE_ID" AND "USER_ID"="E"."USER_ID")
  43 - access("R"."ROLE_ID"="G"."ROLE_ID")
  44 - access("A"."ROLE_ID"="R"."ROLE_ID")
  45 - access("A"."ACTIVITY_ID"="C"."ACTIVITY_ID")
  46 - filter("C"."ACTIVITY_NAME" LIKE 'ISM%')
  50 - access("PNAME"."PARAMETER_ID"="PVALUE"."PARAMETER_ID" AND 
              "PTYPE"."PARAMETER_TYPE_ID"="PVALUE"."PARAMETER_TYPE_ID")
  51 - access("PTYPE"."PARAMETER_TYPE_ID"="PNAME"."PARAMETER_TYPE_ID")
  52 - filter(UPPER("PTYPE"."PARAMETER_TYPE")='MS APPS USER NAME DISPLAY FORMAT')
  53 - filter(UPPER("PNAME"."PARAMETER_NAME")='DISPLAY_FORMAT')
  54 - filter("PVALUE"."ORG_ENTITY_ID"=1)


While comparing this two plans i came across these two lines


  30 - access("connect$_by$_pump$_025"."PRIOR OE.ORG_ENTITY_ID "="PARENT_ORG_ENTITY_ID")
       filter("connect$_by$_pump$_025"."PRIOR OE.ORG_ENTITY_ID "="PARENT_ORG_ENTITY_ID")


Will that cause any issue , is it here for some specific reason.
Re: Whast is connect$_by$_pump$ in explain plan [message #653201 is a reply to message #653200] Tue, 28 June 2016 23:35 Go to previous message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Updates:

I have a inline view
WITH ORG_VALUES AS  (SELECT OE.ORG_ENTITY_ID, OE.ORG_ENTITY_NAME, 
OE.ENTERPRISE_ID, 
   LEVEL LVL , SYS_CONNECT_BY_PATH(OE.ORG_ENTITY_ID, ':') PATH
   FROM SI_ORG_ENTITIES OE WHERE  NVL (OE.END_DATE, SYSDATE) >= SYSDATE
    START WITH OE.ORG_ENTITY_ID IS NOT NULL
   CONNECT BY PRIOR OE.ORG_ENTITY_ID = OE.PARENT_ORG_ENTITY_ID) 
  SELECT  ORG_ENTITY_ID, ORG_ENTITY_NAME,
ENTERPRISE_ID, 'ROOT:'||PATH||':END' PATH FROM ORG_VALUES O1    WHERE LVL = (SELECT MAX (LVL)
                      FROM ORG_VALUES O2
                     WHERE O2.ORG_ENTITY_ID = O1.ORG_ENTITY_ID);

Previous Topic: Performance worse for given workflow query
Next Topic: Compare spatial and attributive query
Goto Forum:
  


Current Time: Thu Mar 28 11:48:06 CDT 2024