Forum Stats

  • 3,851,525 Users
  • 2,263,993 Discussions
  • 7,904,766 Comments

Discussions

'RUNNING_SESSIONS' query

Charles M
Charles M Member Posts: 809 Silver Badge
edited Apr 2, 2018 10:53AM in Data Integrator

Hi All -

We were reviewing Doc ID 1681972.1 recently. It mentioned to create an index on snp_session to improve an inefficient query. I looked and found the query in our Dev instance (or, at least a very similar query):

SELECT count(DISTINCT SESS.SESS_NO) RUNNING_SESSIONS FROM SNP_SESSION SESS WHERE SESS.AGENT_NAME = :1 AND SESS.SESS_STATUS = :2

I ran the SQL Tuning Advisor on it, and found that it did recommend using an index (slightly different than the note; but, nonetheless indicated an much improved execution plan).

However, in both our UAT & Production instances, I find a somewhat different query getting executed for 'RUNNING_SESSIONS' (Note: I am searching on 'RUNNING_SESSIONS' in the SQL text, to find this ... ):

SELECT count(DISTINCT SESS.SESS_NO) RUNNING_SESSIONS  FROM SNP_SESSION SESS LEFT OUTER JOIN SNP_LPI_STEP_LOG LPSL ON SESS.SESS_NO = LPSL.SESS_NO AND LPSL.RETURN_CODE IS NULL LEFT OUTER JOIN SNP_LPI_EXC_LOG LPEL ON SESS.SESS_NO = LPEL.SESS_NO AND LPEL.RETURN_CODE IS NULL  WHERE SESS.AGENT_NAME = :1 AND (SESS.SESS_STATUS = :2 OR (SESS.SESS_STATUS IN (:3 , :4 , :5 ) AND ((LPSL.STATUS IN ('R', 'W') AND EXISTS  (SELECT LPR.STATUS  FROM SNP_LPI_RUN LPR  WHERE LPR.I_LP_INST = LPSL.I_LP_INST AND LPR.NB_RUN = LPSL.NB_RUN AND LPR.STATUS = 'R') ) OR (LPEL.STATUS IN ('R', 'W') AND EXISTS  (SELECT LPR.STATUS  FROM SNP_LPI_RUN LPR  WHERE LPR.I_LP_INST = LPEL.I_LP_INST AND LPR.NB_RUN = LPEL.NB_RUN AND LPR.STATUS = 'R') ) ) ) )

Any thoughts as to why there is a different query for RUNNING_SESSIONS in the other instances?

I can see that the tuning advisor also recommends an index on this one as well, but it is not on the SNP_SESSION table ...

ODI 11.1.1.9.0 / OBIEE 11.1.1.9.3 / OBIA 11.1.1.10.1

Regards,

Charles

Tagged:

Answers

This discussion has been closed.