| Cursors not closed at the database level [message #312698] |
Wed, 09 April 2008 11:14  |
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 #312732 is a reply to message #312698] |
Wed, 09 April 2008 12:24  |
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 #312739 is a reply to message #312698] |
Wed, 09 April 2008 12:41 |
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 #312779 is a reply to message #312698] |
Wed, 09 April 2008 13:59 |
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 |
 |
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 |
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 |
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
