| Whast is connect$_by$_pump$ in explain plan [message #653200] |
Tue, 28 June 2016 23:14  |
|
|
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  |
|
|
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);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
