session_cached_cursors
Baqir Hussain, December 04, 2002 - 10:57 am UTC
Tom,
Thanks for the prompt reply.
Could you please shed some light what do you mean by "after logon database"?
Each user who will logon to the system will set this value to 100 or it will be set ONLY once?
What parameter should I look into verify that
session_cached_cursors = 100 needs to decresed or increased or it is working fine.
What advantage do we get to put this trigger into spfile?
Thanks again for doing an exellent job for the oralce community.
December 04, 2002 - 1:13 pm UTC
The after login trigger fires after each and every logon -- so it will be once per session.
It'll be set ONCE per session.
100 is a good value, there has been some andedoctal evidence to show thats the point of diminishing marginal return. Just watch the increased ram in the dedicated servers.
you cannot put a trigger into an spfile. This is a bad way to set an init.ora paramter. You would put the session_cached_cursors = 100 INTO the paramter file INSTEAD of using a trigger to do an alter session for each and every logon
session_cached_cursors
Baqir Hussain, January 07, 2003 - 1:39 pm UTC
Tom,
Setting session_cached_cursors is a great help in getting more soft cursor parsing which in turns almost no latch contention.
This parameter is continuing hogging a lot of RAM. My questios are:
1. Would it be continue to have more space in RAM or it will stop hogging at some point??
2. Is there any way to find out how much memory is being used exclusively by this parameter?
3. How can we prevent to have more memory consumed by this parameter??
Looks like 2 1/2 G of memory is occupied (and increasing day by day) by this parameter.
January 08, 2003 - 2:37 pm UTC
session cached cursor is NOT a great help in getting MORE soft cursor parsing.
session cached cursors is a great help in reducing latching that takes place due to excessive soft parsing (where a program parses, executes, closes a statement over and over)
This parameter is not hogging alot of ram (tell me -- how exactly did you measure that, what is "hogging" -- quantify that for me)
2.5 gig of memory WHERE?
session_cached_cursors
Baqir Hussain, January 08, 2003 - 4:28 pm UTC
When this parameter was added into the parameter file, the ram size was "real 1500M free", "swap 2500M free" using top (unix utility). In three weeks or so memory size came to "680M free" and "swap 1300M free". I have been watching sequential increase in memory usage over this 3 weeks period of time.
You also mentioned in your reply
"Just watch the increased ram in the dedicated servers."
Is this the known fact when setting this parameter or some thing else I need to look at.
Please advise. thanks
January 08, 2003 - 6:29 pm UTC
running solaris?
solaris uses the entire set of free memory to cache disk stuff. it is normal, you cannot look at free and such and draw any conclusions.
Besides -- you have FREE memory -- you cannot put it into the bank and save it for later. Use it or lose it.
did you measure the dedicated servers? You cannot USE TOP for that -- as it'll report the sga. if you are using solaris, look at PMAP.
But -- as you have free memory (and lots of it) -- what is the problem?
pga_aggregate_target
harri, January 09, 2003 - 6:38 am UTC
If session_cashed_cursor is set at system level does pga_aggregate_target affect to memory size of sessions cursor cache?
If so, can we set session_cashed_cursor to relative big value and then limit users memory usage by pga_aggregate_target.
January 09, 2003 - 7:33 am UTC
depends -- first pga_aggregate_target is just that -- a target, not a hard limit.
second, this will increase UGA memory which is in the PGA in dedicated server mode and in the SGA in shared server mode. So, depending on how you run will affect where this memory actually lives.
andedoctal tests show that setting this about 100 or thereabouts is not useful and in fact shows diminishing marginal returns. So, bigger is not better here. It is useful as a bandage, a crutch, for an application that has a bug. The bug being that the developer slammed the cursors closed ASAP -- even though they are being used over and over and over and over again. You can use this to put a stop to the slamming close of the cursor -- so it can be reused nicely in the same session. You can use it until the developer fixes their bug. If you do not suffer from this malady -- you wouldn't be using it as it would only add overhead to normal processing (eg: you do not take anti-biotics when you are healthy, that would make you sick).
Which should be our goal session_cached_cusor =0
Juan Carlos Reyes Pacheco, March 25, 2003 - 9:50 am UTC
Hi Tom,
As I understand about session_cached_cursor, as in cursor sharing, we should look to use the = EXACT, while the other values are for fixing temporaly problems in binding.
So our goal is to fix the problems and then set the session_cached_cusor =0?
March 25, 2003 - 10:00 am UTC
No, not really as session_cached_cursor doesn't materially affect anything as cursor sharing does.
It'll help when it can.
It does nothing if it is not needed.
I can provide benefits for recursive sql (something over which you have no control) from time to time as well.
Is there a way to estimate the value for session_cached_cursor
Juan Carlos Reyes P, March 25, 2003 - 10:17 am UTC
Thanks Tom
Recently changing the value of SESSION_CACHED_CURSORS from 100 to 2000, I got a great performance improvement in a specific process. But I guessed, I tried a big amount to see what happened, this was not scientific.
Oracle documentationsays
"To determine whether the session cursor cache is sufficiently large for your instance, you can examine the session statistic session cursor cache hits in the V$SYSSTAT view. This statistic counts the number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, then consider setting SESSION_CACHED_CURSORS to a larger value."
1. Here is not very clear about when and how increase this value, what means "relatively low" . About which number or percentage specifically.
2- Could you please helpme to build a query to get it.
SELECT * FROM V$SYSSTAT WHERE NAME = 'session cursor cache hits'.....
:) Thank you
March 25, 2003 - 11:58 am UTC
well, being a despiser of ROT and RATIOs in general, I'll take a whack at this one.
You cannot come up with a meaningful ratio with the supplied statistics.
It would take:
(a) count of UNIQUE sql statements parsed <<=== don't have that anywhere
(b) number of cache hits <<=== got it.
(c) parse count <<<=== got it.
Then, we would like to see
c-a = b
That is take the total parses (c), subtract out the number of unique statements (a) we do have to parse them at least once, then we want the rest of the parsed to be cache hits (b)
But you don't know how many unique sql statements you have. If you have an application that runs 500 unique sql statements ONCE -- cursor cache hits = ZERO.
I would say -- increase while you still see positive improvement and an uptick in the cursor cache hits..
:) Thanks
A reader, March 25, 2003 - 12:05 pm UTC
As you said it seems session_cached_cursor values the same as open_cursors
Juan Carlos Reyes P, March 25, 2003 - 2:02 pm UTC
Hi Tom I'm a little confused.
You said in
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:865497961356 <code>
"
So, if open_cursors was set to 1000 and you execute a plsql routine with 1000
statments -- peek at v$open_cursor -- you'll find its chock full of open
statements. PLSQL caches them for us. Session cached cursors is useful for
environments that don't (or can't) cache the cursors (eg: DYNAMIC SQL in plql
CANNOT cache the cursor).
"
This seems the same as say:
" Is the same for you, if You use session_cached_cursor or open_cursors, if you don't use dynamic sql."
Is this right, thanks :)
March 25, 2003 - 8:18 pm UTC
huh, not following your train of thought there.
Juan Carlos Reyes P, March 26, 2003 - 10:21 am UTC
Sorry I forgot the ,etc. after dynamic in the previous question, but all is clear now.
:) Thanks.
Can't set session_cached_cursors in spfile
Kamal Kishore, May 11, 2003 - 1:45 pm UTC
Hi Tom,
I'm trying to change the value of session_cached_cursors in my spfile, but I keep getting this error:
sys @ ORA92.LAPTOP > alter system set session_cached_cursors = 100 scope = both ;
alter system set session_cached_cursors = 100 scope = both
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
sys @ ORA92.LAPTOP > alter system set session_cached_cursors = 100 scope = spfile ;
alter system set session_cached_cursors = 100 scope = spfile
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
sys @ ORA92.LAPTOP >
How to change it in the SPFILE? none of the above seems to work. I'm Running Oracle 9.2 on RedHat Linux.
Thanks,
May 11, 2003 - 2:00 pm UTC
it is fixed in 9iR2 9.2.0.3
until then,
create pfile from spfile;
edit the pfile, add it
then startup with the pfile and create spfile from pfile
interaction with cursor_sharing
Scott, July 10, 2003 - 12:45 pm UTC
We have a vendor whose scripting tools don't allow bind variables. To offset this we've created a logon trigger that alters the session to set cursor_sharing to FORCE for this particular user. Would we also benefit from giving them a non-zero number of session_cached_cursors? The docs for cursor_sharing
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch131.htm#1015593 <code>
state that "FORCE -- Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement." However, this doesn't really tell me whether there are soft parses that we can mitigate with session_cached_cursors.
Many thanks,
Scott
July 10, 2003 - 2:24 pm UTC
the two work well together actually -- if you are using one, the other almost demands to be used! Consider:
ops$tkyte@ORA920> alter session set cursor_sharing=force;
Session altered.
ops$tkyte@ORA920> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> alter session set session_cached_cursors=0;
Session altered.
ops$tkyte@ORA920> declare
2 l_cursor sys_refcursor;
3 begin
4 for i in 1 .. 10000
5 loop
6 open l_cursor for 'select * from dual where dummy = ''' || i || '''';
7 close l_cursor;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> alter session set session_cached_cursors=100;
Session altered.
ops$tkyte@ORA920> declare
2 l_cursor sys_refcursor;
3 begin
4 for i in 1 .. 10000
5 loop
6 open l_cursor for 'select * from dual where dummy = ''' || i || '''';
7 close l_cursor;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec runStats_pkg.rs_stop(100)
Run1 ran in 388 hsecs
Run2 ran in 268 hsecs
run 1 ran in 144.78% of the time
Name Run1 Run2 Diff
STAT...Elapsed Time 398 277 -121
LATCH.SQL memory manager worka 134 0 -134
STAT...redo size 65,192 64,920 -272
STAT...session cursor cache hi 1 9,999 9,998
LATCH.shared pool 30,067 10,109 -19,958
LATCH.library cache pin 40,058 20,065 -19,993
LATCH.library cache pin alloca 40,026 22 -40,004
LATCH.library cache 80,088 30,112 -49,976
STAT...session pga memory 0 65,536 65,536
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
193,684 63,495 -130,189 305.04%
PL/SQL procedure successfully completed.
Bjorn disagrees?
Mark J. Bobak, July 11, 2003 - 6:20 am UTC
Hi Tom,
Those are interesting results, particularly since they seem
to directly conflict with Bjorn Engsig's paper, available on
OTN.
A short quote:
Application category: Not using bind variables at all.
General behavior: Generally poor response time and
scalability. Is however, recommended for DSS applications,
that have little or no repitition of SQL statements.
cursor_sharing force or similar: Good improvement of
response time and scalability; although not quite as good
as re-writing the application.
session_cached_cursors sufficiently high: No extra benefit.
cursor_space_for_time=true: no effect.
He then goes through three other major categories of
application, each progressively better behaved with respect
to SQL and cursor handling, and describes which parameters
are a benefit.
However, his result clearly disagrees with yours on the
cursor_sharing combined w/ session_cached_cursors question.
Any thoughts, Tom?
July 11, 2003 - 10:30 am UTC
You would have to ask him -- perhaps he tested cursor_sharing INDEPENDENT of session_cached_cursors.
If he did -- then it would be accurate, sesssion_cached_cursors would do NOTHING since you never execute the same statement more then once.
If you use them *together*, as you can see, measurably different.
Sigh.
Scott, July 11, 2003 - 10:47 am UTC
Thank you much. So from this I conclude that the scalar replacement that cursor_sharing=FORCE imposes takes place prior to a more standard soft parse, which the session_shared_cursors then helps along.
July 11, 2003 - 11:10 am UTC
yup, it would sort of have to -- else, the benefit of the soft parse would not be there!
Just to make sure, I run this in 8i as well since "things change", just to make sure it applied in the past as well as now (it does)
ops$tkyte@ORA817DEV> alter session set cursor_sharing=force;
Session altered.
ops$tkyte@ORA817DEV> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> alter session set session_cached_cursors=0;
Session altered.
ops$tkyte@ORA817DEV> declare
2 type sys_refcursor is ref cursor;
3 l_cursor sys_refcursor;
4 begin
5 for i in 1 .. 10000
6 loop
7 open l_cursor for 'select * from dual where dummy = ''' || i || '''';
8 close l_cursor;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> alter session set session_cached_cursors=100;
Session altered.
ops$tkyte@ORA817DEV> declare
2 type sys_refcursor is ref cursor;
3 l_cursor sys_refcursor;
4 begin
5 for i in 1 .. 10000
6 loop
7 open l_cursor for 'select * from dual where dummy = ''' || i || '''';
8 close l_cursor;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> exec runStats_pkg.rs_stop(100)
Run1 ran in 459 hsecs
Run2 ran in 345 hsecs
run 1 ran in 133.04% of the time
Name Run1 Run2 Diff
STAT...Elapsed Time 462 348 -114
STAT...CPU used by this sessio 466 347 -119
STAT...CPU used when call star 466 347 -119
STAT...redo size 1,428 1,620 192
STAT...session cursor cache hi 1 9,999 9,998
LATCH.shared pool 20,097 28 -20,069
LATCH.library cache 200,138 50,115 -150,023
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
220,425 50,339 -170,086 437.88%
PL/SQL procedure successfully completed.
Tom,Where can I get runStats_pkg package?
A reader, November 27, 2003 - 10:28 pm UTC
November 28, 2003 - 10:26 am UTC
A Reader, February 28, 2004 - 1:39 pm UTC
Hi Tom,
I installed your runstats package successfully and tried your example above but I don't get any output or errors after running runStats.stop. Did I miss something?
Thanks!
test> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
test> alter session set session_cached_cursors=0;
Session altered.
test> declare
2 type sys_refcursor is ref cursor;
3 l_cursor sys_refcursor;
4 begin
5 for i in 1 .. 10000
6 loop
7 open l_cursor for 'select * from dual where dummy = ''' ||
i || '''';
8 close l_cursor;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
test> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
test> alter session set session_cached_cursors=100;
Session altered.
test> declare
2 type sys_refcursor is ref cursor;
3 l_cursor sys_refcursor;
4 begin
5 for i in 1 .. 10000
6 loop
7 open l_cursor for 'select * from dual where dummy = ''' ||
i || '''';
8 close l_cursor;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
test> exec runStats_pkg.rs_stop(100)
PL/SQL procedure successfully completed.
February 28, 2004 - 2:06 pm UTC
test> SET SERVEROUTPUT ON SIZE 1000000
A reader, March 24, 2004 - 8:35 pm UTC
Tom,
Environment is RAC 2 nodes 9.2.0.3
Middle Tier is weblogic with connection pooling.
I noticed value of soft parse is ~1.4 million and the database has been up for 4 days..
current session_cached_cursors value is set to 0. Will i benefit if i change my session_cached_cursors to 100 or more. Memory is not a issue..
Thanks.
March 25, 2004 - 8:53 am UTC
probably -- but, only if latch contention on the library cache is
a) a problem
b) your problem
c) a material part of your problem (eg: accounts for some measurable time)
If not, you won't see a thing change really.
(in order to tune, you need to find your problems and these are always found by tracing the problem/most important APPLICATIONS (not 'system'))
A reader, March 25, 2004 - 10:11 am UTC
Thanks.
cache_space_for_time
bigfan, November 10, 2004 - 3:45 pm UTC
Tom
some time ago I came upon a thread here about the
cache_space_for_time parameter should be set to TRUE;
I've tried your search engine here to find that
thread again and you do not mention it in your
EOBD book.
Could you point me to that thread or review again
here the purpose,function and value of that parameter.
thanks
November 10, 2004 - 8:21 pm UTC
no, you could not have -- for two reasons
a) there is no such parameter
b) I have no rules of thumb like that.
I believe you mean cursor_space_for_time.
correction...
bigfan, November 10, 2004 - 4:52 pm UTC
meant 'cursor_space_for_time'....found it.
apologies.
November 10, 2004 - 8:36 pm UTC
but where did I say it should be set to true?
Difference between CURSOR_SPACE_FOR_TIME and SESSION_CACHED_CURSORS
Parag Jayant Patankar, February 05, 2005 - 8:21 am UTC
Hi Tom
I have came across document which is saying
CURSOR_SPACE_FOR_TIME : if you set this parameter is true you choose to use space to gain time. shared SQL areas not aged out until cursor referencing them are closed.
SESSION_CACHED_CURSORS : this parameter helps in situation in which user repetedly parses the same statments. This occurs in Forms applications when user often swith form. all SQL statments are closed when you switch another session. Therefore any subsequent call to parse statement will bypass the parse stage.
Question : Can you elaborate the difference between two parameters in simple term and in depth because broadly both paramters seems to be doing same thing.
regards & thanks
pjp
February 05, 2005 - 9:02 am UTC
session cached cursors says "program closed cursor, but we'll keep pointing to it in the shared pool in the likely event they open the same cursor, that way we don't have to search so hard for it. it is a softer soft parse.
the cursor in the shared pool is subject to normal aging, we are just pointing to it in case"
the other says "until i close the cursor, please don't age out the sql in the shared pool, when I close it, do whatever you want. This prevents a miss in the library cache during an execute"
so, session cached cursors is just a way to reduce soft parse overhead.
the other is a way to try and avoid having to actually reparse a statement while it is still open (due to too small a shared pool perhaps, or someone else hard parsing like mad and aging stuff out)
session_cached_cursor parameter crucial for mod_plsql
James, March 17, 2005 - 1:50 am UTC
Dear Tom,
You say that setting the session_cached_cursor parameter is a crutch for poor applications.
What about mod_plsql applications? The session are automatically reset when the request is completed.
Hence, there is no way to reduce CPU used in soft parsing via softer soft parsing unless we use this parameter.
Wouldn't setting this parameter be *recommended* in this instance?
Regards,
James
March 17, 2005 - 8:33 am UTC
and so session_cached_cursors does nothing for them....
the session is *reset*
doh
James, March 17, 2005 - 6:29 pm UTC
So setting session_cached_cursors is actually totally useless for a mod_pldql environment!
I guess I was after a panacea for our "sick" mod_plsql application - looks like softer soft parsing is not going to do it for me.
Here are some top level Performance Manager stats:
Executes without parses % 3.19
Soft Parse % 98.17
Memory Sort % 100
Can you tell from these brief stats that out application parsing is good?
Cheers
March 17, 2005 - 6:34 pm UTC
how is performance?
performance
James, March 17, 2005 - 7:26 pm UTC
>how is performance
Performance is mostly ok - occasioanly some nasty sql gets run from a reporting application - but we try to fix this when it occurs.
I think we can do much better with our table statistic gathering - I'm currently reasearchin on your website.
Can anything useful be gleaned from Performance Manager?
(Data taken during light/moderate usage)
Execution Rates
Logons/Sec 2.266667
Transactions/Sec 18.066667
User Calls/Sec 102.066667
Application Efficiency
Executes w/o Parses % 2.693776
Soft Parse % 98.207467
Memory Sort % 100.0
Total Table Scans/Sec 11.0
Memory Efficiency
Buffer Cache Hit % 99.904476
Library Cache Hit % 99.223009
% Shared Pool Free 2.806821
Buffer Cache Efficiency
Block Gets/Sec 406.0
Cons. Gets/Sec 70228.933333
Block Changes/Sec 300.6
Cons. Changes/Sec 31.466667
Application IO
Physical Reads/Sec 67.466667
Phys Reads Direct/Sec 0.0
Phys Writes Direct/Sec 0.0
Background IO
Physical Writes/Sec 4.466667
Redo Writes/Sec 16.4
Redo Size/Sec 71862.4
Sessions
Active 20
Inactive 388
Top Waits by Time Waited
control file parallel write 0.07
latch free 0.13
db file sequential read 0.14
log file parallel write 1.5
log file sync 3.55
Regards
March 17, 2005 - 7:28 pm UTC
only think that "jumps" out is the soft parse % which is the only ratio I believe should be over 99. That your shared pool is so full -- might indicate you have a query or two missing binds (but your library cache hit is ok)
ratios hide so much data though, it is hard to say what they really mean.
thanks
James, March 17, 2005 - 7:49 pm UTC
Thanks for your thoughts Tom.
I simply have no confidence that our application/db is running as effeciently as it could. I just have to roll up my sleves and start properly analyzing the entire system.
Ultimatly though, user feedback is the benchmark - if users think it is ok - it is ok - so at the moment things are ok :-)
It's the future I'm worried about :-/
Thanks again.
March 17, 2005 - 8:03 pm UTC
suggestion:
instrument the code
have it be able to report "response time" (i do, all html db applications do). I can tell if the database is returning stuff "faster than fast enough" at a glance.
instruments?
James, March 17, 2005 - 8:21 pm UTC
How do I "instrument the code?"
Is that question similar to "What is Oracle?" :-)
Appologies in adavance...
March 17, 2005 - 8:24 pm UTC
instrument -- like sql_trace=true...
like the v$ tables...
at the begining of each mod_plsql routine, make it a habit to "insert into log_table values ( important information....., dbms_utility.get_time ) returning rowid in l_rowid"
at the end:
update log_table set end_time = dbms_utility.get_time where rowid = l_rowid;
commit;
Now, you know:
a) what routines are popular
b) what are not
c) what take the longest
d) how long they have taken in the past
e) if they are going slower over time
f) what isn't popular anymore
g) if your application is being used
and if you can turn on sql_trace=true via a parameter for each routine -- that'd be great too....
session_cached_cursors
A reader, October 11, 2006 - 6:32 pm UTC
can session_cached_cursors=0 cause ORA-4031?
thanks
October 11, 2006 - 8:17 pm UTC
no, but setting session cached cursors really HIGH could contribute to it.
Getting ORA-600 error for setting session_cached_cursors
Kulkarni.B.M, October 19, 2006 - 10:11 am UTC
We are getting ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [729], [8360], [SPACE LEAK], [], [], error for a database. Initially oracle recommended to set a trace parameter which will ignore memory leakage less than 4000. But we continue to get the same error even after putting this parameter.
Now Oracle Support says we need to do away with session_cached_cursors parameters. Currently value is set to 100.
Will you please shed some light on this?. Will this parameter lead to memory leakage?. What is the solution for this?.
October 19, 2006 - 2:02 pm UTC
please work with support on internal errors.
How to see cursors being aged out before EXECUTE phase
Sachin, January 12, 2007 - 4:47 am UTC
Tom,
I know setting cursor_space_for_time can hv significant issue on shared pool space.
But in which case you suggest setting this parameter.
And how do i know -> "How to see open cursors being aged out before EXECUTE phase"? if the number of misses are too high, then we might consider setting this parameter along with increasing the size of shared pool. isnt it?
high value of session_cached_cursors
Florin, February 02, 2007 - 5:03 am UTC
Dear Tom,
I saw in one of our projects the session_cached_cursors is set to 3000 !!!
I've asked the previous DBAs who worked before on this project but no one can remember why this param was set to such a high value (3000). Would you please let me know how to check if indeed we need 3000 or I can simply reduce to 100?
I saw in the ixora site the following script and when I ran it I got the following:
select
max(count(*)) max_cacheable_cursors
from
( select
p.kglobt18 schema# -- parsing schema number
from
sys.x$kglcursor p
where
p.kglobt12 > 2 -- enough parse_calls
union all
select
s.kglntsnm schema# -- authorized schema number
from
sys.x$kglcursor c,
sys.x$kglsn s
where
c.kglobt12 > 2 and
s.kglhdadr = c.kglhdadr
)
group by
schema#
/
MAX_CACHEABLE_CURSORS
---------------------
1906
Would you please advise?
Many thanks in advance!
February 02, 2007 - 10:50 am UTC
3,000 could be negatively impacting performance. If the client has lots of cached cursors (more memory allocated), everytime they parse - they have more stuff to look through.
I would say it is doubtful you need more than 100 or so. Bear in mind this also affects the size of the PLSQL cursor cache, PLSQL will hold open up to session cached cursors (from 9.2.0.5 on, before that, open_cursors controlled this).
cursors or queries?
A reader, July 28, 2007 - 6:40 am UTC
Hello,Mr. Tom!
Could you kindly give me an explain on:
What difference between CURSOR and SQL STATMENT?
one cursor=one sql statment¿
I¿am so confused with it.
Regards!
Alan
July 28, 2007 - 9:28 am UTC
A cursor is like a 'handle', a pointer.
you can open a cursor (dbms_sql.open_cursor for example), then later you associate a SQL statement with it - dbms_sql.parse( 'select ....' ) )
A cursor will point to a single SQL statement at a time, and it is your way as a programmer to access that sql statement, to provide inputs to it, to read outputs from it.
think of a cursor like you might a FILE HANDLE. You can read and write from them - and they point to a single file at a time.
cursor sql
A reader, July 28, 2007 - 10:47 am UTC
Cool~~~~
Great Tom!
Shared pool fragmentation
Deepak, August 30, 2007 - 1:36 pm UTC
Hi Tom,
Thanks a lot for your nice explanation. Got a doubt after reading few experts' articles on SESSION_CACHED_CURSORS parameter.
According to you if this parameter is set then a pointer to the closed cursor is maintained in the sessions memory. And the cursor area in the shared pool is subject to reuse. But few articles suggest that if we set this parameter we may increase the chances of getting our shared pool fragmented because of this. Is it correct? If yes how?
Please help.
September 04, 2007 - 5:47 pm UTC
the sql can age out, it is just pointed to and if it isn't there, so be it.
Now, it does control also the number of cached cursors PLSQL keeps open as well. that could cause some sql to not be as flushable - which in a very stressed system could lead to an ora-4031.
However, I would NOT turn this off to avoid the 4031, fix the problem (you have a hard parse problem on that system - undoubtedly) - do not further reduce your performance and not fix the problem.
Where is the PLSQL cursor cache then?
Sean, March 04, 2010 - 9:16 am UTC
Hi Tom,
My customer is using 9208 database. You said from 9205 onwards, SESSION_CACHED_CURSORS also affects the size of PLSQL cursor cache. Could you let me know where is the PLSQL cursor cache? Is it separated from OPEN_CURSORS? Coz I am told prior to 9205, OPEN_CURSORS was used as a cache of PLSQL cursors. If this is still the case in 9208, should I also up OPEN_CURSORS after increasing SESSION_CACHED_CURSORS?
Thanks,
Sean
March 04, 2010 - 10:06 am UTC
it is part of your session, In the UGA
coz? what is Coz?
The plsql cursor cache has and will be a subset of the open_cursors set of open cursors you may have. BUT - it is a very "safe and nice cache"
Meaning, if you set open_cursors to 100 and session_cached_cursors to 100, then plsql will cache up to 100 sql statements. Let's say plsql has 100 cached right now (so 100 open cursors in your session). Further, let's say your application tries to open a cursor - you have 100 used by plsql - you want 1 more for your application - but the open cursors is set to 100.
We don't fail, we don't raise an error - rather we ask plsql to close one - and it will, silently, transparently, in the background. So, you have your 100 cursors to use as you see fit - and if you are not using some of them - PLSQL will use them to cache. If you are using all of them - PLSQL will not.
Thanks a lot! Does 'opened cursors current' in v$sesstat include PLSQL cached cursors?
Sean, March 05, 2010 - 3:30 am UTC
Sorry Tom. I won't use "coz" any more.
If PLSQL use a subset of OPEN_CURSORS, then 'opened cursors current' statistic value in v$sesstat includes PLSQL cached cursors or not? If not, is there anyway to know the number of PLSQL currently cached cursors? If yes, how can we acquire the real number of opened cursors?
Thanks again,
Sean
March 05, 2010 - 5:55 am UTC
ops$tkyte%ORA11GR2> create or replace function get_stat_val( p_name in varchar2 ) return number
2 as
3 l_val number;
4 begin
5 select b.value
6 into l_val
7 from v$statname a, v$mystat b
8 where a.statistic# = b.statistic#
9 and a.name = p_name;
10
11 return l_val;
12 end;
13 /
Function created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure p
2 as
3 begin
4 for x in (select * from dual d1) loop null; end loop;
5 for x in (select * from dual d2) loop null; end loop;
6 for x in (select * from dual d3) loop null; end loop;
7 for x in (select * from dual d4) loop null; end loop;
8 for x in (select * from dual d5) loop null; end loop;
9 for x in (select * from dual d6) loop null; end loop;
10 for x in (select * from dual d7) loop null; end loop;
11 for x in (select * from dual d8) loop null; end loop;
12 for x in (select * from dual d9) loop null; end loop;
13 for x in (select * from dual d10) loop null; end loop;
14 for x in (select * from dual d11) loop null; end loop;
15 for x in (select * from dual d12) loop null; end loop;
16 for x in (select * from dual d13) loop null; end loop;
17 for x in (select * from dual d14) loop null; end loop;
18 for x in (select * from dual d15) loop null; end loop;
19 for x in (select * from dual d16) loop null; end loop;
20 for x in (select * from dual d17) loop null; end loop;
21 for x in (select * from dual d18) loop null; end loop;
22 for x in (select * from dual d19) loop null; end loop;
23 for x in (select * from dual d20) loop null; end loop;
24 for x in (select * from dual d21) loop null; end loop;
25 for x in (select * from dual d22) loop null; end loop;
26 for x in (select * from dual d23) loop null; end loop;
27 for x in (select * from dual d24) loop null; end loop;
28 for x in (select * from dual d25) loop null; end loop;
29 for x in (select * from dual d26) loop null; end loop;
30 for x in (select * from dual d27) loop null; end loop;
31 for x in (select * from dual d28) loop null; end loop;
32 for x in (select * from dual d29) loop null; end loop;
33 for x in (select * from dual d39) loop null; end loop;
34 end;
35 /
Procedure created.
ops$tkyte%ORA11GR2> exec p
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select get_stat_val('opened cursors current') open_cur_curr,
2 get_stat_val('session cursor cache hits') cur_cache_hits,
3 get_stat_val('session cursor cache count') cur_cache_count,
4 get_stat_val('parse count (total)') parse_cnt_tot,
5 get_stat_val('parse count (hard)') parse_cnt_hard
6 from dual;
OPEN_CUR_CURR CUR_CACHE_HITS CUR_CACHE_COUNT PARSE_CNT_TOT PARSE_CNT_HARD
------------- -------------- --------------- ------------- --------------
3 137 48 83 2
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> select get_stat_val('opened cursors current') open_cur_curr,
2 get_stat_val('session cursor cache hits') cur_cache_hits,
3 get_stat_val('session cursor cache count') cur_cache_count,
4 get_stat_val('parse count (total)') parse_cnt_tot,
5 get_stat_val('parse count (hard)') parse_cnt_hard
6 from dual;
OPEN_CUR_CURR CUR_CACHE_HITS CUR_CACHE_COUNT PARSE_CNT_TOT PARSE_CNT_HARD
------------- -------------- --------------- ------------- --------------
3 2 16 19 0
ops$tkyte%ORA11GR2> exec p
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select get_stat_val('opened cursors current') open_cur_curr,
2 get_stat_val('session cursor cache hits') cur_cache_hits,
3 get_stat_val('session cursor cache count') cur_cache_count,
4 get_stat_val('parse count (total)') parse_cnt_tot,
5 get_stat_val('parse count (hard)') parse_cnt_hard
6 from dual;
OPEN_CUR_CURR CUR_CACHE_HITS CUR_CACHE_COUNT PARSE_CNT_TOT PARSE_CNT_HARD
------------- -------------- --------------- ------------- --------------
3 10 47 53 0
ops$tkyte%ORA11GR2> exec p
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select get_stat_val('opened cursors current') open_cur_curr,
2 get_stat_val('session cursor cache hits') cur_cache_hits,
3 get_stat_val('session cursor cache count') cur_cache_count,
4 get_stat_val('parse count (total)') parse_cnt_tot,
5 get_stat_val('parse count (hard)') parse_cnt_hard
6 from dual;
OPEN_CUR_CURR CUR_CACHE_HITS CUR_CACHE_COUNT PARSE_CNT_TOT PARSE_CNT_HARD
------------- -------------- --------------- ------------- --------------
3 49 47 57 0
ops$tkyte%ORA11GR2> exec p
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select get_stat_val('opened cursors current') open_cur_curr,
2 get_stat_val('session cursor cache hits') cur_cache_hits,
3 get_stat_val('session cursor cache count') cur_cache_count,
4 get_stat_val('parse count (total)') parse_cnt_tot,
5 get_stat_val('parse count (hard)') parse_cnt_hard
6 from dual;
OPEN_CUR_CURR CUR_CACHE_HITS CUR_CACHE_COUNT PARSE_CNT_TOT PARSE_CNT_HARD
------------- -------------- --------------- ------------- --------------
3 88 47 61 0
the real number of opened cursors is in fact 3, yes, plsql has them cached - but they don't count against you, so we don't count them.
you can see *potentials* in the cursor cache cnt - but it isn't directly mapped to just plsql remember, the entire session not just plsql uses that.
Mostly, you'll see it in the "missing parses", the parses that never are - the only good parses - the ones we skip.
Session_cached_cursors...
Baskar.l, August 14, 2010 - 11:04 am UTC
hi Tom,
SQL> L
1 select 'session_cached_cursors' parameter, lpad(value, 5) value, used,
2 decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
3 from ( select max(s.value) used from V$STATNAME n, V$SESSTAT s
4 where n.name = 'session cursor cache count' and s.statistic# = n.statistic# ),
5 ( select value from V$PARAMETER where name = 'session_cached_cursors' )
6 union all
7 select 'open_cursors', lpad(value, 5), used, to_char(100 * used / value, '990') || '%'
8 from ( select max(sum(s.value)) used from V$STATNAME n, V$SESSTAT s
9 where n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic# group by s.sid ),
10* ( select value from V$PARAMETER where name = 'open_cursors' )
SQL> /
PARAMETER VALUE USED USAGE
---------------------- ----- ---------- -----
session_cached_cursors 1000 3432 343%
open_cursors 1500 3434 229%
2 rows selected.
Why the percentage are over 100 here, and the value used is more than the value specified!!!!
thanks,
baskar.l
August 19, 2010 - 12:50 am UTC
dump more detailed information, look at the session reporting this number.
in short, check out that info from v$sesstat - see who it belongs to and that'll probably lead us somewhere.
and of course - things like versions and OS and whatever always helps.
Cursors
baskar.loganathan, August 20, 2010 - 10:20 pm UTC
Hi Tom,
Thanks for the update and here is the information
DB version is 10.2.0.4
SQL> @cursors
PARAMETER VALUE USED USAGE
---------------------- ----- ---------- -----
session_cached_cursors 1000 3093 309%
open_cursors 1500 3095 206%
2 rows selected.
SQL> select * from (
select s.sid, s.value, dense_rank ()over ( partition by s.statistic# order by s.value desc) rnk
from V$STATNAME n, V$SESSTAT s
where n.name = 'session cursor cache count' and s.statistic# = n.statistic#
) where rnk <6
order by rnk
/ 2 3 4 5 6 7
SID VALUE RNK
----- ---------- ----------
1548 3095 1
587 1000 2
1324 1000 2
1452 1000 2
1520 1000 2
1588 1000 2
1553 1000 2
1394 1000 2
1306 1000 2
1234 1000 2
1264 1000 2
976 1000 2
1514 999 3
677 999 3
1206 999 3
1208 999 3
1463 999 3
1471 999 3
977 999 3
775 999 3
749 999 3
1531 955 4
1461 939 5
23 rows selected.
SQL> @sid
Enter value for sid: 1548
old 3: where sid = '&sid'
new 3: where sid = '1548'
SID SNO OSUSER USERNAME STATUS PROCESS PADDR SPID MODULE
----- ------ ---------- --------------- -------- ------------ ---------------- ------------ ----------------------------------------
1548 1 osteelp SYS INACTIVE 29643 000000057E8F8068 320 racgimon@jswbdbp17.jsw.in (TNS V1-V3)
1 row selected.
SQL>
SQL> /
Enter value for sid: 587
old 3: where sid = '&sid'
new 3: where sid = '587'
SID SNO OSUSER USERNAME STATUS PROCESS PADDR SPID MODULE
----- ------ ---------- --------------- -------- ------------ ---------------- ------------ ----------------------------------------
587 1275 astlprd APPS INACTIVE 19983 000000057B938770 22329 fnd.framework.navigate.server.OANavigate
PortletA
1 row selected.
SQL> /
Enter value for sid: 1324
old 3: where sid = '&sid'
new 3: where sid = '1324'
SID SNO OSUSER USERNAME STATUS PROCESS PADDR SPID MODULE
----- ------ ---------- --------------- -------- ------------ ---------------- ------------ ----------------------------------------
1324 32559 osteelp SYS ACTIVE 25538 000000057E90FBE8 25538 DBMS_SCHEDULER
1 row selected.
thanks,
baskar.l
August 25, 2010 - 1:06 pm UTC
well, individual sessions can change session-cached-cursors - that is easy with an alter session - so we'll assume that is the cause of that.
and recgimon is part of the clusterware stuff (internal process) so, unless that is causing some operational issue (consuming increasing amounts of memory for example) - from what I've read - it is OK.