Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

GambleskAug 1 2014 — edited Aug 1 2014

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.

SQL_DEV_Report.png

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

Comments

That's fixed in v4.0.2

Gamblesk

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.

I'm not seeing that behavior. I'm counting open cursors by going against v$statname where name = 'opened cursors current'

Gamblesk

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.

cursor_count_1.png

cursor_count_2.png

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'

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:

  1. thanks for the effort here and reporting this
  2. I've reopened the bug with a high priority and hope to have it addressed for our next update
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 29 2014
Added on Aug 1 2014
5 comments
1,469 views