PeopleSoft jobs are intermittently failing with the following error -
PSPlugin.ps Internal Thread.PsDbHandler.runUpdateNoReconnect[:602] - java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
......
The DBA team frequently receives a monitoring alert that the session <id> on Instance using over 80% percent of its maximum open cursors limit.
They identified the following SQL statement consuming all open cursors since the last refresh.
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FROM PS_PRCSDEFN WHERE PRCSNAME = ...
select sid, user_name, sql_id, sql_text, cursor_type, count(*)
from v$open_cursor
where sid = 2386
group by sid, user_name, sql_id, sql_text, cursor_type
order by count(*) desc;
|
SID
|
USER_NAME
|
SQL_ID
|
SQL_TEXT
|
CURSOR_TYPE
|
COUNT(*)
|
|
2386
|
PSAUTOSYS
|
37rdyy0arwwcx
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
123
|
|
2386
|
PSAUTOSYS
|
3sfbm9j1xccf0
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
71
|
|
2386
|
PSAUTOSYS
|
2gtzvzpxuhq38
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
71
|
|
2386
|
PSAUTOSYS
|
crc57h03gw8rm
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
71
|
|
2386
|
PSAUTOSYS
|
9b5k6n3mk29g6
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
52
|
|
2386
|
PSAUTOSYS
|
00f1v75s59c36
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
52
|
|
2386
|
PSAUTOSYS
|
dc92pmfw0yxy9
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
40
|
|
2386
|
PSAUTOSYS
|
fzgw9ud639tgf
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
40
|
|
2386
|
PSAUTOSYS
|
33sudnr4rz7r8
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
40
|
|
2386
|
PSAUTOSYS
|
5f0zq0g94fpm7
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
15
|
|
2386
|
PSAUTOSYS
|
b13rxjjt9a17u
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
13
|
|
2386
|
PSAUTOSYS
|
9nh7ch6yzmz9h
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
13
|
|
2386
|
PSAUTOSYS
|
0htqx053vag3d
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
13
|
|
2386
|
PSAUTOSYS
|
d809d5y31mf7a
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
13
|
|
2386
|
PSAUTOSYS
|
1b6wck4uu70ak
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
13
|
|
2386
|
PSAUTOSYS
|
3vunc42m548z9
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
8
|
|
2386
|
PSAUTOSYS
|
85pxtwjc47952
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
4
|
|
2386
|
PSAUTOSYS
|
fjxq4822k9q7j
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
2
|
|
2386
|
PSAUTOSYS
|
f15xraqwu2a2u
|
select OUTDESTFORMAT from PS_PRCSRUNCNTLDTL where PRCSNAME='
|
SESSION CURSOR CACHED
|
1
|
In the above table the SQL_IDs different. However, the statements look the same; That’s because PS Agent using literals.
The full text of the top statement, that has 123 open cursors:
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FROM PS_PRCSDEFN WHERE PRCSNAME = 'H5GL2471'
Grouping by the partial sql text:
select sid, user_name, sql_text, cursor_type, count(*)
from v$open_cursor
where sid = 2386
group by sid, user_name, sql_text, cursor_type
order by count(*) desc;
|
SID
|
USER_NAME
|
SQL_TEXT
|
CURSOR_TYPE
|
COUNT(*)
|
|
2386
|
PSAUTOSYS
|
SELECT OUTDESTTYPE,OUTDESTFORMAT,OUTDEST,PSRF_FOLDER_NAME FR
|
OPEN
|
654
|
|
2386
|
PSAUTOSYS
|
select RUNSTATUS from PSPRCSRQST where PRCSINSTANCE = 192049
|
SESSION CURSOR CACHED
|
7
|
|
2386
|
PSAUTOSYS
|
SELECT 'X' FROM PS_PRCSDEFNGRP A, PSAUTHPRCS B, PSOPRCLS C W
|
SESSION CURSOR CACHED
|
5
|
|
2386
|
PSAUTOSYS
|
select DESCR, PARMLIST, PARMLISTTYPE, WORKINGDIR, WORKINGDIR
|
SESSION CURSOR CACHED
|
5
|
