| How to change the execution order [message #553152] |
Wed, 02 May 2012 06:23  |
 |
vattigunta
Messages: 32
Registered: August 2011
Location: India
|
Member |
|
|
I have been facing some peformance issues with the below mentioned sql.After gone through execution plan we have found out the reason but we couldn't able to change the execution plan the way we want.
If we could able to join HRMGR.HR_EXPANDED_BOOK table with MISBOMGR.ibm_client_mgr7_empid,
MISBOMGR.ibm_client_mgr6_empid at earlier stage means before HRMGR.HR_EMP_STATUS_LOOKUP then my issue will be solved but somehow optimizer is not considering that path. Even i have added push_subq hint which will push sub queries to execute at earlier stage but no use. Could anybody give some insights about why push_subq hint is not working in this sceneria and what can be the other alternative to change the driving path.
Query :-
select /*+ push_subq */CEMP.EMP_ID,
CEMP.EMP_STATUS_CD,
EMP_STATUS_DESC,
MGR_6_EMP_ID,
MGR_7_EMP_ID
FROM
HRMGR.HR_EXPANDED_BOOK@INFODB CEMP
LEFT JOIN
HRMGR.HR_EMP_STATUS_LOOKUP@INFODB EMPLU
ON CEMP.EMP_STATUS_CD = EMPLU.EMP_STATUS_CD
WHERE CEMP.EMP_STATUS_CD = 'P'
AND (exists
(SELECT /*+ use_hash(s1) */ 1 from (select DISTINCT mgr_7_emp_id
FROM MISBOMGR.ibm_client_mgr7_empid) S1 where s1.mgr_7_emp_id=CEMP.MGR_7_EMP_ID )
or EXISTS
(SELECT 1 FROM (SELECT DISTINCT mgr_6_emp_id
FROM MISBOMGR.ibm_client_mgr6_empid) S2 WHERE s2.mgr_6_emp_id=CEMP.MGR_6_EMP_ID))
Execution plan :-
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16958 | 927K| 12008 (2)| 00:02:25 | | |
|* 1 | FILTER | | | | | | | |
| 2 | MERGE JOIN OUTER | | 173K| 9511K| 12008 (2)| 00:02:25 | | |
| 3 | REMOTE | HR_EXPANDED_BOOK | 173K| 7303K| 12005 (2)| 00:02:25 | INFODB | R->S |
|* 4 | SORT JOIN | | 11 | 143 | 3 (34)| 00:00:01 | | |
| 5 | REMOTE | HR_EMP_STATUS_LOOKUP | 11 | 143 | 2 (0)| 00:00:01 | INFODB | R->S |
|* 6 | TABLE ACCESS FULL| IBM_CLIENT_MGR7_EMPID | 1 | 8 | 2 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL| IBM_CLIENT_MGR6_EMPID | 1 | 8 | 3 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ USE_HASH ("IBM_CLIENT_MGR7_EMPID") */ 0 FROM
"MISBOMGR"."IBM_CLIENT_MGR7_EMPID" "IBM_CLIENT_MGR7_EMPID" WHERE "MGR_7_EMP_ID"=:B1) OR EXISTS
(SELECT 0 FROM "MISBOMGR"."IBM_CLIENT_MGR6_EMPID" "IBM_CLIENT_MGR6_EMPID" WHERE "MGR_6_EMP_ID"=:B2))
4 - access("CEMP"."EMP_STATUS_CD"="EMPLU"."EMP_STATUS_CD"(+))
filter("CEMP"."EMP_STATUS_CD"="EMPLU"."EMP_STATUS_CD"(+))
6 - filter("MGR_7_EMP_ID"=:B1)
7 - filter("MGR_6_EMP_ID"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "EMP_ID","EMP_STATUS_CD","MGR_6_EMP_ID","MGR_7_EMP_ID" FROM
"HRMGR"."HR_EXPANDED_BOOK" "SYS_ALIAS_2" WHERE "EMP_STATUS_CD"='P' (accessing 'INFODB' )
5 - SELECT "EMP_STATUS_CD","EMP_STATUS_DESC" FROM "HRMGR"."HR_EMP_STATUS_LOOKUP" "EMPLU"
(accessing 'INFODB' )
---------------------------
edit: added code tags to make it readable. Please do this yourself in future. jw.
[Updated on: Wed, 02 May 2012 06:52] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: How to change the execution order [message #553172 is a reply to message #553170] |
Wed, 02 May 2012 08:24  |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Try this:
WITH cemp AS
(SELECT emp_id,
emp_status_cd,
mgr_6_emp_id,
mgr_7_emp_id
FROM HRMGR.HR_EXPANDED_BOOK@INFODB heb
WHERE emp_status_cd = 'P'
AND (EXISTS (SELECT NULL
FROM MISBOMGR.ibm_client_mgr7_empid
WHERE mgr_7_emp_id = heb.mgr_7_emp_id
)
OR EXISTS (SELECT NULL
FROM MISBOMGR.ibm_client_mgr6_empid
WHERE mgr_6_emp_id = heb.mgr_6_emp_id
)
)
)
SELECT cemp.emp_id,
cemp.emp_status_cd,
emplu.emp_status_desc,
cemp.mgr_6_emp_id,
cemp.mgr_7_emp_id
FROM cemp
LEFT JOIN hrmgr.hr_emp_status_lookup@infodb emplu
ON cemp.emp_status_cd = emplu.emp_status_cd
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
