search cancel

Cursor leak in Peoplesoft Agent: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

book

Article ID: 200166

calendar_today

Updated On:

Products

CA Workload Automation AE - Business Agents (AutoSys) CA Workload Automation AE - System Agent (AutoSys) CA Workload Automation AE - Scheduler (AutoSys) Workload Automation Agent CA Workload Automation AE

Issue/Introduction

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

 

Cause

This issue is due to a cursor leak in the PeopleSoft Agent

Environment

Release : 11.5.00.00-0059

Component : CA Workload Automation Agent for PeopleSoft 

PeopleTools version: 8.57

Resolution

This issue is addressed in fix SO14752. The solution can be found using the Solution Search feature on the Product Download site. 
Search for the fix number in the "Search by AparNo" field of the page.

Direct link to solution download: https://support.broadcom.com/download-center/edit-solution.html?aparNo=SO14752&os=ANY