PL/SQL Developer - select * from v$open_cursor where sid = :sid

[Ratna]

Hi,

Good day.
We are receving "WARNING: inbound connection timed out (ORA-3136)" in one of our production database every 2 to 3 hours.

Steps that taken by us :

1. Checked trace log - no related findings
2. Checked statspack report during the period of time we received the warning messages
3. Noticed that top event was

SQL ordered by CPU DB/Inst: TAPROD/TAPROD Snaps: 22078-22079
-> Total DB CPU (s): 607
-> Captured SQL accounts for 112.0% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU

CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
330.69 | 719 | 0.46 | 54.5 | 587.55 | 0 3972474907
Module: PL/SQL Developer
select * from v$open_cursor where sid = :sid

4. Checked the maximum open cursor allowed with the max cursor used.

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR
-------------------------------------------
167 2000

Kindly advice if there is any automated script that would trigger this select statement in pl/sql developer?
(719 Executions in one hour)

Thank you.

p/s : The version that we are using currently is Version 8 & Version 9

 

[Marco Kalter]

The query "select * from v$open_cursor where sid = :sid" will be executed by the Sessions Window (Tools > Sessions) when viewing the "Cursors" tab page. If the "Auto refresh" option of the Session Window is enabled, the query will be executed automatically at the selected time-interval.

 

[Ratna]

Thank you Marco !!

Will check and see our settings then.