5 Replies Latest reply on Aug 1, 2014 6:17 PM by thatJeffSmith-Oracle

BUG: 4.0.0.13 Report Refresh opens a new cursor for every child query

Gamblesk Aug 1, 2014 2:34 PM

It appears that a parent report refreshing also refreshes one of the child reports in my case its the first one in the list and for every refresh of the parent a new cursor is created for the child until you hit max open cursors.

Have a report that shows active sessions. If I set that to refresh every 5 seconds.

SID 4876 is my report refreshing.

Select from open cursors.

SYSDATE SID SQL_ID COUNT(*)

-------------------- ---------- ------------- ----------

Aug 01 2014 09:11:17 4876 0hm6ba4rbdbxp 272

Aug 01 2014 09:11:19 4876 0hm6ba4rbdbxp 273

Aug 01 2014 09:11:23 4876 0hm6ba4rbdbxp 274

The open cursor is for the query shown in the first child report tab which in this case is a select from v$session_longops.

Looks like this was reported back in 2007 as well Child Report Cursors Are Not Being Closed

I will try to reproduce this on the latest version of SQL Developer. The version of Oracle for this is 10.2.0.5. I will also try on version 11.2 which is the latest I have available.

Some Additional detals.

Happens on both latest SQL developer ( 4.0.2.15) and 11.2.0.3 of Oracle as well.

also can change the query in the child report to something as simple as select sysdate from dual and the same thing happens.

Message was edited by: Gamblesk

1. Re: BUG: 4.0.0.13 Report Refresh opens a new cursor for every child query

thatJeffSmith-Oracle Aug 1, 2014 2:34 PM (in response to Gamblesk)

That's fixed in v4.0.2
Like Show 0 Likes(0)

Actions
2. Re: BUG: 4.0.0.13 Report Refresh opens a new cursor for every child query

Gamblesk Aug 1, 2014 3:53 PM (in response to thatJeffSmith-Oracle)

Just installed 4.0.2.15 and the same thing happens.

I put a select sysdate from dual into the child report and I get a new cursor for every refresh when the parent refreshes.

Maybe this will help. This does not appear to happen when the child is set to refresh. Only when the parent is.
Like Show 0 Likes(0)

Actions
3. Re: BUG: 4.0.0.13 Report Refresh opens a new cursor for every child query

thatJeffSmith-Oracle Aug 1, 2014 4:01 PM (in response to Gamblesk)

I'm not seeing that behavior. I'm counting open cursors by going against v$statname where name = 'opened cursors current'
Like Show 0 Likes(0)

Actions
4. Re: BUG: 4.0.0.13 Report Refresh opens a new cursor for every child query

Gamblesk Aug 1, 2014 4:26 PM (in response to thatJeffSmith-Oracle)

I am not sure what is different about what I am doing but here is a child report showing the value of opened cursors current and it count goes up with refreshes.
This also only happens when the type is set to table. It does not happen if the type is set to script.

I can gather anything else you think would be useful but I am not sure what else to add.

Parent query
    select * from v$session where username = 'DBA_MONITOR'
Child query - Hardcoded SID to make sure it was showing right one.

select sysdate, stat.sid,
   stat.value, statname.name
   from v$sesstat stat,
        v$session sess,
        v$statname statname
   where stat.sid = 617
   and stat.sid = sess.sid
   and statname.statistic# = stat.statistic#
and statname.name = 'opened cursors current'
Like Show 0 Likes(0)

Actions
5. Re: BUG: 4.0.0.13 Report Refresh opens a new cursor for every child query

thatJeffSmith-Oracle Aug 1, 2014 6:17 PM (in response to Gamblesk)
It took a few minutes to find the exact reproducible scenario - a bit different than the one originally reported that we did FIX.

But, two things:
thanks for the effort here and reporting this
I've reopened the bug with a high priority and hope to have it addressed for our next update
Like Show 0 Likes(0)

Actions

Go to original post