LifeSaver of the Week -2

CLIENT_INFO

Pinpointing PeopleSoft user sessions to Oracle session is vital for troubleshooting performance related issues. Previously, I used some crude techniques (using the V$SESSION.STATUS =
‘ACTIVE’ and V$SESSION.PROGRAM) to map the PeopleSoft session to the Oracle session. This was not always accurate in a production system where multiple user sessions are active at the same time and running the same program. I came across V$SESSION.CLIENT_INFO and it made my life so much easier…

PeopleSoft populates CLIENT_INFO by default for all 2-tier sessions, COBOL, SQR and Process Scheduler. However, for 3-tier connections we need to enable ‘EnableDBMonitoring = 1’ in the psappsrv.cfg configuration file.

Now, if I get a call from a user having performance problem running the custom report AABC123.sqr then I can log on to SQLPLUS and run the following SQL to determine what the session for OPRID NPAI is currently doing.

SELECT SQL_TEXT FROM V$SQLTEXT

WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION

WHERE STATUS = ‘ACTIVE’ AND UPPER(PROGRAM) LIKE ‘SQR%’

AND CLIENT_INFO LIKE ‘NPAI%’)

ORDER BY PIECE

/

Note:

PeopleSoft also delivers a function GET_PS_OPRID which can be used to determine the OPRID. So if you have that function created in your database then you can use the below SQL to determine the OPRID.

SELECT GET_PS_OPRID(client_info) FROM V$SESSION WHERE …

Alternatively you can use -

SELECT SUBSTR(client_info,1,INSTR(client_info,’,') – 1) FROM V$SESSION WHERE …

Read more about LifeSaver.