Performance across Oracle Gateway

From: Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com>
Date: Thu, 23 Sep 1999 19:16:07 GMT
Message-ID: <7sdu98$l2v$1@nnrp1.deja.com>

Subject: Performance across Oracle Gateway

One of our application developers brought me a piece of SQL, looking for some performance help. The statement (shown below) includes a join to a DB2 database accessed across the Oracle Gateway. We suspect that what is happening is that Oracle is issuing a SELECT to the DB2 database, pulling the entire result set thru the gateway, then performing the logic necessary to complete the join. The questions are: 1) is there anyway to verify that this assumption is correct? 2) is there any effective way to minimize the result set returned by the remote database in a join such as this?

Here is the statement. All references to table names beginning with "SY_" are synonyms pointing to a link to the DB2/MVS table via the Oracle Gateway.

---

*

SELECT SY_LCM_EMPLOYEE_VEH.LCMEVH_MDL_YR,
       SY_LCM_EMPLOYEE_VEH.LCMEVH_MDL_DSC,
       SY_LCM_EMPLOYEE_VEH.LCMEVH_EMP_NBR,
       SY_LCM_EMPLOYEE_VEH.LCMEVH_EMP_1ST_NME,
       SY_LCM_EMPLOYEE_VEH.LCMEVH_EMP_LST_NME,
       SY_LCM_EMPLOYEE_VEH.LCMEVH_VEH_ID,
       SY_LCM_EMPLOYEE_VEH.LCMEVH_VEH_TYP_CDE,
       LCM_EMP_APPOINTMENT.LCMEAP_APTMNT_DTE,
       LCM_EMP_APPOINTMENT.LCMEAP_FACL_ID,
       LCM_EMP_APPOINTMENT.LCMEAP_SHFT_CDE,
       LCM_EMP_APPOINTMENT.LCMEAP_EMP_WKPHNBR,
       LCM_EMP_APPOINTMENT.LCMEAP_EMP_HMPHNBR,
       LCM_EMP_APPOINTMENT.LCMEAP_TYP_CDE,
       LCM_EMP_APPOINTMENT.LCMEAP_ARCSTACD
  FROM LCM_EMP_APPOINTMENT,
       SY_LCM_EMPLOYEE_VEH

 WHERE ( LCM_EMP_APPOINTMENT.LCMEAP_APTMNT_DTE = To_Date ('09/30/1999','mm/dd/yyyy')) and

( SY_LCM_EMPLOYEE_VEH.LCMEVH_EMP_NBR =
LCM_EMP_APPOINTMENT.LCMEAP_EMP_NBR ) and

( SY_LCM_EMPLOYEE_VEH.LCMEVH_VEH_ID =
LCM_EMP_APPOINTMENT.LCMEAP_VEH_ID ) AND        (( NMM.SY_LCM_EMPLOYEE_VEH.LCMEVH_VEH_TYP_CDE = 'P' )) --
Ed Stevens

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 23 1999 - 14:16:07 CDT