Tuning BO report of FCUBS which is taking more than 30mins

Tuning BO report of FCUBS which is taking more than 30mins,

By using hash join you can change the response time to 2mins.

Below is the example of query run more than 30mins,the plan is taking nested loop.

SELECT

 ACVW_ALL_AC_ENTRIES.TRN_REF_NO,

 ACVW_ALL_AC_ENTRIES.TRN_CODE,

 ACVW_ALL_AC_ENTRIES.AC_NO,

 GLTMS_GLMASTER.GL_DESC,

 ACVW_ALL_AC_ENTRIES.CUST_GL,

 ACVW_ALL_AC_ENTRIES.MODULE,

 ACVW_ALL_AC_ENTRIES.AC_CCY,

 ACVW_ALL_AC_ENTRIES.DRCR_IND,

 ACVW_ALL_AC_ENTRIES.FCY_AMOUNT,

 ACVW_ALL_AC_ENTRIES.TRN_DT,

 ACVW_ALL_AC_ENTRIES.VALUE_DT,

 ACVW_ALL_AC_ENTRIES.LCY_AMOUNT,

 ACVW_ALL_AC_ENTRIES.AUTH_ID,

 ACVW_ALL_AC_ENTRIES.USER_ID

FROM

 FCUBSUSER.ACVW_ALL_AC_ENTRIES,

 FCUBSUSER.GLTMS_GLMASTER

….

…

…

UNION

SELECT

 ACVW_ALL_AC_ENTRIES.TRN_REF_NO,

 ACVW_ALL_AC_ENTRIES.TRN_CODE,

 ACVW_ALL_AC_ENTRIES.AC_NO,

 STTMS_CUST_ACCOUNT.AC_DESC,

 ACVW_ALL_AC_ENTRIES.CUST_GL,

 ACVW_ALL_AC_ENTRIES.MODULE,

 ACVW_ALL_AC_ENTRIES.AC_CCY,

 ACVW_ALL_AC_ENTRIES.DRCR_IND,

 ACVW_ALL_AC_ENTRIES.FCY_AMOUNT,

 ACVW_ALL_AC_ENTRIES.TRN_DT,

 ACVW_ALL_AC_ENTRIES.VALUE_DT,

 ACVW_ALL_AC_ENTRIES.LCY_AMOUNT,

 ACVW_ALL_AC_ENTRIES.AUTH_ID,

 ACVW_ALL_AC_ENTRIES.USER_ID

FROM

 FCUBSUSER.ACVW_ALL_AC_ENTRIES,

 FCUBSUSER.STTMS_CUST_ACCOUNT

WHERE

….

….

…

Explain plan:

++By changing the plan from nested to hash we get the output in 2 Mins.

SELECT /*+ USE_HASH(ACVW_ALL_AC_ENTRIES GLTMS_GLMASTER)  index(GLTMS_GLMASTER PK01_GLTM_GLMASTER)*/

 ACVW_ALL_AC_ENTRIES.TRN_REF_NO,

 ACVW_ALL_AC_ENTRIES.TRN_CODE,

 ACVW_ALL_AC_ENTRIES.AC_NO,

 GLTMS_GLMASTER.GL_DESC,

 ACVW_ALL_AC_ENTRIES.CUST_GL,

 ACVW_ALL_AC_ENTRIES.MODULE,

 ACVW_ALL_AC_ENTRIES.AC_CCY,

 ACVW_ALL_AC_ENTRIES.DRCR_IND,

 ACVW_ALL_AC_ENTRIES.FCY_AMOUNT,

 ACVW_ALL_AC_ENTRIES.TRN_DT,

 ACVW_ALL_AC_ENTRIES.VALUE_DT,

 ACVW_ALL_AC_ENTRIES.LCY_AMOUNT,

 ACVW_ALL_AC_ENTRIES.AUTH_ID,

 ACVW_ALL_AC_ENTRIES.USER_ID

FROM

 FCUBSUSER.ACVW_ALL_AC_ENTRIES,

 FCUBSUSER.GLTMS_GLMASTER

……

…..

….

UNION

SELECT /*+ USE_HASH(ACVW_ALL_AC_ENTRIES STTMS_CUST_ACCOUNT) */

 ACVW_ALL_AC_ENTRIES.TRN_REF_NO,

 ACVW_ALL_AC_ENTRIES.TRN_CODE,

 ACVW_ALL_AC_ENTRIES.AC_NO,

 STTMS_CUST_ACCOUNT.AC_DESC,

 ACVW_ALL_AC_ENTRIES.CUST_GL,

 ACVW_ALL_AC_ENTRIES.MODULE,

 ACVW_ALL_AC_ENTRIES.AC_CCY,

 ACVW_ALL_AC_ENTRIES.DRCR_IND,

 ACVW_ALL_AC_ENTRIES.FCY_AMOUNT,

 ACVW_ALL_AC_ENTRIES.TRN_DT,

 ACVW_ALL_AC_ENTRIES.VALUE_DT,

 ACVW_ALL_AC_ENTRIES.LCY_AMOUNT,

 ACVW_ALL_AC_ENTRIES.AUTH_ID,

 ACVW_ALL_AC_ENTRIES.USER_ID

FROM

 FCUBSUSER.ACVW_ALL_AC_ENTRIES,

 FCUBSUSER.STTMS_CUST_ACCOUNT……

….

…..

++ How to change this in BO.

Login the BO and then open the report. In the Menu click on Edit data provider

++ Then click on the query which you want to update.

Now on the menu click on SQL

Then past tuned query and save.

Now you run the report and check.