Home » RDBMS Server » Performance Tuning » How to change the execution order (Oracle,11g,AIX)
How to change the execution order [message #553152] Wed, 02 May 2012 06:23 Go to next message
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 #553170 is a reply to message #553152] Wed, 02 May 2012 08:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
are statistics for IBM_CLIENT_MGR6_EMPID & any indexes current & accurate?

how many rows exist in IBM_CLIENT_MGR6_EMPID at execution time?

which columns of IBM_CLIENT_MGR6_EMPID are indexed
Re: How to change the execution order [message #553172 is a reply to message #553170] Wed, 02 May 2012 08:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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
Re: How to change the execution order [message #553180 is a reply to message #553172] Wed, 02 May 2012 08:30 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
As for why push_subq isn't working, the documentation says:
Quote:

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.
Previous Topic: Gather statistics on SYS objects
Next Topic: Achieve same execution plan
Goto Forum:
  


Current Time: Fri Mar 29 10:03:20 CDT 2024