Hi Tom,
I am trying to understand concept of open_cursors in Oracle 9.2. Thanks for your excellent answer in this thread.
Can you pl tell me
Q1. How to arrive the good paramter ( optimum parameter ) of open_cursors ?
Q2. Suppose I set up the value overboard that is 1,000,000 for this parameter ( you said not to set this value overboard in this question ) ? Where it will make a problem ?
regards & thanks
pjp
q1) it'll be at least one more than your applications actually use.
Meaning, you need to look to your applications to answer this.
q2) It'll allocate an array of 1,000,000 cursor slots at connect time (memory). It'll let a buggy application leak cursors like mad for a long time before blowing up (eating ram, consuming resources).
where wouldn't it make a problem would be a better question
Tom,
Quite confusing!!!
On one hand you are saying "It should be noted that OPEN_CURSORS simply allocates a fixed number of slots but does not allocate memory for these slots for a client"
On the other hand:
¿q2) It'll allocate an array of 1,000,000 cursor slots at connect time (memory). It'll let a buggy application leak cursors like mad for a long time before blowing up (eating ram, consuming resources).¿
Please explain the same.
Suraj Sharma
My apology for not being clear in this:
I was actually talking about memory allocation. On one statement you said that " OPEN_CURSORS simply allocates a fixed number of
slots but does not allocate memory for these slots for a client" ON THE OTHER HAND "It'll allocate an array of 1,000,000 cursor slots at connect time (memory). It'll let a buggy
application leak cursors like mad for a long time before blowing up (eating ram, consuming resources)."
I am sorry if I could not understand this properly.
do you understand how much memory an array with 1,000,000 slots would consume.
it would consume at least 1,000,000 bytes of storage and if it were an array of pointers - between 4,000,000 and 8,000,000 bytes of storage. If it were an array of a more complex structure - it would consume even more.
1,000,000 of ANYTHING is big.
we allocate a fixed sized array of something. If you set open cursors to a reasonable number, this is not a problem. If you set it to a wildly high number - well - think about it...
Hi Tom,
Does this parameter have an impact on performance? If we set it too small, Oracle may throw an error when an application tries to open too many cursors. If we set it too high, it will let buggy applications get away with not managing cursors properly.
I have this doubt because I am trying to find out why a file generation program (extract dta from Oracle db) written in c++ is taking a few minutes on one machine and an hour on another. The same db dump was applied on both machines. So data content, structures are same. Both machines have same configuration (RAM, CPU)etc. On comparing the parameters, open_cursors is 1000 on one and 2000 on the other. Would that cause a performance difference?
the down side of setting large will be increased client and server memory usage (eery cursor you have opened takes memory) and possibly increased cursor opening time as we have more slots to look through to find an empty one.
1000 to 2000 would not cause "hours of difference"
run the application with dbms_monitoring on (with wait event tracing) and tkprof the results. You will discover instantly "what is different" - if anything - at the database level by comparing those reports.
Dear TOM,
I also got the same error when I run one JAVA based application which make huge updates and insertions in database.
I checked the default cursor value as follow:
sql>Sho parameter open_cursors;
It is displayed as 300
I have change the parameter using the below query:
alter system set open_cursors=500 scope=both;
system altered.
The value is changed but I am facing the same error:
Open cursor exceeds.
Please suggest me on this.I will thankful to you.
Thanks
you are leaking cursors, your program has a serious bug. Your program is almost certainly corrupting data in the database.
Here is what I think is happening (99.99999999999% sure on this, I see it so many times....)
you open a cursor
you hit an error
you fly over the code that would have closed the cursor
your exception block HIDES the error (you are swallowing exceptions - google up that term and read and read and read about it)
you return from your method - and no one knows that you failed at your task
you just leaked a cursor, you cannot reach it anymore, it is out of scope.
While your program is running, query v$open_cursor every now and then and display all of the duplicate sql your session has open - that'll give you a clue where the problem is in your code.
And then remove all of your exception blocks that swallow exceptions so that your program just DIES with unhandled exceptions (that is something you should do, just let it DIE) - or fix them to
a) release resources
b) re-raise the exception
c) die
You cannot survive exceptions when you have no clue what they are about.
Actually the java 11 drivers do not actually close a cursor on serverside if you does that on the client.
It seems that Oracle has choosen to fix the cursor reuse problem on the driver side. Our application now requires well over 1000 open cursors as we have this many different statements overall.
So prepare the server resources for your needs is what I found as suggestion.
Hi,
We have just upgraded Oracle from version from 11.2.0.2 to 11.2.0.4.
As part of upgrade the values of few parameters got changed.
Parameter Name Post Prior to upgrade
----------------------------------------------------
optimizer_secure_view_merging ->FALSE -> TRUE
open_cursors ->1500 ->500
session_cached_cursors ->300 ->50
job_queue_processes ->128 ->32
processes ->800 ->300
_cursor_features_enabled ->MISSING ->10
Now one of our processes which use Oracle Queues, has got performance degradation.
But now when we change values of few parameters back to the original one i.e. previous version, the process delivers as per SLA. The following parameters are impacting the process
1. Without setting value of _cursor_features_enabled as 10, it doesn’t work i.e response is very slow. So this has become must for us.
2. Either of following combinations work
->a. Processes = 800 and open_cursors = 500
->b. Processes = 1500 and open_cursors = 300
Regarding _cursor_features_enabled, perhaps there was one bug in Oracle. As solution of that a patch along with setting some value of this parameter solves the issue.
Could you help me in understanding how this change is impacting performance?
• Reducing value of process to 800 from 1500
• Reducing value of open_cursors to 300 from 500
Apologies for typos.
Regards,
Rohit
