Issue on open cursor maximum reached
Date: Wed, 27 Feb 2013 16:01:47 +0530
Message-ID: <CAFQkUXRqXUWSqVXUx=p7CDxDgnShd0SfHDqqv5DhiOhwZjkKvw_at_mail.gmail.com>
Hi,
We see some wierd issue in our ERP and NON-ERP environments where open_cursors reaching maximum causing the new jvm sessions to spawn multiple sessions ending with INACTIVE state. When we observed the alert log on why the connections are getting into inactive state I was seeing lot of max open cursors and it reached to the set value i.e 2000. But looking at the SID 221 I was seeing there was a SQL "*select count(*) from SYS.AQ$_SYS$SERVICE_METRICS_TAB_S
where nam*e = :1" which was just causing the open cursors to be in open state. But reaching to Oracle SR did not find any correct root cause. Ideally we had to bounce the job queue process to fix the issue. Can someone please share if you had the similar occurance and what could be the cause?
*
*
*Oracle Version: 11.2.0.3 on Linux 64 bit*
VALUE NAME SID
---------- ---------------------------------------------------------------- ---------- * 2000 opened cursors current 221*19 opened cursors current 728
17 opened cursors current 722
12 opened cursors current 874
7 opened cursors current 147
6 opened cursors current 5
6 opened cursors current 291
6 opened cursors current 153
6 opened cursors current 149
6 opened cursors current 80
Session and Process Information for 221 (SID)
Sql Statement
*select count(*) from SYS.AQ$_SYS$SERVICE_METRICS_TAB_S where nam*
*e = :1*
Event Wait Information
SID 221 is waiting on event : rdbms ipc message
P1 Text : timeout
P1 Value : 20
P2 Text :
P2 Value : 0
P3 Text :
P3 Value : 0
EVENT COUNT(*) AVG(B.WAIT_TIME)
---------------------------------------- ---------- ----------------PL/SQL lock timer 1 0
Streams AQ: qmn coordinator idle wait 1 0 VKTM Logical Idle Wait 1 0
GCR sleep 1 0
Streams AQ: qmn slave idle wait 1 0
smon timer 1 0
pmon timer 1 0
ges remote message 1 0
Streams AQ: emn coordinator idle wait 1 0 PING 1 0
wait for unread message on broadcast cha 1 0 nnel
DIAG idle wait 2 0
db file sequential read 2 0
library cache lock 2 0
EMON slave idle wait 3 0
gcs remote message 4 0
Space Manager: slave idle wait 4 0
Streams AQ: waiting for time management 5 0
or cleanup tasks
*rdbms ipc message 19 0*
SQL*Net message from client 46 0
SQL> conn / as sysdba
Connected.
SQL> SELECT TRUNC(enq_time), COUNT(*) FROM sys$service_metrics_tab GROUP BY
TRUNC(enq_time) ORDER BY 1;
no rows selected
SQL> select * from SYS.AQ$_SYS$SERVICE_METRICS_TAB_T; no rows selected
SQL> select * from SYS.AQ$_SYS$SERVICE_METRICS_TAB_S; no rows selected
SQL> select * from SYS.AQ$_SYS$SERVICE_METRICS_TAB_S 2 ;
SUBSCRIBER_ID QUEUE_NAME NAME
------------- ------------------------------ ------------------------------ADDRESS
PROTOCOL SUBSCRIBER_TYPE RULE_NAME TRANS_NAME RULESET_NAME NEGATIVE_RULESET_NAME CREATION_TIME MODIFICATION_TIME DELETION_TIME SCN_AT_REMOVE SCN_AT_ADD
---------- --------------- ------------------------------ ----------------------------------------------------------------- ----------------------------------------------------------------- ----------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ----------
0 0
21 SYS$SERVICE_METRICS SCHED$_LBAGT$_3
0 65 17-JUN-12 02.46.42.332846 PM +00:00 1.2555E+13
41 SYS$SERVICE_METRICS SCHED$_LBAGT$_2
0 65 17-JUN-12 02.49.19.606773 PM +00:00 1.2555E+13
61 SYS$SERVICE_METRICS SCHED$_LBAGT$_4
0 65 17-JUN-12 02.49.19.740646 PM +00:00 1.2555E+13
81 SYS$SERVICE_METRICS SCHED$_LBAGT$_1
0 65 17-JUN-12 02.53.07.011504 PM +00:00 1.2555E+13
2 SYS$SERVICE_METRICS
"SYS"."SYS$SERVICE_METRICS"
0 4
1 SYS$SERVICE_METRICS SYS$RLB_GEN_SUB 0 65 SYS.SYS$SERVICE_METRICS_GEN_TS SQL> show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ job_queue_processes integer 20
SQL> show parameter aq
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ aq_tm_processes integer 5
SQL> alter system set job_queue_processes=0;
Thanks,
Shastry
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 27 2013 - 11:31:47 CET