- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 65 Insurance
- 536.7K On-Premises Infrastructure
- 138.4K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.2K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
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 126.96.36.199.0 / OBIEE 188.8.131.52.3 / OBIA 184.108.40.206.1