Home » RDBMS Server » Performance Tuning » open cursors (10.2.0.1.0)
open cursors [message #376878] Fri, 19 December 2008 00:43 Go to next message
grpatwari
Messages: 257
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 #376879 is a reply to message #376878] Fri, 19 December 2008 00:45 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try to close the cursors before you exit the program.
Re: open cursors [message #376888 is a reply to message #376878] Fri, 19 December 2008 01:02 Go to previous messageGo to next message
grpatwari
Messages: 257
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 #376890 is a reply to message #376888] Fri, 19 December 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Implicit cursor are automatically closed when you go out of their scope.
It is more likely you use explicit cursor in your package or statement in Java you don't close.

Regards
Michel
Re: open cursors [message #376891 is a reply to message #376888] Fri, 19 December 2008 01:06 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 Go to previous messageGo to next message
grpatwari
Messages: 257
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 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 Go to previous messageGo to next message
grpatwari
Messages: 257
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 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 #376937 is a reply to message #376878] Fri, 19 December 2008 04:00 Go to previous messageGo to next message
grpatwari
Messages: 257
Registered: June 2008
Location: Hyderabad
Senior Member
Thanq Frank.

I will check with the code.
Re: open cursors [message #376939 is a reply to message #376937] Fri, 19 December 2008 04:05 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 Go to previous messageGo to next message
grpatwari
Messages: 257
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?
Re: open cursors [message #376955 is a reply to message #376953] Fri, 19 December 2008 04:27 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Because your program is a mess! I guess you will have to spend some time to debug it.
Previous Topic: Identifying trace files and how to retrieve from the server (merged)
Next Topic: poor performance of Exchange partition on composite range-hash tables
Goto Forum:
  


Current Time: Sat Dec 10 01:22:38 CST 2016

Total time taken to generate the page: 0.28105 seconds