We are using IBM BAW 20.0.0.1.

Below SQL is consuming some resources on the PROD DB intermittently.

We observed that the query using CPU Utilization 100%.

Can you please explain about What is the query purpose & What is the query doing?

We plan to consider/create indexing for CPU utilization reduce.

Can you please give some information?

Thanks.

-----------------------------------------------------

Query:

SELECT nvl(

max(SUBSTR(SEG_FILE_NAME, INSTR(SEG_FILE_NAME, :"SYS_B_00", :"SYS_B_01", :"SYS_B_02")+:"SYS_B_03", :"SYS_B_04")),

:"SYS_B_05")

FROM BIF_FILE_HANDLER BL, BIF_LOAD_MASTER BM

WHERE BL.LOAD_ID=BM.LOAD_ID

AND BL.LAST_UPDATE_DT=(SELECT MAX(BL.LAST_UPDATE_DT)

FROM BIF_FILE_HANDLER BFL, BIF_LOAD_MASTER BLM

WHERE BFL.LOAD_ID=BLM.LOAD_ID

AND BLM.SOURCE_SYSTEM = :"SYS_B_06"

AND FILE_VAL_STATUS=:"SYS_B_07"

AND UPPER(SEG_FILE_NAME) LIKE UPPER(:"SYS_B_08"))

AND UPPER(SEG_FILE_NAME) LIKE UPPER(:"SYS_B_09")

AND FILE_VAL_STATUS=:"SYS_B_10"

AND BM.SOURCE_SYSTEM = :"SYS_B_11"

I do not recognize those tables as BPM or MDM. Are those custom tables? If so, you will need to troubleshoot this if they are non product related tables. I hope this helps..thanks!

Thanks for your information.