| open cursors [message #376878] |
Fri, 19 December 2008 00:43  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi,
I am using PL/SQL packages for data manipulataion etc. I am calling these pacakges from Java.If run the packages from Java, my SQL cursors(select stmts) are still open after completion of my task.So I am not able send more requests.My requests are failing some point of time.
My Sessions are not closing after completion of my task.
Please advice to improve the performance.
Regards,
Gajanan
|
|
|
|
|
|
| Re: open cursors [message #376888 is a reply to message #376878] |
Fri, 19 December 2008 01:02  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Thnq for quick response.
Actually I am not using explicit cursors in the packages. I am using Implicit cursors and I am commiting from Java.
Why the cursors are not closing?
How to close the cursors and How to tune the packages in Oracle?
Please advice.
Regards,
Gajanan
|
|
|
|
|
|
| Re: open cursors [message #376891 is a reply to message #376888] |
Fri, 19 December 2008 01:06 |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
|
Well it should close by itself. However, if the program terminates abruptly it may not get a chance to clean-up after itself. What are the symptoms? Do you get ORA-01000 errors?
|
|
|
|
| Re: open cursors [message #376909 is a reply to message #376878] |
Fri, 19 December 2008 02:12 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
You are right, implicit cursors will close automatically after issues commit but it is not closing some point of time.
After some point of time, all the requests to my package are failing.
I am using the folllowing for checking open cursors.Is it right to see the open cursors.
Please advice.
select USERNAME, USER#, OWNERID, STATUS, SERVER, SCHEMA#, OSUSER, PROGRAM, SQL_TEXT
from v$session , v$open_cursor
where v$session.sid = v$open_cursor.sid
and USER_NAME='BAM'and STATUS = 'INACTIVE';
Please let me know How to tune my pacakges?
|
|
|
|
| Re: open cursors [message #376914 is a reply to message #376909] |
Fri, 19 December 2008 02:42 |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
|
What do you mean by "all the requests to my package is failing"? Do you get Oracle errors? After the Java program exists, can you still see its session on the DB?
|
|
|
|
| Re: open cursors [message #376923 is a reply to message #376878] |
Fri, 19 December 2008 03:13 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
I am using collections in the packages that means I am getting more than one record from Java.So I have to process those records.I am getting proper records and I am processing properly but if more records comes from java and it is failing becoz of open cursors.
If I will get less records also still I am seeing open cursors after exiting from Java.
I am seeing the opened cursors in the DB and I am not getting oracle errors.
Why the cursors are not closing?
I am using following paramters.
open_cursors=500
Processes=800
Sessions=850
|
|
|
|
| Re: open cursors [message #376929 is a reply to message #376923] |
Fri, 19 December 2008 03:37 |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
| Quote: | I am not getting oracle errors
|
So, why do you think cursors are to blame? Maybe you should rather refine the program's error handlers.
|
|
|
|
|
|
| Re: open cursors [message #376939 is a reply to message #376937] |
Fri, 19 December 2008 04:05 |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
Just a last couple of remarks:
1) If you do get ORA-01000 errors, just increase your open_cursors parameter (your value is relatively small).
2) Your v$open_cursors query doesn't return what you think it should. Remember that Oracle can mark a cursor as "closable" and actually keep it open for later reuse.
Best of luck.
Frank
|
|
|
|
| Re: open cursors [message #376953 is a reply to message #376878] |
Fri, 19 December 2008 04:24 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
One more thing Frank, i missed one point here is...
After some point of time, connection is lost from java to db.
Why it is happening?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
