Home » RDBMS Server » Performance Tuning » Cursors not closed at the database level (10gR2, Windows)
Cursors not closed at the database level [message #312698] Wed, 09 April 2008 11:14 Go to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Hi all,

Our customer is facing this issue where the cursors are not closing at the database level and then the application hangs while executing queries. We tested the issue inhouse database and it worked fine by increasing the open cursor limit to 2500. But at the customer database considering the huge amount of data, the open cursors is already set to 4000 and still doesnt help. The cursor_sharing parameter is set to exact and cursor_space_for_time is set to false. Will it help if I change the cursor_sharing to similar and cursor_space_for_time to True.
Or any other suggestions?

Thanks much
Re: Cursors not closed at the database level [message #312701 is a reply to message #312698] Wed, 09 April 2008 11:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Or any other suggestions?
close the cursors!
Re: Cursors not closed at the database level [message #312707 is a reply to message #312698] Wed, 09 April 2008 11:31 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Thats the problem we are trying to solve. Cursors are not closing, not sure if the application is not the closing the cursors at the database level since the cursors seem to be still open at the database end.
Re: Cursors not closed at the database level [message #312708 is a reply to message #312698] Wed, 09 April 2008 11:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Is this a Java app? Using connection pooling?
Re: Cursors not closed at the database level [message #312710 is a reply to message #312698] Wed, 09 April 2008 11:35 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
No. Its a vision application (for publishing)
Re: Cursors not closed at the database level [message #312714 is a reply to message #312698] Wed, 09 April 2008 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Its a vision application (for publishing)
OK, this is a new term/phrase for me.
Please post a URL which better explains or describes what this is.
Re: Cursors not closed at the database level [message #312732 is a reply to message #312698] Wed, 09 April 2008 12:24 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Anacedent, its a new phrase for me too, I just joined this company. But I am just concerned at the oracle end, if there are any parameters I can set in the database to fix this problem,
which I can only think of the SESSION_CACHED_CURSORS or cursor_sharing parameters. But I think this will hardly help solve this problem.

Else I can have to leave it to the application team to figure out the problem.
Re: Cursors not closed at the database level [message #312736 is a reply to message #312698] Wed, 09 April 2008 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMO, the root cause is a Oracle hostile application.
Oracle does not spontaneously open cursors just to complicate performance.
Since the problem does not originate within the DB, neither does the fix.

Is this a 3 tier application?
Re: Cursors not closed at the database level [message #312739 is a reply to message #312698] Wed, 09 April 2008 12:41 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Yes it is a 3 tier application. I am also pretty sure it cannot be a database originated issue, but had to investigate the issue before I turn it in to the application team that its not a database problem and there are no fixes at the database end.

Thanks.
Re: Cursors not closed at the database level [message #312741 is a reply to message #312698] Wed, 09 April 2008 12:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Is http://www.hibernate.org/ involved?
Re: Cursors not closed at the database level [message #312758 is a reply to message #312698] Wed, 09 April 2008 13:10 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Do you have inline cursors?

SELECT A, B, C, CURSOR ( SELECT ...) 
FROM TAB


Michael
Re: Cursors not closed at the database level [message #312779 is a reply to message #312698] Wed, 09 April 2008 13:59 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Anacedent, I think there is jboss service running on the application server. But its not clear if it is involved in the process. Its just that the application issues command to close the cursor in the database, but its not closing at the database level and hence the process does not complete.

Michael, there is no inline cursors..
Re: Cursors not closed at the database level [message #312793 is a reply to message #312698] Wed, 09 April 2008 14:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> Its just that the application issues command to close the cursor in the database, but its not closing at the database level and hence the process does not complete.

Forgive me, but I don't accept the validity of the statement above.
If the above is true, then you should be able to create a simple test to confirm what would be a very NASTY bug.

The fact that it is only your site & application are reporting such a problem,
leads me to conclude that the problem is not Oracle malfunctioning; but your application is abusing Oracle.

Does listener.log show repeated new connection requests coming from JBOSS server(s)?

[Updated on: Wed, 09 April 2008 15:04] by Moderator

Report message to a moderator

Re: Cursors not closed at the database level [message #312794 is a reply to message #312698] Wed, 09 April 2008 15:08 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Anacedent,

I am not trying to say IT is an oracle issue. As I said it could be just the application that is not doing it correct but as a dba, I will have to see at different views and see if there is any clue at the database level.

We actually tested it out with the same process that was ran at the customer end, by reducing the max open cursor limit, just to reproduce the problem. But no luck on that. The process hung way before it hit the cursor limit. I guess at this point, the application team have to take over and look it up for any possible clues.

Thank you for your help.
Re: Cursors not closed at the database level [message #312798 is a reply to message #312698] Wed, 09 April 2008 15:25 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The process hung way before it hit the cursor limit.
Completely sloppy & imprecise statement!

ALTER SESSION SET SQL_TRACE=TRUE;
& tkprof will reveal where time is being spent!

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/

Code above might be able to identify contention problem during "hang" condition.


Does listener.log show repeated new connection requests coming from JBOSS server(s)?

[Updated on: Wed, 09 April 2008 15:26] by Moderator

Report message to a moderator

Previous Topic: Index Creation
Next Topic: Order by with Nulls -- Performance
Goto Forum:
  


Current Time: Mon Nov 04 05:18:25 CST 2024