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