How to do soft soft parse in java
Zoran Martic, August 26, 2002 - 9:19 am UTC
Hi Tom,
I have this situation. We are using java with JDBC connections to the database (8i and 9i). We are managing a pool of connections to the database (we are not using MTS), 8 connections are created at the startup and many more when the need arise (if there are more then 8 active transactions at the moment).
How to have soft soft parse in this case, at least with these static 8 connections (99% percent of work is done in them).
Because we are using connections in some circular order I need to know for which session I have prepared (cached) statement.
What is your way of solving this issue?
How to elegantly prepare a statement for every session I use?
How to know do I have a statement prepared for some particular session?
I know that this is java related issue but maybe you have seen something like this.
Thanks in advance.
Regards,
Zoran
August 26, 2002 - 10:04 am UTC
You know -- if you move 99% of your SQL into stored procedures, this'll be done for you automagically. PLSQL will do that -- then all you have to worry about is the significantly FEWER plsql blocks you have to parse.
And those, I suppose you could attach them to your connection with a hash table or something similar. That is -- when you grab a connection, you get a hash table with "named" prepared statements in them (instead of just a connection, you get a connection and a set of statements waiting to be called -- if you don't find a statement in the hash table, you'll prepare it and add it yourself.
How to do soft soft parse in java
Zoran Martic, August 26, 2002 - 12:38 pm UTC
Hi Tom,
Thanks a lot.
As I wondered there is not any magic method there.
One question more if it is not the problem.
I am using weak ref cursors in PL/SQL without closing them (in Oracle documentation is possible to find that you do not need to close weak ref cursors because it is done automatically).
Also I am reusing these cursors in the code without close before or after.
I have seen many your examples with explicit close (as I always thinking is good method).
What is the difference, if any, if you close or not close ref cursor in PL/SQL and reuse it again many times (speed, latches, memory, ...)?
Thanks in advance.
Regards,
Zoran
August 26, 2002 - 1:30 pm UTC
Well, lets see -- if you close the cursor, you won't hit "max open cursors" at some date in the future.
close em, close em all when they are done. just close them.
Ref cursors - the last frontier
Zoran Martic, August 27, 2002 - 3:38 am UTC
Hi Tom,
Thanks a lot Tom.
I am dealing with four weak ref cursors.
Open many times (I am 99% sure more times then I have open_cursors) never close (Oracle suggest that you do not need).
As you said that means I only need to close these four at the end because probably when I open the same ref cursor again the Oracle code will automatically reuse memory (and who knows what) that belongs to this cursor (is it maybe faster in this case because I will have some of the infrastructure already in place if I do not close, just open again). Maybe it is only 9i, who knows.
Thanks for you patience. I am only curious because nobody from Oracle never discuss in public this "recommended" way of dealing with ref cursors.
Thanks in advance.
"Sometimes I ask myself why do you need things like that!"
Regards,
Zoran
Ref cursors - the last frontier
Zoran Martic, August 29, 2002 - 10:57 am UTC
Hi Tom,
Finally I did some tests based on your runstats.sql script.
I used this:
declare
l_start number;
-- add any other variables you need here for the test...
begin
delete from run_stats;
commit;
-- start by getting a snapshot of the v$ tables
insert into run_stats select 'before', stats.* from stats;
-- and start timing...
l_start := dbms_utility.get_time;
-- for things that take a very small amount of time, I like to
-- loop over it time and time again, to measure something "big"
-- if what you are testing takes a long time, loop less or maybe
-- not at all
for i in 1 .. 1000
loop
-- your code here for approach #1
essent_correlate.correlate_per_switch('HGL');
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
-- get another snapshot and start timing again...
insert into run_stats select 'after 1', stats.* from stats;
l_start := dbms_utility.get_time;
for i in 1 .. 1000
loop
essent_correlate2.correlate_per_switch('HGL');
-- your code here for approach #2
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
insert into run_stats select 'after 2', stats.* from stats;
commit;
end;
/
I used PL/SQL packages as you can see. The first one with this scenario:
loop
open weak cursor; -- for different queries with binds
....
close weak cursor;
end loop;
and the second:
loop
open weak cursor; - for different queries with binds
end loop;
close weak cursor
The code is much more complex then that, with 3 weak cursors in loops, with some processing without commit and rollback inside. But just to be consistent I did this tests with running PL/SQL in loop 10, 100 and 1000 times in Oracle817 and Oracle9iR2. And also change the position for what is going first as:
1. essent_correlate2.correlate_per_switch('HGL'); -- first
essent_correlate.correlate_per_switch('HGL'); -- second
and
2. essent_correlate.correlate_per_switch('HGL');
essent_correlate2.correlate_per_switch('HGL');
All statistics were pretty similar except:
STAT...opened cursors cumulative
STAT...recursive calls
and of course execution time.
The conclusion is:
If you want to reuse (weak) ref cursors each time, it is faster 2-5% to open the cursor again without closing them
but you can see the difference only if you are reopening ref cursors so many times as I need in my batch processing.
The test was on 1000 times less data rows in tables (2000 times less reopening).
********************************************************
It is not too much (1-5%) on big number of reopening.
What do you think Tom, is it worth it at all, because you can gain more percentage on other things.
Regards,
Zoran
August 29, 2002 - 7:52 pm UTC
I would feel better closing the cursor (not seeing your code).
That way I know I don't have a leak.
Also, i code procedures NO LARGER then the height of my monitor, which effectively limits me to 60-80 lines of code / procedure. So, I would probably have had many procedures where you did your stuff inline (eg: i would not have been ABLE to reuse the cursor! it wouldn't have fit, i would have had a loop that called proc1, proc2, proc3, proc4, ... )
Ref cursors - the last frontier
Zoran Martic, August 30, 2002 - 4:16 am UTC
Hi Tom,
Thanks for the answer.
I agree with you 100%, why to risk a memory leak as probably the most common thing in all softwares around the world for 2% gain.
"THE ONLY THING WHY I DID THAT IN MY FIRST RELEASE WAS
ORACLE RECOMMENDED - YOU DO NOT NEED TO CLOSE REF CURSOR IF YOU ARE OPENING AGAIN"
(it was in Oracle docs or maybe Metalink note).
I am exactly doing what are you talking about (one of the basic programming rules, short methods):
open c1
loop
proc1(c2,bind1,bind2)
proc2(processing....)
proc3(c3,bind1,bind2)
proc4(processing....)
proc5(finishing)
close c2
close c3
end loop
close c1
but you need reopenings every time you do not know names of tables you need to process as I do not know. The code is dynamic to correlate (process) as many tables as somebody put in the routing table (because of that I am using weak refs).
Thanks for all. Your book is great. I am happy to read a book as yours after 6 years of Oracle experience. Your book is my first Oracle book I bought with my (non-company) money.
Regards,
Zoran
cursor parameter
mo, December 20, 2002 - 5:46 pm UTC
Tom:
1. Can you explain briefly what the setting of this parameter does for the system and if you did not set it =0 what happens?
Would it matter for a web app. IS it when a store procedure is run and you open a cursor it checks if it is in shared pool or not?
December 20, 2002 - 8:20 pm UTC
search this site for
"session_cached_cursors"
lots of articles on this topic exist already....
it caches cursors for reuse in your session -- SESSION. soft parsing -- what you describe, happens automagically.
cursors
mo, December 21, 2002 - 9:45 am UTC
Tom:
I did of course before posting. Got 28 hits two only applied directly. My understanding is that pl/sql will do it automatically, this parameter will only helps if you use dynamic SQL. Correct?
December 21, 2002 - 10:23 am UTC
native dynamic sql in plsql
sql in a poorly written java/vb application
help with recursive sql (sql oracle does on your behalf)
are all uses for this parameter.
PreparedStatement and cursor sharing
Sam, January 08, 2003 - 9:32 pm UTC
Tom,
Suppose I have the following piece of Java code:
String mySQL = "SELECT * FROM MyTable WHERE id = :b1";
for (i=0;i<100;i++)
(
PreparedStatement ps = myConnection.prepareStatement(mySQL);
ps.setInt(1,i);
ResultSet rs = ps.executeQuery();
...
rs.close();
ps.close();
}
Are the following correct:
1. If session_cached_cursor is set to say, 100, then the cursor will be cached, even if we close the PreparedStatement explicitly inside the loop
2. Once the cursor is cached, subsequent PreparedStatement calls will result in repeated "soft soft parse" instead of "soft parse"
3. Even though the cursor is cached, JDBC still has to issue a separate query to find out the column types the query will return, as I did not use defineColumnType to specify the datatypes. And JDBC has to do this every time in the loop.
4. We can avoid this extra JDBC operation as well as the repeated "soft soft parse" if we prepare the statement outside the loop and just rebind every time in the loop.
measuring the memory session_cached_cursors is using
Juan Carlos Reyes, March 31, 2003 - 11:08 am UTC
Hi Tom
is possible to measure the memory session_cached_cursors is using for all the clients connected to the database.
Thank you
March 31, 2003 - 11:21 am UTC
it sets up a simple small array in the PGA/UGA memory of the session that is dynamically grown.
You would have to measure PGA memory use with and without a cursor cache -- so in effect "no, not really"
Thanks
A reader, March 31, 2003 - 1:59 pm UTC
cursor opened in for cur_name in (select * .....) close automatically when an exception occurs
Ram, February 23, 2004 - 11:45 am UTC
I did not declare any explicit cursor . So I cannot use close cursor when an exception occurs.
Her eis my code
begin
for cur_name in (select id, name from peoples)
if (cur_name.id =2 )
select deptname into name from department where peopleid =2;
exception:
when NO_DATA_FOUND then
dbms_output.put_line(sqlerrm);
end if;
end loop
end;
if the deptname Query does not return anything and raises exception, does the cursor opened in for loop closes automatically.
One more question , does the for loop open cursor each time it traverses thru the loop, or it uses the same cursor.
February 23, 2004 - 4:34 pm UTC
implicit cursors are always taken care of for you as they go out of scope.
PLSQL is awesome (if you have my book effective Oracle by design -- i go into this in more detail). It'll parse a query once per session and keep is cached for you. You'll find that your SQL is parsed ONCE (tkprof will prove that to you) unless you become seriously starved for cursor slots (then it'll really close ones you are not using to avoid the "max open cursors" error you'd get otherwise)
session_cached_cursors in 10g
Arun Mathur, April 14, 2004 - 4:14 pm UTC
Tom,
Out of curiosity, does 10g deal with session_cached_cursors differently? This is what I get below where I do the steps you performed in the first followup:
I:\oracle\product\10.1.0\db_2\network\admin>sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 14 16:07:01 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter user-name: amathur
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set session_cached_cursors=0;
Session altered.
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%';
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 0
session cursor cache count 0
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select count(*) from dual d1' into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%';
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 0
session cursor cache count 0
SQL> alter session set session_cached_cursors=10;
Session altered.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select count(*) from dual d2' into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> alter session set session_cached_cursors=10;
Session altered.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select count(*) from dual d2' into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%';
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 14
session cursor cache count 0
Thanks Tom. Great discussion.
Arun
April 14, 2004 - 4:25 pm UTC
cool - yet another new feature :)
it isn't session cached cursors that changed.
It is native dynamic sql!
consider:
ops$tkyte@ORA10G> create table t ( x int );
Table created.
ops$tkyte@ORA10G> @trace
ops$tkyte@ORA10G> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte@ORA10G> declare
2 l_n number;
3 begin
4 for i in 1 .. 100
5 loop
6 execute immediate 'select count(*) from dual' into l_n;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Note:
select count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.00 0.00 0 0 0 0
Fetch 100 0.00 0.00 0 0 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 0.00 0.00 0 0 0 100
<b>Just one parse! In 9i:</b>
select count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.00 0.00 0 0 0 0
Execute 100 0.00 0.00 0 0 0 0
Fetch 100 0.02 0.00 0 300 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300 0.02 0.01 0 300 0 100
<b>100 parses. They've tuned up native dynamic sql....
Guess DBMS_SQL is truly dead now for most purposes. you no longer need to use it to get the parse once/execute many effect</b>
PL/SQL Optimization
Mark A. Williams, April 14, 2004 - 10:35 pm UTC
If anyone is interested in the new pl/sql optimizer, there is a really well done Whitepaper on OTN called "Freedom, Order, and PL/SQL Optimization" that discusses in some technical depth alot of the cool new features in the 10g pl/sql optimizer.
</code>
http://otn.oracle.com/tech/pl_sql/files/CodeOrder.zip <code>
insert into bit_bucket values (0.02);
- Mark
session cached cursor
A reader, July 18, 2004 - 4:17 pm UTC
Hi Tom
In James Morle book he says that the session cache
is populated only when the same sql is encountered
for the *3rd* time (he calls it Soft Parse Type 3.)
His stuff is on 8i though. My experiments (Similar
to the ones above) seems to indicate
that the session cached cursors start getting hit with the second time itself...
So was his assertion 8i specific?
From the comments of JL at </code>
http://www.mail-archive.com/oracle-l@fatcity.com/msg86259.html <code>
"See James Morle's book for the differences between 2nd and 3rd. This is when a cursor reference goes into the session cache"
this seems to be true for 10g as well?
So what am I missing here?
Thanx!
July 18, 2004 - 5:20 pm UTC
3 is the magical number.
occurences 1, 2, and 3 are "noted but won't hit"
occurence 4 will "hit"
share your test case and maybe we can comment on it (small, concise yet 100% complete (tm))
ok - here you go..
A reader, July 18, 2004 - 5:57 pm UTC
scott@ORA92I> -- trying to find when the session cache
scott@ORA92I> -- kicks in.
scott@ORA92I>
scott@ORA92I> -- set session cache to 0 - dont want to cache
scott@ORA92I> -- the following cursor
scott@ORA92I> alter session set session_cached_cursors=0;
Session altered.
scott@ORA92I> drop table t1;
Table dropped.
scott@ORA92I> create table t1( x number );
Table created.
scott@ORA92I> begin
2 for i in 1..5000
3 loop
4 insert into t1( x ) values ( i );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
scott@ORA92I>
scott@ORA92I> alter session set session_cached_cursors=100;
Session altered.
scott@ORA92I> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower( a.name ) like '%cursor ca%';
session cursor cache hits 0
session cursor cache count 1
scott@ORA92I> declare
2 l_count number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select /*+ my select*/ ' ||
7 ' count(*) from dual' into l_count;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
scott@ORA92I> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower( a.name ) like '%cursor ca%';
session cursor cache hits 100
session cursor cache count 4
scott@ORA92I> spool off
--------
my (potentially incorrect) explanation:
---------------------------------------
first candidate for caching (after caching
is enabled is the select from v$ views. It does not
get cached first time but gets a hit second time round.
there are 99 hits for the cursor in the for.
Thus we can account for all the 100 hits shown above.
This math does not work if we assume that the hit is
on the 3rd encounter...
Am i doing something wrong?
The count is 4 cos there may be some recursive
sqls (some recursive sql do show up
in the tkprof)
(e.g. I count only 2 cursors (one inside for loop
and the other selecting from v$ )
July 18, 2004 - 7:04 pm UTC
recursive sql -- that did you in. there are lots of things happening.
it is "3", hard to prove, but it is "3"
run this script over and over -- sometimes with the alter system flush, sometimes without - it is hard to measure that which the very act of measuring changes (heisenberg has something to say about this :)
@connect /
-- alter system flush shared_pool;
alter session set session_cached_cursors = 100;
select 1, x.*
from (
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower( a.name ) like '%cursor ca%'
) x;
/
declare
l_n number;
begin
for i in 1 .. 100
loop
execute immediate 'select count(*) from dual blah' into l_n;
end loop;
end;
/
select 2, x.*
from (
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower( a.name ) like '%cursor ca%'
) x;
oops!
A reader, July 18, 2004 - 5:59 pm UTC
ok one additional cursor is accounted for
by the logging in - the other cursor is presumably
recursive since session cache also stores any
recursive cursors generated as far as I know...
sorry dont get it...
A reader, July 19, 2004 - 11:48 am UTC
"recursive sql -- that did you in. there are lots of things happening."
well, through tkprof, one should be able to find out
what exactly is happening and account for the heisenberg
effect?..
So I guess what you are saying is that:
1. the two queries from v$ are not in the cache yet and
the "hit" does not include them...
2. the query inside the loop has 98 hits...
3. The remaining 6 hits are from the recursive
queries.
4. Also, can you confirm if anonymous blocks are
also put in the session cache?
July 19, 2004 - 12:11 pm UTC
don't forget -- sqlplus does it's own "recursive" sql -- dbms_output, dbms_application_info -- there is lots going on under the covers!
4) absolutely -- they are just "sql"
oops again!
A reader, July 19, 2004 - 11:51 am UTC
I meant
"1. the two queries from v$ are not in the cache yet and
the "hit" does not include them...
2. the query inside the loop has *97* hits...
3. The remaining 7 hits are from the recursive
queries.
4. Also, can you confirm if anonymous blocks are
also put in the session cache?
"
I suppose tkprof is a good way of proving this as
it shows all recursive queries?
July 19, 2004 - 12:11 pm UTC
yes, it would be.
thanx!
A reader, July 19, 2004 - 12:17 pm UTC
") absolutely -- they are just "sql" "
cool but confused
A reader, August 10, 2004 - 9:46 am UTC
Tom, this thread is too cool at the same I am highly confused about this stuff.
Could you please differentiate to me between OPEN_CURSORS and SESSION_CACHED_CURSORS parameters.
August 10, 2004 - 10:04 am UTC
open_cursors = maximum number of concurrently opened (by you, explicitly) cursors.
eg:
begin
open l_cursor1 for select * from dual;
open l_cursor2 for select * from dual;
...
open l_cursorN for select * from dual;
end;
open_cursors will dictate the highest number N could be -- you cannot have more than N open_cursors (you have to close some).
session_cached_cursors is an optimization behind the scenes. We keep an "array of pointers to parsed sql in the shared pool" in your client application. When you close a cursor (assuming you open/closed it 3 times, we don't cache immediately) we keep a pointer to it. The next time you open that cursor, we check out what we are pointing to and see if it is still relevant. If so, away we go (so we skip lots of work). If not, we parse as normal.
session cached_cursors does not count against your "open_cursors" count.
Vipin, August 11, 2004 - 12:08 am UTC
Hi Tom,
please correct if I am wrong:-
when ever i execute an SQL,
1. Oracle will hash my SQL find whether a cached cursor is there in my UGA (Private SQL area) for the same
1.1 if so use it
1.2 if not
1.2.1 see whether I have exceeded the OPEN_CURSORS value 1.2.1.1 if so then throw the exception
1.2.1.2 if not then do a hard parse or soft parse depending on whether the SQL is available in my shared pool and then cache the cursor for future use.
August 11, 2004 - 9:48 am UTC
when you parse a sql statement -- oracle will look in your session cached cursors (if you have that set up to a non-zero size) to see if the statement is there and then goto the shared pool to verify it is still there and is "ok"
if either
a) you are not using session cached cursors
b) you are using it but nothing is found there
c) you are using it but the thing in the shared pool isn't good anymore
you go onto step 2 which is a regular parse (be it soft -- a hit in the shared pool or hard -- a miss in the shared pool)
open cursors comes into play when you open the cursor itself -- regardless of where it was found.
PL/SQL cursor caching
Jay, February 08, 2005 - 3:02 pm UTC
The following is an excerpt from the 9.2.0.5 patch set readme:
-----------
PL/SQL Cursor Caching
Prior to release of the 9.2.0.5.0 patch set, the maximum number of cursors that could be cached for fast lookup by PL/SQL was bounded by the value of the init.ora parameter open_cursors. If you currently have open_cursors set to a high value (for example, greater than 1000), it is likely that this is causing large numbers of PL/SQL cursors to be cached in the shared pool. This could lead to issues with memory management, frequent reloading of library cache objects and ORA-04031 errors.
Patch set 9.2.0.5.0 alleviates the issue by changing the init.ora parameter which determines the upper bound for PL/SQL cursor caching from open_cursors to session_cached_cursors.
It will not be necessary for the majority of users to modify the value of either of these parameters. If you already have session_cached_cursors set to a value greater than the open_cursors parameter, then this change will have no performance impact upon your system.
However, if you have session_cached_cursors set to zero, or set at a value significantly lower than the open_cursors parameter, and you are concerned that PL/SQL cursors need to be cached for optimal performance, then you should ensure that the session_cached_cursors parameter is increased appropriately.
This issue is bug number 3150705.
--------
Also two other points:
1. Somewhere on your site I remember that you mentioned that the cursors (that have been closed) that PL/SQL caches (which can viewed using v$open_cursor) can be overwritten (i.e. the slot re-used) if necesary.
2. Also in this thread, you mention:
---------
session_cached_cursors is an optimization behind the scenes. We keep an "array
of pointers to parsed sql in the shared pool" in your client application. When
you close a cursor (assuming you open/closed it 3 times, we don't cache
immediately) we keep a pointer to it. The next time you open that cursor, we
check out what we are pointing to and see if it is still relevant. If so, away
we go (so we skip lots of work). If not, we parse as normal.
session cached_cursors does not count against your "open_cursors" count.
--------
Given that the PL/SQL cached cursor slot in the UGA can be overwritten (if it is closed by application) and it only contains a pointer to the shared cursor (in the shared pool)
1. How can PL/SQL cursor caching that uses open_cursor as upper bound result in "lead to issues with memory management, frequent reloading of library cache objects and ORA-04031 errors" as mentioned in the 9.2.0.5 patch set readme
2. If indeed the problems mentioned in point 1 do occur, how do they disappear from 9.2.0.5 onwards when the only change seems to be the upper bound value (i.e. session_cached_cursors instead of open_cursors)
February 09, 2005 - 1:43 am UTC
search for some of that text on this site, we had this discussion previously.
plug in
3150705
it'll find it.
Is a query which throws an exception cached?
Bob Lyon, May 09, 2005 - 12:33 pm UTC
Tom,
I was trying to make sense of the PARSE_CALLS to EXECUTIONS ratio (1/1)
that I was seeing in V$SQL for a cursor in a package procedure.
I eventually came to the conclusion that a cursors which generate an exception
are not being cached (on 9.2.0.6).
Here is a test case:
CREATE OR REPLACE PROCEDURE TEST
IS
lv INTEGER;
BEGIN
SELECT /*+ NO EXCEPTION */ 1
INTO lv
FROM ALL_OBJECTS
WHERE ROWNUM = 1;
SELECT /*+ EXCEPTION */ 1
INTO lv
FROM ALL_OBJECTS
WHERE ROWNUM < 3;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
NULL;
END TEST;
/
BEGIN
FOR i IN 1 .. 10
LOOP
TEST;
END LOOP;
END;
/
Querying V$SQL shows:
<SQL_TEXT> /*+ NO EXCEPTION */ /*+ EXCEPTION */
EXECUTIONS 10 10
--> PARSE_CALLS 1 10
BUFFER_GETS 162 192
CPU_TIME 60000 60000
ELAPSED_TIME 47366 55967
ROWS_PROCESSED 10 10
So I guess my question is "is a query which throws an exception cached?".
May 09, 2005 - 1:45 pm UTC
see, I learn something new.....
It has to be parsed/executed once successfully to get into the cache.
If you:
create table t as select * from all_users;
create or replace procedure p( p_n in number )
as
l_uid number;
begin
select user_id into l_uid from t where rownum <= p_n;
end;
/
and run:
exec P(1);
exec p(2);
exec p(2);
exec p(2);
it'll parse ONCE and execute 4 times. if you run instead:
exec p(2);
exec P(1);
exec p(2);
exec p(2);
it'll parse 2 times -- first failure, second success and none after that.
exec p(2);
exec p(2);
exec P(1);
exec p(2);
it'll parse 3 times.... and so on
thanks
Thanks!!!
Bob Lyon, May 09, 2005 - 2:03 pm UTC
OK, that explains what I was seeing - the package procedure was ALWAYS getting a TOO_MANY_ROWS.
I did a little more testing... A NO_DATA_FOUND exception is considered "a successful execution" while a TOO_MANY_ROWS is not.
"Curiouser and curiouser, said Alice."
session cached cursor..
Jim, May 11, 2005 - 12:55 pm UTC
From </code>
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/memory.htm#38401 <code>
<quote>Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.<quote>
I thought the session cursor is always in pga/uga and it points to shared sql/plsql execution plan in the shared pool. What is the doc talking about *moving the cursor into the session cursor cache*
Thanks.
May 11, 2005 - 7:17 pm UTC
they are setting up for that softer soft parse, even though you close the cursor in your application, we keep a link to it -- in the hopes that nothing in the environment or sga changes (no invalidations for example) and make your next soft parse -- a little bit softer.
A reader, June 28, 2005 - 6:43 am UTC
Tom,
Can you clarify when an Oracle Forms and an Oracle Reports cache and don't cache cursors, namely
1) Do they cache SQL that they execute in any PL/SQL code they have, ie a trigger or a user-defined procedure/function in that Form?
2) Do they Cache cursors in an Oracle Forms Block but once the Query completes the cursor is closed?
3) Do they close ALL cursors in current form OPEN_FORM /CALL_FORM/NEW_FORM.
Thanks
Ravi
June 28, 2005 - 7:56 am UTC
you'll want to ask this on a forum where forms experts reside, perhaps on otn.oracle.com.
I've not used forms in over a decade
session_cached_cursors and shared_pool fragmentation
amit poddar, July 28, 2005 - 8:06 am UTC
Hi,
After 9.2.0.5 I set session_cached_cursors to 50000 same as open_cursors about few months back. Recently we are seeing lots of ORA-4031s. I went through lots of metalink notes, all seem to point in same direction that session session_cached_Cursors to such a high value can lead to shared pool fragmentation.
I have set it now to a low value of 50.
My question is why does high value for session_cached_cursors lead to shared pool fragmentation.
As per my understanding session_cached_cursors=50000 implies the plsql cursor cache is 50000 cursors. So plsql will hold on to the library cache lock on the cursor even if the cursors is closed.
But I thought that only the cursor head needs to be in memory and rest of the cursor can be flushed out by oracle, since it can be recreated from the information in the cursor header. So then what results in fragmentation
thanks
amit
July 28, 2005 - 9:30 am UTC
5000!! 100 is probably more than high enough for any sort of performance related stuff.
session cached cursors controls the plsql cursor cache in 9205 AS WELL AS everything it did pre-9205 (eg: the setting predates the change in plsql, session cache cursors has another, older use)
clarification
amit poddar, July 28, 2005 - 10:00 am UTC
Hi,
So setting session_cache_cursor to such a high value will cause high shared pool fragmentation only in 9.2.0.5 or later since it controls the plsql cursors cache also?
So does that mean, before 9.2.0.5 setting session_cached_cursors to a high value did not have any effect on shared pool fragmentation ?
July 28, 2005 - 10:35 am UTC
no, it could have that effect in 9204 and before as well (there is a soft link from the client back to the shared pool).
but in 9205 is would be compounded by the additional pointing back by these cursors as well.
a reader
raman, August 09, 2007 - 6:21 am UTC
First CaseSQL*Plus: Release 10.1.0.2.0 - Production on Thu Aug 9 14:58:09 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter system flush shared_pool
2 /
System altered.
SQL> alter system flush buffer_cache
2 /
System altered.
SQL> alter session set session_cached_cursors=10
2 /
Session altered.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select count(*) from dual d2' into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 19
session cursor cache count 0
SQL> show parameter cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
session_cached_cursors integer 10
SQL> exit
Second caseconnect again
if i don't flush the shared pool and buffer cache then 0 hits why?
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Aug 9 15:03:08 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set session_cached_cursors=10
2 /
Session altered.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select count(*) from dual d2' into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 0
session cursor cache count 0
SQL> show parameter cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
session_cached_cursors integer 10
one more confusion if i do the same as i did in first case it shows me session cursors hits 21 why its getting vary from first case which is 19 hits
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Aug 9 15:05:11 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter system flush shared_pool
2 /
System altered.
SQL> alter system flush buffer_cache
2 /
System altered.
SQL> alter session set session_cached_cursors=10
2 /
Session altered.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select count(*) from dual d2' into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 21
session cursor cache count 0
SQL>
SQL>
tom as you produced the code in yours environment in this thread at above its showing 99 hits ,why its differing to me?
one last question tom does session cached cursor impact expose in tkprof result if it is then please where to see?
raman
August 14, 2007 - 10:44 am UTC
because in 10g, execute immediate is better than it was in 9i and it caches cursors for you.
In 10g, your loop:
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select count(*) from dual d2' into l_cnt;
7 end loop;
8 end;
9 /
parsed the sql statement ONCE and never ever closed it - since you execute immediate the same thing over and over. In 9i, it would have parsed it 100 times, in 10g - just once....
execute immediate logic psuedo code for:
execute immediate l_sql;
in 10g is:
if ( l_sql <> last_sql_we_executed_here )
then
if there was a last sql executed here, close it
parse l_sql
last_sql_we_executed_here = l_sql
end if;
bind l_sql
execute l_sql
In 9i, it was
parse l_sql
bind l_sql
execute l_sql
close l_sql
so, in 10g, we can "trick it out" and just execute 2 sql statements instead:
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set session_cached_cursors=0;
Session altered.
ops$tkyte%ORA10GR2> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%';
NAME VALUE
------------------------------ ----------
session cursor cache hits 627
session cursor cache count 20
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 l_cnt number;
3 begin
4 for i in 1 .. 1000
5 loop
6 execute immediate 'select count(*) from t t'||mod(i,2) into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%';
NAME VALUE
------------------------------ ----------
session cursor cache hits 627
session cursor cache count 20
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set session_cached_cursors=10;
Session altered.
ops$tkyte%ORA10GR2> declare
2 l_cnt number;
3 begin
4 for i in 1 .. 1000
5 loop
6 execute immediate 'select count(*) from t t'||mod(i,2) into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%';
NAME VALUE
------------------------------ ----------
session cursor cache hits 1626
session cursor cache count 3
a reader
raman, August 15, 2007 - 3:31 am UTC
thanks tom , tom i have some confusion please clear me..
thousands of apologies as now i am going to ask bunch of question.
SESSION_1SESSION_1>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
SESSION_1>
SESSION_2From second session i am checking the session_cached_cursors for session 1,session 1 sid is 126
SESSION_2>select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 and b.sid='126'
6 /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 0
session cursor cache count 0
SESSION_1Now i come to session 1 again and set the session_cached_cursors=10
SESSION_1>alter session set session_cached_cursors=10
2 /
Session altered.
SESSION_1>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 10
SESSION_1>
SESSION_2validating again there is no query is executed at session 1
SESSION_2>select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 and b.sid='126'
6 /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 0
session cursor cache count 0
SESSION_1back again to run yours code at session 1
SESSION_1>declare
2 l_cnt number;
3 begin
4 for i in 1 .. 1000
5 loop
6 execute immediate 'select count(*) from t t'||mod(i,2) into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SESSION_2logged to session 2 to check the session_cached_cursors hits of session 2
SESSION_2>select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 and b.sid='126'
6 /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 998
session cursor cache count 2
1-as my understanding due to session cursor cache count 2 impact to session curosr chache hits 998 i.e (1000-2=998)
am i right tom?
2-as oracle documents says oracle checks library cache to determine whether more than 3 parse requests have been issued on a given statement.if so,then oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into session cursor cache.but in my case after 2 parse requests oracle cached and moves the cursor into session cursor cache.my question is 3 figure is standard or its default or its fixed?
if 3 is standard then why 2 is at my end i did not change any parameter,if it can be changed then from where i can change?
3-tom what tricks you used here mod(i,2) as you said 10g execute immediate is better than it was in 9i and it caches cursors for you.you just change the alias at each iteration i am not getting it ,please would you tell me whats the trick you used,my understanding at each iteration its new brand sql due to alias changing for that how oracle identify its the same statement as issued before?
4- as i am posting the tkprof result at the end ,now oracle shows 5000 parse at each iteration it is not using the 10g new feature for caching execute immediate statement with or without setting session cached cursor for parse when i use yours trick mod(i,2).
SESSION_1>conn scott/tiger
Connected.
SESSION_1>alter system flush shared_pool
2 /
System altered.
SESSION_1>alter system flush shared_pool
2 /
System altered.
SESSION_1>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
SESSION_1>alter session set events '10046 trace name context forever,level 12'
2 /
Session altered.
SESSION_1>declare
2 l_cnt number;
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate 'select /*+cached=0 */ count(*) from t t'||mod(i,2) into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SESSION_1>
SESSION_1>disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
TKPROF********************************************************************************
select /*+cached=0 */ count(*)
from
t t1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5000 0.20 0.26 0 0 0 0
Execute 5000 0.25 0.32 0 0 0 0
Fetch 5000 56.01 55.95 0 1815000 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15000 56.46 56.53 0 1815000 0 5000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=363 pr=0 pw=0 time=9448 us)
64392 TABLE ACCESS FULL T (cr=363 pr=0 pw=0 time=32 us)
********************************************************************************
SESSION_1>CONN SCOTT/TIGER
Connected.
SESSION_1>alter system flush shared_pool
2 /
System altered.
SESSION_1>alter system flush shared_pool
2 /
System altered.
SESSION_1>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
SESSION_1>/
System altered.
SESSION_1>alter session set session_cached_cursors=10
2 /
Session altered.
SESSION_1>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 10
SESSION_1>alter session set events '10046 trace name context forever,level 12'
2 /
Session altered.
SESSION_1>declare
2 l_cnt number;
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate 'select /*+cached=10 */ count(*) from t t'||mod(i,2) into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SESSION_1>disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
TKPROF********************************************************************************
select /*+cached=10 */ count(*)
from
t t1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5000 0.14 0.53 0 0 0 0
Execute 5000 0.21 0.22 0 0 0 0
Fetch 5000 58.60 60.67 0 1815000 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15000 58.96 61.43 0 1815000 0 5000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=363 pr=0 pw=0 time=9285 us)
64392 TABLE ACCESS FULL T (cr=363 pr=0 pw=0 time=37 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: cache buffers chains 2 0.03 0.03
********************************************************************************
raman
curious statistics of total parse and cursor cache hits¿
Michel SALAIS, March 26, 2008 - 10:27 am UTC
Hi tom,
I had the curious result below several times and I wander what could be the reason to have session cursor cache hits > parse count (total)
select name, value
from v$mystat ms, v$statname sn
where ms.statistic# = sn.statistic#
and (sn.name like '%cursor%' or sn.name like 'parse count%')
NAME VALUE
-------------------------- -----
opened cursors cumulative 8950
opened cursors current 38
session cursor cache hits
6305 session cursor cache count 20
cursor authentications 239
parse count (total)
5933 parse count (hard) 684
parse count (failures) 60
March 26, 2008 - 10:46 am UTC
if I had to guess - your plsql cursor cache (controlled by session cached cursors) is hitting the upper bound and we are experiencing implicit closes of cursors that are implicitly reparsed, eg:
ops$tkyte%ORA11GR1> create or replace procedure p
2 as
3 n number;
4 begin
5 select count(*) into n from dual d1;
6 select count(*) into n from dual d2;
.......
52 select count(*) into n from dual d48;
53 select count(*) into n from dual d49;
54 end;
55 /
Procedure created.
ops$tkyte%ORA11GR1> connect /
Connected.
ops$tkyte%ORA11GR1> show parameter session_cached
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
ops$tkyte%ORA11GR1> select name, value
2 from v$mystat ms, v$statname sn
3 where ms.statistic# = sn.statistic#
4 and (sn.name like '%cursor%' or sn.name like 'parse count%');
NAME VALUE
------------------------------ ----------
opened cursors cumulative 22
opened cursors current 4
session cursor cache hits 1
session cursor cache count 16
cursor authentications 1
parse count (total) 20
parse count (hard) 0
parse count (failures) 0
8 rows selected.
ops$tkyte%ORA11GR1> exec p
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> exec p
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> exec p
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> select name, value
2 from v$mystat ms, v$statname sn
3 where ms.statistic# = sn.statistic#
4 and (sn.name like '%cursor%' or sn.name like 'parse count%');
NAME VALUE
------------------------------ ----------
opened cursors cumulative 177
opened cursors current 4
session cursor cache hits 103
session cursor cache count 50
cursor authentications 1
parse count (total) 79
parse count (hard) 0
parse count (failures) 0
8 rows selected.
so, anything that causes an IMPLICIT reparse (aged out sql, invalidated sql, hitting the cursor cache like I did above...) would be able to do that.
Great explanation with example
A reader, March 26, 2008 - 11:26 am UTC
.. as always
sessio_cursor_cache
deba, March 27, 2008 - 9:11 am UTC
Hi Tom,
You are saying "Followup December 21, 2002 - 10am US/Eastern:
native dynamic sql in plsql
sql in a poorly written java/vb application
help with recursive sql (sql oracle does on your behalf)
are all uses for this parameter. "
In my database, in awr report , hard parse count is high for followings :
Parse Calls Executions % Total Parses SQL Id SQL Text
4,533,493 4,535,624 78.49 8sph6b5p41afr select min(bitmapped) from ts$...
150,865 150,866 2.61 gt76xm3smrdmn select sys.cdc_rsid_seq$.nextv...
69,813 98,584 1.21 4m94ckmu16f9k select count(*) from dual
43,442 43,442 0.75 2mzqp5mh27akf SELECT DRV.AUD...
26,450 26,450 0.46 3htvbztpgxu3q SELECT AUD_INVFACT_8 FROM VW_...
26,448 26,448 0.46 1aajzxjdqq58f SELECT AUD_INVFACT_13 FROM VW...
26,448 26,448 0.46 8yg8aqpfgfxtd SELECT AUD_INVFACT_10 FROM VW...
26,448 26,448 0.46 bxrz171p0qznm SELECT AUD_INVFACT_15 FROM VW...
26,447 26,448 0.46 bxn1yuq1dx7kt SELECT AUD_INVFACT_3 FROM VW_...
26,438 26,438 0.46 8n70vgrrm3pwa SELECT AUD_INVFACT_12 FROM VW...
Out of the above sqls, I am pasting one sql in full :
SELECT AUD_INVFACT_8 FROM VW_SSP_CMCL_BREAK_INVPROP WHERE CMCL_BREAK_KEY = :1 AND TXM_LVL_KEY = :2 AND YEAR_MONTH = :3 AND IMPACT_TYPE = :4 AND AUD_SET_KEY = :5 AND TERRITORY_KEY = :6
Here the column section (AUD_INVFACT_8) of the sql is determined dynamically. This applies for other sqls as well. Everytime it is getting hard parsed.
In configuration, the value of session_cached_cursors is 1000 and value of open curosrs is 2000.
If I see the statistics then I am getting followings as well :
select
'session_cached_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from
( select
max(s.value) used
from
sys.v_$statname n,
sys.v_$sesstat s
where
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
( select
value
from
sys.v_$parameter
where
name = 'session_cached_cursors'
)
union all
select
'open_cursors',
lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from
( select
max(sum(s.value)) used
from
sys.v_$statname n,
sys.v_$sesstat s
where
n.name in ('opened cursors current', 'session cursor cache count') and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
sys.v_$parameter
where
name = 'open_cursors'
)
/
PARAMETER VALUE USAGE
----------------------------- ----- -----
session_cached_cursors 1000 100%
open_cursors 2000 126%
But still so many hard parsing is happening ? How is this possible ? What i need to do to avoid such hard parsing ?
Thanks
deba
March 27, 2008 - 10:50 am UTC
where do you see a high hard parse count? parse count is not "hard parse count"
I see that it is PARSED for every execute (shame on your programmers, they did that and they are the only ones that can FIX THAT).
that does not mean they are HARD PARSED, just parsed - the first time is "hard" the next time(s) are "softer - still very bad but not hard"
You can set session cached cursors to turn a "soft parse into a softer soft parse" but it will never REDUCE THE PARSE COUNT
there is only one set of people that can reduce the parse count - the developers, they control this 100% - you cannot reduce the parse count yourself.
session_curosr_cache
deba, March 28, 2008 - 3:47 pm UTC
Hi,
You are saying "I see that it is PARSED for every execute (shame on your programmers, they did that and they are the only ones that can FIX THAT)."
My code is similar like this :
create or replace procedure dd as
l_name varchar2(20) :='subfile_key';
l_text varchar2(2000) := '';
l_subfile_key number := 20002;
l_result number :=0;
begin
l_text:='select '||l_name||' from cmn_batch_job_status where batch_dt=(select batch_dt from
cmn_batch_parameter) and '
||' subfile_key=:1';
execute immediate l_text into l_result using l_subfile_key;
end;
/
Now if we run this everytime hard parse count is getting incremented. Please see the followings:
Connected.
SQL> select name,value
2 from v$mystat m, v$statname s
3 where m.STATISTIC#=s.STATISTIC#
4 and m.STATISTIC# in (336,337,338,339,340);
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 0
parse count (total) 16
parse count (hard) 0
parse count (failures) 0
SQL> exec dd;
PL/SQL procedure successfully completed.
SQL> select name,value
2 from v$mystat m, v$statname s
3 where m.STATISTIC#=s.STATISTIC#
4 and m.STATISTIC# in (336,337,338,339,340);
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 1
parse time elapsed 1
parse count (total) 19
parse count (hard) 1
parse count (failures) 0
SQL> exec dd;
PL/SQL procedure successfully completed.
SQL> select name,value
2 from v$mystat m, v$statname s
3 where m.STATISTIC#=s.STATISTIC#
4 and m.STATISTIC# in (336,337,338,339,340);
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 2
parse time elapsed 2
parse count (total) 21
parse count (hard) 1
parse count (failures) 0
SQL> exec dd;
PL/SQL procedure successfully completed.
SQL> select name,value
2 from v$mystat m, v$statname s
3 where m.STATISTIC#=s.STATISTIC#
4 and m.STATISTIC# in (336,337,338,339,340);
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 2
parse time elapsed 3
parse count (total) 23
parse count (hard) 1
parse count (failures) 0
SQL> show parameter session_cach
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
session_cached_cursors integer
1000
I don't know where the problem lies . What should I do to avoid hard parsing ? Why the parse count
total gets incremented by 2 always ?
I have to select column dynamically ( beceause there are lot of columns in that table ). Please
correct me with examples as you always do and help us to understand the problem correctly.
Thnaks
Deba
March 31, 2008 - 8:04 am UTC
yup, your "generic lets do it slowly" approach is the cause here. You did it.
do not dynamically select the column name like that - not only are you VERY MUCH SUBJECT TO SQL INJECTION - you are doing it inefficiently.
create or replace procedure dd as
l_name varchar2(20) :='subfile_key';
l_text varchar2(2000) := '';
l_subfile_key number := 20002;
l_result number :=0;
begin
l_text:='select '||l_name||' from cmn_batch_job_status where batch_dt=(select
batch_dt from
cmn_batch_parameter) and '
||' subfile_key=:1';
execute immediate l_text into l_result using l_subfile_key;
end;
/
At most, that would be:
select decode( l_name, 'A', a, 'B', b, 'C', c, ..... )
into l_result
from cmn_batch_job_status
where batch_dt = (select batch_dt from cmn_batch_paramter)
and subfile_key = l_subfile_key;
do that, do NOT use dynamic sql unless and until you absolutely have no other choice.
I don't care if there are 1,000 columns in that table - you do NOT have to use dynamic sql and it would be wrong to use it.
If you want, you can generate the select out of the dictionary. eg:
ops$tkyte%ORA11GR1> select '''' || column_name || ''', ' || column_name
2 from dba_tab_columns where table_name = 'GV_$SESSION' and owner = 'SYS';
''''||COLUMN_NAME||''','||COLUMN_NAME
----------------------------------------------------------------
'ACTION_HASH', ACTION_HASH
'CLIENT_INFO', CLIENT_INFO
'FIXED_TABLE_SEQUENCE', FIXED_TABLE_SEQUENCE
'ROW_WAIT_OBJ#', ROW_WAIT_OBJ#
...
'MODULE_HASH', MODULE_HASH
'ACTION', ACTION
92 rows selected.
there is the main body of the decode faster than you typed in the original code.
session_curosr_cache
deba, March 30, 2008 - 3:19 am UTC
Hi Tom,
Plesae don't consider the line "What should I do to avoid hard parsing ?". It is wrong.
If you see my above post, then you can find out that everytime I run the above piece of ocde , parse count is getting incremented by 2. Certianly that dynamic sql is there.
Now you are saying "I see that it is PARSED for every execute (shame on your programmers, they did that and they are the only ones that can FIX THAT). " Now please let me know what can I do in this code to fix parsing problem ?
March 31, 2008 - 8:37 am UTC
see above.
You don't need, want or desire to use dynamic sql. Everything will be better if anytime someone says "I need dynamic sql" if they sit down and spend 30 minutes trying to figure any way to NOT use dynamic sql... That 30 minutes thinking about a non-dynamic approach will be the best way to spend 30 minutes, it'll pay off every time.
session_cursor_cache
deba, March 31, 2008 - 4:45 pm UTC
Hi Tom,
I am using dynamic sql in procedure dd and decode in dd1. Please see below
CREATE OR REPLACE PROCEDURE Dd AS
l_name VARCHAR2(20) :='subfile_key';
l_text VARCHAR2(2000) := '';
l_subfile_key NUMBER := 20002;
l_result NUMBER :=0;
BEGIN
l_text:='select '||l_name||' from cmn_batch_job_status where batch_dt=(select batch_dt from cmn_batch_parameter) and '
||' subfile_key=:1';
EXECUTE IMMEDIATE l_text INTO l_result USING l_subfile_key;
END;
/
CREATE OR REPLACE PROCEDURE Dd1 ( p_subfile_key IN NUMBER ) AS
l_name VARCHAR2(20) :='subfile_key';
l_text VARCHAR2(2000) := '';
l_result NUMBER :=0;
BEGIN
SELECT DECODE(p_subfile_key,20002,subfile_key,EXECUTION_SEQ)
INTO l_result
FROM cmn_batch_job_status
WHERE batch_dt=(SELECT batch_dt FROM cmn_batch_parameter)
AND subfile_key=p_subfile_key;
END;
/
Now I am using procedure using dynamic sql and see the statistics
SQL> select name,value
2 from v$mystat m, v$statname s
3 where m.STATISTIC#=s.STATISTIC#
4 and m.STATISTIC# in (336,337,338,339,340,287,288);
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 4
session cursor cache count 13
parse time cpu 0
parse time elapsed 0
parse count (total) 19
parse count (hard) 1
parse count (failures) 0
7 rows selected.
SQL> exec dd;
PL/SQL procedure successfully completed.
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 4
session cursor cache count 17
parse time cpu 2
parse time elapsed 2
parse count (total) 26
parse count (hard) 3
parse count (failures) 0
7 rows selected.
SQL> exec dd;
PL/SQL procedure successfully completed.
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 5
session cursor cache count 18
parse time cpu 2
parse time elapsed 2
parse count (total) 29
parse count (hard) 3
parse count (failures) 0
7 rows selected.
SQL> exec dd;
PL/SQL procedure successfully completed.
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 6
session cursor cache count 19
parse time cpu 2
parse time elapsed 2
parse count (total) 31
parse count (hard) 3
parse count (failures) 0
7 rows selected.
Now if I use dd1 procedure where I have used decode , not a dynamic sql , then I have following statistics :
SQL> select name,value
2 from v$mystat m, v$statname s
3 where m.STATISTIC#=s.STATISTIC#
4 and m.STATISTIC# in (336,337,338,339,340,287,288);
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 3
session cursor cache count 12
parse time cpu 1
parse time elapsed 1
parse count (total) 17
parse count (hard) 0
parse count (failures) 0
7 rows selected.
SQL> exec dd1(20002);
PL/SQL procedure successfully completed.
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 4
session cursor cache count 14
parse time cpu 1
parse time elapsed 1
parse count (total) 20
parse count (hard) 0
parse count (failures) 0
7 rows selected.
SQL> exec dd1(20002);
PL/SQL procedure successfully completed.
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 6
session cursor cache count 14
parse time cpu 1
parse time elapsed 1
parse count (total) 22
parse count (hard) 0
parse count (failures) 0
7 rows selected.
SQL> exec dd1(20002);
PL/SQL procedure successfully completed.
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 8
session cursor cache count 14
parse time cpu 1
parse time elapsed 1
parse count (total) 24
parse count (hard) 0
parse count (failures) 0
7 rows selected.
After considering both statistics , I can find out
1) no decrease in value of parsing. In both cases parsing gets incremented. In both cases , I am not sure whether it is soft parse or softer parse.
2) In both cases , for the first time in session ,parse time cpu and parse time elapsed get incremented but after that these are fixed.
3) In both cases, there is only one difference. For the first time , hard parse count is incremented in case of dynamic sql, not the the sql where decode has been used. This is happening since it was not in shared pool. If I once again login and execute dd then there is no hard parse .
4) In both cases , session cursor cache hits gets incremented.
So I am not sure what extra advantages I can get if I use decode instead of dynamic sql . But you have told that if I use decode then it will
be better and efficient.
Could you please tell me where I am wrong ? Could you please explain your point giving example ?
Thanks
Deba
March 31, 2008 - 5:42 pm UTC
1) of course, you are using sqlplus. every single time you run any statement in sqlplus (a very very simple command line tool) it parses. You are seeing the parse of your exec being done.
My point was, if you have 100 column names you pass in, ultimately you'll have 100 copies of that tiny little sql statement in the shared pool, and if you pass in different column names one after the other, you'll see a big difference in parsing...
ops$tkyte%ORA10GR2> create or replace procedure dyn( p_cname in varchar2 )
2 as
3 l_data varchar2(30);
4 begin
5 execute immediate 'select ' || p_cname || ' from t' into l_data;
6 end;
7 /
Procedure created.
ops$tkyte%ORA10GR2> create or replace procedure static( p_cname in varchar2 )
2 as
3 l_data varchar2(30);
4 begin
5 select decode( upper(p_cname), 'C1', c1, 'C2', c2 ) into l_data from t;
6 end;
7 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "parse count (total)"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
---------------------- ----------
parse count (total) 966
ops$tkyte%ORA10GR2> begin
2 for i in 1 .. 100
3 loop
4 dyn( 'c' || (mod(i,2)+1) );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
---------------------- ---------- ------------------
parse count (total) 1076 110
ops$tkyte%ORA10GR2> @mystat "parse count (total)"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
---------------------- ----------
parse count (total) 1079
ops$tkyte%ORA10GR2> begin
2 for i in 1 .. 100
3 loop
4 static( 'c' || (mod(i,2)+1) );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
---------------------- ---------- ------------------
parse count (total) 1087 8
static = good
dynamic = bad
static = secure
dynamic = insecure (totally, I can sql inject to in a really bad way...)
2) your test was not valid....
3) see #2
4) see #3
session_cache_cursor
deba, April 01, 2008 - 10:05 am UTC
Hi Tom,
Thanks for the brilliant answer. Once again two small doubts.
What I know that whenever a sql gets executed, then a parsing ( atleast soft or softer )is required. This is basic. But here parsing is very very less (6) in compared to executions(100). How is this possible and in what cases ? Is parsing getting shared due to session_cache_cursor ? Please clear this doubt.
Is there any difference between soft and softer parse ? If so what is that difference ? How to find out softer parse ?
Thanks
Deba
April 01, 2008 - 10:11 am UTC
plsql does not close cursors unless and until it has to.
You can say 'close', PLSQL says "yeah, right, you'll be back" and keeps it open.
It uses the limit of session cached cursors to determine how many cursors to hold open.
a softer parse uses a few less latches, making it a little more scalable than a soft parse.
session_cache_cursor
deba, April 02, 2008 - 3:39 am UTC
Hi Tom,
You are saying "It uses the limit of session cached cursors to determine how many cursors to hold open."
But you tell that in case of pl/sql, session cached cursors is applicable for only dynamic sql as pl/sql can not cache dynamic sql ? If this is true then how limit of session cached cursors is used to determine how many cursors to hold open which are not dynamic ?
Thanks
Deba
April 02, 2008 - 8:04 am UTC
that isn't what I said.
that isn't even what I demonstrated.
and that is because that isn't true at all.
session_cached_cursors is used by plsql to determine how large of a cursor cache to have, how many cursors to hold open. STATIC SQL is cached especially well using this session_cached_cursors setting. STATIC SQL is absolutely cached.
dynamic SQL will be cached - when it can be, in 10g (new feature). Native dynamic sql used to not cache anything, in 10g, it can. Native dynamic sql now works sort of like this (assuming there is room in the cursor cache)
execute immediate l_stmt;
the psuedo code for that might be:
if (l_stmt <> SAME_THING_WE_EXECUTED_RIGHT_HERE_LAST_TIME)
then
if last_cursor is open
then
close last_cursor;
end if;
open last_cursor for l_stmt;
SAME_THING_WE_EXECUTED_RIGHT_HERE_LAST_TIME = l_stmt;
end if;
bind last_cursor;
execute last_cursor;consider, we'll run dyn with the following inputs:
a) 1,000 zeros. It'll parse a minimum number of times since the statement we dynamically execute remains constant.
b) 100 zeroes, then 100 ones, then 100 twos and so on... It'll parse a little more (9 times more - for the ones, the twos, the threes and so on)
c) 0 then 1 then 0 then 1 then 0 then 1 .... It'll parse every single time - because the statement is never the same call after call..
ops$tkyte%ORA10GR2> create or replace procedure dyn( p_correlation in number )
2 as
3 l_cnt number;
4 begin
5 execute immediate 'select count(*) from dual d' || p_correlation into l_cnt;
6 end;
7 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "parse count (total)"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
---------------------- ----------
parse count (total) 2126
ops$tkyte%ORA10GR2> exec for i in 1 .. 1000 loop dyn(0); end loop;
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
---------------------- ---------- ------------------
parse count (total) 2133 7
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "parse count (total)"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
---------------------- ----------
parse count (total) 2136
ops$tkyte%ORA10GR2> exec for i in 1 .. 1000 loop dyn( trunc(i/100) ); end loop;
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
---------------------- ---------- ------------------
parse count (total) 2152 16
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "parse count (total)"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
---------------------- ----------
parse count (total) 2155
ops$tkyte%ORA10GR2> exec for i in 1 .. 1000 loop dyn( mod(i,2)+1 ); end loop;
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
---------------------- ---------- ------------------
parse count (total) 3161 1,006
session_cache_cursor
deba, April 02, 2008 - 3:45 pm UTC
Hi Tom,
I have following diagram in metalink.
SQL Parsing Flow Diagram
========================
Statement
Submitted
|
Is it in an open cursor?--------------YES----V
| |
NO |
| |
Is SESSION_CACHED_CURSORS = Value |
and cursor in --------------YES----V In these 3 cases we
Session Cursor cache? | know that the cursor has
| | already been parsed, so
NO | re-parsing is
| | unnecessary.
Is HOLD_CURSOR=Y |
and cursor in --------------YES----V
Held cursor cache? |
| |
NO |
| | ^
OPEN A CURSOR | CLIENT SIDE |
| | -------------|
Statement is Hashed and compared | SERVER SIDE |
with the Hashed value in the sql area | V
| V
Is it in sql area? --YES-(Soft Parse)--> ---------
| | |
NO | EXECUTE |
| | |
PARSE STATEMENT ('Hard' Parse)---------> ---------
In this case, you can find out "open cursor" layer before session cache curosr . This is not clear. Could you please explain me where "open cursor" actually stays ?
Thanks
Deba
April 02, 2008 - 8:05 pm UTC
what note is that, I'll have them retract that - it is wrong.
Metalink note
Michel Cadot, April 03, 2008 - 12:54 am UTC
SQL Parsing Flow Diagram, Note:32895.1
Regards
Michel
session cache cursor
deba, May 22, 2008 - 10:48 am UTC
Hi Tom,
Suddenly I have seen a not on metalink and it is saying that REF CURSOR in PL/SQL does not use session cache cursor in 10g.
My database is 10.2.0.3 on solaris 10.5 . Both are 64 bit .
In order to check this , I have done following:
SQL> show parameter session_cach
session_cached_cursors integer 100
SQL> edit
Wrote file afiedt.buf
1 create or replace procedure px as
2 type rc is ref cursor;
3 rc_var rc;
4 l_dt date;
5 begin
6 open rc_var for select batch_dt from cmn_batch_parameter;
7 fetch rc_var into l_dt;
8 close rc_var;
9* end;
SQL> /
Procedure created.
SQL> set pages 0
SQL> set linesize 1000
SQL> SELECT name,value FROM
2 v$statname n, v$mystat m
3 WHERE name LIKE '%cursor%'
4 AND n.statistic#=m.statistic#;
opened cursors cumulative 235
opened cursors current 2
session cursor cache hits 134
session cursor cache count 99
cursor authentications 5
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 248
opened cursors current 2
session cursor cache hits 139
session cursor cache count 99
cursor authentications 6
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 251
opened cursors current 2
session cursor cache hits 139
session cursor cache count 99
cursor authentications 8
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 254
opened cursors current 2
session cursor cache hits 140
session cursor cache count 100
cursor authentications 8
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 257
opened cursors current 2
session cursor cache hits 142
session cursor cache count 100
cursor authentications 8
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 260
opened cursors current 2
session cursor cache hits 144
session cursor cache count 100
cursor authentications 8
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 263
opened cursors current 2
session cursor cache hits 146
session cursor cache count 100
cursor authentications 8
From the above test , I can see that in all cases ( except in one case ) session cursor cache hit gets incremented. It means that REF CURSOR in PL/SQL user session cursor cache in 10g.
Let me know whether my test is wrong or doument is wrong.
Thanks
Deba
May 23, 2008 - 8:04 am UTC
they changed it in 10g, it can and does use it.
be neat if you would have provided the note id, then I could have gotten it fixed.
session cache cursor
deba, May 24, 2008 - 5:34 am UTC
Tom,
Thanks for the reply. You have told "be neat if you would have provided the note id, then I could have gotten it fixed." . Yes I missed the bug id . Here it is 6510615 and note id is 30804.1
Thanks
Deba
May 24, 2008 - 9:31 pm UTC
oh, it was found, fixed and is patched in most current releases. Note is "ok" as is.
session cache cursor
deba, May 25, 2008 - 5:47 am UTC
Hi Tom,
you are saying "oh, it was found, fixed and is patched in most current releases. Note is "ok" as is. " - but it is not clear.
Ypu earlier told "they changed it in 10g, it can and does use it." - it means pl/sql ref cursor can and does use session cache. So it is
cotracdictory.
Not only that my following example shows that ef cursor open in pl/sql uses session cache ( if I am not wrong ).
SQL> show parameter session_cach
session_cached_cursors integer 100
SQL> edit
Wrote file afiedt.buf
1 create or replace procedure px as
2 type rc is ref cursor;
3 rc_var rc;
4 l_dt date;
5 begin
6 open rc_var for select batch_dt from cmn_batch_parameter;
7 fetch rc_var into l_dt;
8 close rc_var;
9* end;
SQL> /
Procedure created.
SQL> set pages 0
SQL> set linesize 1000
SQL> SELECT name,value FROM
2 v$statname n, v$mystat m
3 WHERE name LIKE '%cursor%'
4 AND n.statistic#=m.statistic#;
opened cursors cumulative 235
opened cursors current 2
session cursor cache hits 134
session cursor cache count 99
cursor authentications 5
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 248
opened cursors current 2
session cursor cache hits 139
session cursor cache count 99
cursor authentications 6
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 251
opened cursors current 2
session cursor cache hits 139
session cursor cache count 99
cursor authentications 8
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 254
opened cursors current 2
session cursor cache hits 140
session cursor cache count 100
cursor authentications 8
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 257
opened cursors current 2
session cursor cache hits 142
session cursor cache count 100
cursor authentications 8
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 260
opened cursors current 2
session cursor cache hits 144
session cursor cache count 100
cursor authentications 8
SQL> exec px
PL/SQL procedure successfully completed.
SQL> /
opened cursors cumulative 263
opened cursors current 2
session cursor cache hits 146
session cursor cache count 100
cursor authentications 8
From the above test , I can see that in all cases ( except in one case ) session cursor cache hit
gets incremented. It means that REF CURSOR in PL/SQL uses session cursor cache in 10g.
Am I right ? If not, can you give your great example to clear my doubt ?
Thanks
Deba
May 27, 2008 - 7:43 am UTC
I already said "yes, it does", yes, it used the session cursor cache.
Pratik, July 06, 2009 - 7:14 am UTC
Hi Tom,
Case-1
SQL> show parameter session_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 20
SQL> select count(*) from t;
COUNT(*)
----------
0
SQL> select count(*) from t;
COUNT(*)
----------
0
SQL> select count(*) from t;
COUNT(*)
----------
0
SQL> select count(*) from t;
COUNT(*)
----------
0
SQL> select count(*) from t;
COUNT(*)
----------
0
SQL> select count(*) from t;
COUNT(*)
----------
0
Output of TKprof for case-1:
select count(*)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 1 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 0 18 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.00 0.00 0 19 0 6
Misses in library cache during parse: 1
Case-2
SQL> declare
2 l_n number;
3 begin
4 for i in 1 .. 100
5 loop
6 execute immediate 'select count(*) from T' into l_n;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Output of TKprof for case-2:
select count(*)
from
T
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 100 0.01 0.00 0 0 0 0
Fetch 100 0.01 0.01 0 300 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 0.03 0.01 0 301 0 100
Misses in library cache during parse: 1
I am expecting output for case-1 as 1 parse and 6 execution. Why it is 6 parse and 6 execution?
why it perform 5 soft parse?
Thanks for your time and efforts..
Pratik
July 06, 2009 - 8:28 pm UTC
session cached cursors CANNOT, WILL NOT, DOES NOT directly decrease the number of parses - never.
It can turn a soft parse into a softer soft parse, but a parse is a parse is a parse.
Only the APPLICATION DEVELOPER can reduce the number of parses - period.
In the first case - sqlplus, the application works like this:
loop
read input
if input = exit then EXIT;
open cursor
parse input
execute input
if input was a query - display output
close cursor
end loop
sqlplus is a simple, small, dumb program to read your input and parse/execute it. It attempts no caching of statements - it would not be worthwhile. It is just a *simple dumb command line tool to run ad-hoc sql*. It is not a programming environment really.
PLSQL on the other hand, plsql is designed to scale, to perform to write database applications in. PLSQL is doing the caching for you there. PLSQL implements (in current releases - you people with 8i and 9i will see different results than we see above) implements execute immediate like this:
If you have code like:
for i in 1 .. 10
loop
execute immmediate SOME_STRING using i;
end loop;
Under the covers, plsql does this:
for i in 1 .. 10
loop
if (last_statement_executed <> SOME_STRING)
then
if last_statement_executed is open, close it
else open a cursor for last_statement_executed.
parse SOME_STRING into last_statement_executed
end if
bind last_statement_executed
execute last_statement_executed
end loop;
so you see, plsql remembers the last statement it executed for that execute immediate and DOES NOT CLOSE IT - the next time through - if you use the same "some_string", we skip the parse.
session_cached cursors does control the size of the plsql cursor cache - plsql will only cache a number of cursors at a time and the number it caches is set by session_cached_cursors.
A reader, May 09, 2010 - 3:14 pm UTC
Sir,
'Caching' is not server concept - its client's concept
1.PL/SQL decides wther to cache a cursor or not -
2.3gl decides whether to cache or not -
Its not oracle - Oracle (server) just manages the cached cursors depneds on the session_cache_cursor and open_cursor parameters.
It is the client which allocates meemory , creates a link to SGA and make a parse call to server and make 'execute' call and 'fetch' call to the server .
Oracle Server acts as per the client' calls , parse it when it receives parse call , executes it when it receives an execute call.
Is it correct Tom ?
Thanks for your time.
May 10, 2010 - 6:40 pm UTC
'Caching' is not server concept - its client's concept
that is very vague and by itself - false. You'd need to really start with a few more assumptions first.
we cache sql in the shared pool, on the server.
clients might cache a handle to sql in the shared pool, their representation of a cursor as well. but there is caching on all sides.
A reader, May 25, 2010 - 2:05 pm UTC
"It is the client that makes 'parse' calls - we have to look at reducing the parse calls to server "
See small JDBC code :
Stmt = createstatement()------> create a handle
result_set=stmt.executestatement("select emp_name from emp");
--When Java will make a call to stmt.executestatement routine ,Database Server will receive this statement and parse it , execute it ..
again whenever I need , I will run the same statement ..
result_set=stmt.executestatement("select emp_name from emp")
1.Sir, how can I reduce the parse call from client side ,Can you please show with an example..?
2.Session_cached_cursors will reduce some 'load' that needs to be carried out as part of parsing - but will NOT reduce parse calls .. Can you please explain with some example if possible - else forward a link(asktom) where I could see more info on this ? thanks
May 25, 2010 - 6:33 pm UTC
1) use a prepared or callable statement - if you just pretend that Statement as a class
does not exist, you will become a better programmer already!!! It is that simple - never use Statement - always use prepared statements or if needed Callable Statements.
Then it should be obvious how to do it.
if ( x == null )
{
x = PrepareStatment( whatever )
}
x.bind
x.bind
x.execute
/* never close!!! */
Keep x as a class variable so it retains values between calls.
Or, google
jdbc statement caching
and enable it.
2)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1089433252941#2647252700346238363
More on session cached cursors
Dayne Olivier, July 01, 2010 - 5:23 am UTC
(Note: newer version of Oracle than original post, 10g R2)
In my example below, I have two procedures both opening cursors to all_objects. One procedure uses a string (with bind variables) to open the sql, and the other uses embeded SQL to open the identical cursor. I would have assumed the embeded SQL procedure to perform better than the string sql procedure (in terms of parsing and latching). The strange thing is though... it doesn't.
My test seems to tell me that the string SQL will make use of session cached cursors while the embeded SQL will not. Why is this happening? Why is the string SQL "reusable" and the embeded not?
dayneo@SANDBOX> create or replace procedure test_string_cursor as
2 l_rc sys_refcursor;
3 l_sql varchar2(32767);
4 l_value number := 1;
5 begin
6 -- using "where 1=1" to differenciate query from hardcoded sql
7 l_sql := 'select object_name from user_objects where 1=:bv';
8 open l_rc for l_sql using l_value;
9 close l_rc;
10 end test_string_cursor;
11 /
Procedure created.
dayneo@SANDBOX> SHOW ERRORS
No errors.
dayneo@SANDBOX> create or replace procedure test_hardcoded_cursor as
2 l_rc sys_refcursor;
3 l_value number := 2;
4 begin
5 -- using "where 2=2" to differenciate query from string sql
6 open l_rc for select object_name from user_objects where 2 = l_value;
7 close l_rc;
8 end test_hardcoded_cursor;
9 /
Procedure created.
dayneo@SANDBOX> SHOW ERRORS
No errors.
dayneo@SANDBOX> alter session set session_cached_cursors=10;
Session altered.
dayneo@SANDBOX> begin
2 -- warmup
3 test_string_cursor();
4 test_hardcoded_cursor();
5 runstats_pkg.rs_start();
6 for l_count in 1..10000 loop
7 test_string_cursor();
8 end loop;
9 runstats_pkg.rs_middle();
10 for l_count in 1..10000 loop
11 test_hardcoded_cursor();
12 end loop;
13 runstats_pkg.rs_stop(500);
14 end;
15 /
Run1 ran in 200 hsecs
Run2 ran in 201 hsecs
Run1 ran in 99.5% of the time
Name Run1 Run2 Diff
STAT...session cursor cache hi 10,000 0 -10,000
LATCH.library cache 40,004 50,011 10,007
LATCH.library cache pin 40,004 20,010 -19,994
LATCH.library cache lock 0 40,002 40,002
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
80,179 110,493 30,314 72.56%
PL/SQL procedure successfully completed.
July 06, 2010 - 2:18 pm UTC
see the other place you asked this identical question, I responded there.
A reader, July 09, 2010 - 5:26 pm UTC
Sir,
Session_cached_cursors - we will keep a pointer to the SGA -
Do we pin the cursor memory also ?
http://dba.5341.com/msg/35436.html Tom comment, (if steve adams said it, it is more then likely "true".
as they said -- a larger is normally beneficial. I am partial (opinion, no
true science here) to 100.)
1.A.i.b Important
. Be aware that this is done at the expense of increased memory allocation for every session in the this will increase UGA memory which is in the PGA
in dedicated server mode and in the SGA in shared server mode.. An application to run optimally, is necessary to analyze how parsing works
July 09, 2010 - 7:09 pm UTC
it will tend to keep them in the SGA yes, not subject to aging out. Can lead to ora-4031
but - and big but here - that is NOT a reason to NOT use this. Most definitely NOT a reason
A reader, July 09, 2010 - 7:14 pm UTC
"that is NOT a reason to NOT use this. Most definitely NOT a reason "
Why ? do you think the benefit is more than the loss .
by the way - they were mentioning about 'pinning of memory in cursor memory (private SQL area) UGA not SGA (SQL text) ?
July 19, 2010 - 7:51 am UTC
... Why ? do you think the benefit is more than the loss ....
more than 100% if at all possible, yes, the benefit is huge, so huge, I hesitated to even answer for fear that someone will grab onto the answer and outlaw the ability in their confusion.
A reader, July 09, 2010 - 7:30 pm UTC
"that is NOT a reason to NOT use this. Most definitely NOT a reason "
Why ? do you think the benefit is more than the loss .
by the way - they were mentioning about 'pinning of memory in cursor memory (private SQL area)in UGA
not SGA (SQL text) ?
My question is - We 'pin' the SQL in SGA - of course that my lead to ora-4031 but we do not 'pin' the cursor memory in PGA(dedicated) and in SGA(shared) - Correct ? just we keep a pointer to the SQL in SGA but we don't keep the memory as it is with out de-allocating ?
July 19, 2010 - 7:55 am UTC
we don't pin in the PGA as such, there is only one thing using it. we allocate and use memory in the PGA - not "pin it".
we use some amount of memory in the UGA to point to the SGA - but not the entire cursor slot in the UGA.
A reader, November 14, 2011 - 7:59 am UTC
Hi Tom,
Pl/SQL cursor Cache :
When we say ‘close’ the cursor – Pl/SQL still keeps the cursor open (will do up to session_cursor_cache parameter value)-
A cursor variable on client side (pl/sql) points to the a slot in cursor array (PGA) which in turn points to the ‘run time.pesistent’ area in PGA for that cursor and this pointer points to the SQL text/plan in the Shared pool.
When PL/SQL see the same SQL next time – It will directly send EXECUTE call with this SQL/cursor information to the database.
So only ONE parse call- MANY executions
Well – PL/SQL will give slot to server when there is more demand for ‘server cursor caching’
Session cursor Cache :
Cursor is cached in ‘database server’ point of view ,
Server maintains an array of pointers for the cursor (after 3 closes/opens) to the SQL text/plan in the SGA.
Here the Client still makes PARSE call each time it sees the same SQL but NO NEED OF LIBRACRY CAHCE CHECK
Server process understand it and directly POINTS to the SQLtext/plan in the Shared pool
Hard Parse ïƒ The client makes ‘PARSE CALL’ ïƒ Syntax,Sematntic,Optmizisation(the same SQL NOT found in shared pool),
Soft parse ïƒ The client makes ‘PARSE CALL’ ïƒ Syntax,Semantic and Library cache check and NO Optimization as the same SQL is found in shared pool
Softer Soft Parse (Tom calls it) ïƒ The client makes ‘PARSE CALL’ – Server side caching – check in ‘session_cursor_cache’
If it is found – then directly pointed to the SQL text/plan in SGA. (no syntax,ne semantic, no library check)
No PARSE ïƒ the Client does NOT make PARSE CALL –directly the CLINET make EXECUTE call to the database.
Is this understanding correct ? Many thanks for your time and help !!
November 15, 2011 - 8:39 am UTC
Here the Client still makes PARSE call each time it sees the same SQL but NO
NEED OF LIBRACRY CAHCE CHECK
there is still a check - to make sure the stuff is still in the shared pool - but we might be able to skip much of the soft parse work if it is there. But there is a check.
otherwise, looked ok at a glance.
A reader, November 15, 2011 - 8:44 am UTC
Many thanks tom for looking at this lengthy understanding :)
you were talking about this check (I mentioned this above in 'Softer Soft Parse)
Softer Soft Parse (Tom calls it) -> The client makes ‘PARSE CALL’ – Server side caching – check in ‘session_cursor_cache’
Cheers
A reader, November 15, 2011 - 9:12 am UTC
Sir,
One final point mentioned below as per my understaning from yoru book 'Effective Oracle by design' ..Please correct if it wrong :
When we use literal in SQL (select id from t where owner=’IT’) , the client
1.makes parse call to the database with the SQL text and database will do ‘syntax’ , ‘semantic’ check and also prepare the optimal execution plan (if the same SQL not found in Library cache) as the literal is known at parse time itself
2.When the client says ‘execute’- then the database will execute as per the ‘plan’ prepared in the parse call.
***Here – the execution plan is prepared by the engine at ‘PARSE CALL’
When we use bind variable in SQL (select id from t where owner=:owner) – the client will
1.make parse call to the database with the SQL text and database will do ‘syntax’ and ‘semantic’ check
2.when the client says ‘execute’- then the database will peek the first bind value and prepare optimal execution plan
**Here – the execution plan is prepared by the engine at ‘EXECUTE CALL’
November 15, 2011 - 10:51 am UTC
How about this:
if the sql you want to run REGARDLESS of whether it uses binds or not is in the shared pool already - we will do a soft parse.
if the sql you want to run is NOT in the shared pool - we do a hard parse.
the odds of a sql that uses binds being in the shared pool is infinitely larger than the odds of a sql that does not use binds is in the shared pool, in general
there is no difference to the routines used by Oracle in the shared pool management if you use (or not) binds.
A reader, November 15, 2011 - 11:08 am UTC
Many thanks Tom But I was talking about 2 SQLs (one is with binds and the other is with out binds) and assume the both SQL are NOT in shared pool ..
My question was- when the SQL plan is prepared (at parse call (or) execution call) for the SQL with binds and SQL with out binds FOR THE FIRST TIME (neither in shared pool ) ?
November 16, 2011 - 9:14 am UTC
There are opportunities for a deferred optimization (soft parse at Prepare Statement time - finish the hard parse AFTER the binds have been supplied during the Execute phase).
So, in all cases, the sql is at least soft parsed during the prepare. If there are no binds, or if the client does not support the deferred capability - then we finish the hard parse right there and then. Else, we'll wait to do the optimization and row source generation until AFTER the binds have been supplied (for bind peeking) and do that all during the Execute phase.
A reader, November 22, 2011 - 9:39 am UTC
Hi Tom,
We are going through Jonathan’s new book 'Oracle CORE..'
he was mentioning about a 'state object' when talking about 'session cursor cache' What is State Object here ?
and also Can you please elaborate the internal details of session cursor cache mechanism ? Is there any ARRAY of pointers ? and also exact difference (implementation difference) between server side caching (session cursor cache) and client (PL/SQ cursor) cache ?How are they implemented internally ?
(we understood about the parameter session_cursur_cache and the relation between Pl/SQL cursor cache and this parameter)
But we are interested to know how they are implemented Internally ? (using such as KGL locks, array of pointers etc)
Quote from the book:
<<
the cursor for that statement, and this changes the amount of work involved and the scope for contention.
By default we have the session cursor cache enabled, which means that if we call a statement often enough
Oracle (10g) will attach a KGL lock to that statement’s cursor to hold it open, and create a state object in our session memory that links to the cursor so that we have a short cut to the cursor and don’t need to search the library cache for it every time we use it. >>
you may ask us if these details are really required – may not be for day to day programming /work
but these details will help us to learn more about Oracle and make us more interested in learning Oracle internals.
and also we think you might be writing FORWARD to this book :)
Thanks
November 22, 2011 - 12:55 pm UTC
I haven't read Jonathan's book as yet and I don't have access to it.
Why wouldn't you ask Jonathan a question relating to Jonathan's book?
I'm not really into internals inasmuch as "getting the basics right". I find a lot of "internals" people have never bothered mastering just the basics...
A state object is just a fancy name, it is a data structure. With a session cached cursor - we maintain a pointer to the object in the SGA so that when you go to parse again, we can avoid having to do most of the soft parse work and just rejoin the cursor with our session.
The plsql cursor cache keeps the cursor open - so that a parse is entirely avoided. In plsql - if you have a stored procedure with five sql statements in it - and you called that procedure 1,000,000 times in a single session - it is HIGHLY likely that you would observe just 5 parses - and 5,000,000 executes.
If on the other hand, the five sql statements were in the client - say java - and you opened and closed them each time you called a subroutine (also known as a "method") - you would likely find 5,000,000 parses and 5,000,000 executes. With session cached cursors - you would still find 5,000,000 parses but most of them would be a "softer soft parse" - a session cursor cache hit. It won't lower the parse count statistic, it will however make the parse a little easier.
How they are implemented internally is simply not relevant. I don't really care. I know what they do, and what they provide. That is all we really need to know.
plsql cursor cache is better then session cached cursors is better then a true soft parse is better than a hard parse.
Remember that and you'll be good to go.
by the way...
FORWARD http://asktom.oracle.com/Misc/first-there-was.html :)
is it back in 11g?
talek, January 12, 2012 - 3:56 am UTC
Hi,
In 11g I really don't get it! I thought that in 10g they improved the plsql engine so that the "parse once, execute many" mechanism takes place even for native dynamic sql. In fact, there's an example above for 10g. Running the same on a 11g database I end up with the following:
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 12 11:12:12 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Connected.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL>
SQL> alter session set session_cached_cursors=10;
Session altered.
SQL>
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%';
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 5
session cursor cache count 10
SQL>
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..100
5 loop
6 execute immediate 'select count(*) from dual d1' into l_cnt;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%';
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 105
session cursor cache count 10
But, looking at the tkprof report I can see that the "parse once, execute many" is used:
select count(*)
from
dual d1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.00 0.00 0 0 0 0
Fetch 100 0.00 0.00 0 0 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 0.00 0.00 0 0 0 100
Can you explain why the "session cursor cache hits" statistic is incremented? My assumption was that only in case of a "softer soft parse" the "session cursor cache" would be involved.
Many thanks!
January 13, 2012 - 8:53 am UTC
hmmm, something has changed there definitely. I think there could be an issue where they are incrementing that statistics erroneously. I'll file a bug on that.
Something wrong with session_cached_cursor in 11.2.0.3 ?
Vishal, February 17, 2012 - 9:55 am UTC
Create table test (a number);
Insert into test values(1);
commit;
-- Session Cached Cursor is set to 100.
-- Run this SELECT 100 times.
SELECT * FROM TEST WHERE A=1 ;
SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT='SELECT * FROM TEST WHERE A=1';
Output
=======
100
(This is expected)
Hold on , don't exit this session. Now flush the shared pool
ALTER SESSION FLUSH SHARED_POOL;
SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT='SELECT * FROM TEST WHERE A=1';
Output
=======
No rows selected
(This is expected)
Now , run the same select statement just once.
SELECT * FROM TEST WHERE A=1 ;
SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT='SELECT * FROM TEST WHERE A=1';
Output
=======
101
V$SQLAREA now shows 101 executions. But the fact of the matter is that the previous 100 executions have been flushed out of the shared pool. So , this count should be 1 .
I notice this behavior only in 11.2.0.3 and not in the earlier patchsets/versions.
Further , if I set session_cached_cursor=0 ,
I don't get the same issue. I correctly see the execution count as 1
global context policy and open cursors
A reader, March 26, 2012 - 8:44 am UTC
hi tom, we are using gc in our app to get the where predicate of a table (through policy) for
specific client_id, a row in v$globalcontext is : GC_NAME, TABLE_NAME, SOME_WHERE_PREDICATE , NULL
(username), client_id. when the user select a table we return in the policy function the where that
return from sys_context(GC_NAME,TABLE_NAME).
my problem :
A session connect with specific client_id, exec proc A that select from table which has policy
until here everything ok
next i want the session would select from table specific info without policy so
i put null in the client_id (the globalcontext has no row for null) and then
when the user exec proc A (that select from the table that has policy) the query is the same
query as before the change in the client_id.
my guess that this happened because the cursor is still open in the session so there is no need
to do parsing again.
when i exec "alter session set SESSION_CACHED_CURSOR=0" before i put null in the client_id
the problem was solved.
we have connection pool in our app and this flow of actions occur in different types of code everytime.
Is there more elegant solution to this problem?
DYNAMIC SQL in plql CANNOT cache the cursor
A reader, March 27, 2012 - 1:42 am UTC
Hi Tom,
Not quite understand what do you mean by 'DYNAMIC SQL in plql CANNOT cache the cursor'.
Is 'cache' here mean cached in shared pool?
I tested it with below, and can saw all the dynamic SQL exists in v$sqlarea, is it a evidence that the cursor is cached?
create table test(id int, object_name varchar2(100));
declare
v_sql varchar2(100);
v_id number;
v_res test%rowtype;
begin
for i in 1..100 loop
v_id := i;
v_sql := 'select /* test cursor */ * from test where id='||v_id;
execute immediate v_sql into v_res;
end loop;
end;
/
select SQL_FULLTEXT from v$sqlarea where SQL_FULLTEXT like '%test%cursor%test%';
March 27, 2012 - 8:08 am UTC
the caching was referring to the ability of PLSQL to cache a cursor open for you transparently. However, things change over time, plsql can cache native dynamic sql statements in some conditions. For example:
ops$tkyte%ORA11GR2> create or replace procedure p( x in number )
2 as
3 l_cursor sys_refcursor;
4 l_dummy dual.dummy%type;
5 begin
6 for x in ( select * from dual static_sql ) loop null; end loop;
7
8 execute immediate 'select * from dual dynamic_sql_' || x into l_dummy;
9 end;
10 /
Procedure created.
In that procedure, the static sql query will be cached open. If we run that 11 times - the odds are the static_sql query will be parsed ONCE in our session.
The other query, the native dynamic sql performed using execute immediate, will probably have to parse, even the same sql statement, more than once in many cases. It is true that the parse will be a SOFT parse after the hard parse is done, but the huge overhead of the parse will be there.
The native dynamic sql can be cached IF the same statement is executed over and over without a different statement being executed. For example, I ran:
ops$tkyte%ORA11GR2> exec dbms_monitor.session_trace_enable
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(1)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(2)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(1)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(2)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(1)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(2)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(3)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(3)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(3)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(3)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec p(3)
PL/SQL procedure successfully completed.
in the tkprof, you'll see:
SELECT * FROM DUAL STATIC_SQL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.00 0 22 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 0.00 0.00 0 22 0 11
********************************************************************************
select * from dual dynamic_sql_1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 6 0 3
********************************************************************************
select * from dual dynamic_sql_2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 6 0 3
********************************************************************************
select * from dual dynamic_sql_3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 10 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 10 0 5
As you can see - STATIC_SQL was parsed once, kept open and executed 11 times. Perfection!!!
dynamic_sql_1 and dynamic_sql_2 was parsed 3 times and executed three times. That is because we did 1 then 2 then 1 then 2 then 1 then 2 in order - we didn't execute the SAME dynamic sql statement time after time, we switched them up.
But then we did dynamic_sql_3 5 times in a row - we got the parse once, execute many.
so that is the caching (keeping the cursor open - truly open) that I was talking about and also demonstrates how it is different in 10g now - there can be some caching going on.
why session_cached_cursor does not work for native dynamic sql in PL/SQL?
A reader, March 28, 2012 - 12:57 am UTC
Hi Tom,
Got your point now.
I tested and really see dynamic_1/2 got parsed more than once.
I just came up another question that
during my testing, i checked 'session_cached_cursor', it is 50, so per your response below, it should
got dynamic_1/2 cached(kept open), so why not?
'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)'.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
March 28, 2012 - 8:54 am UTC
session cached cursors does two things:
a) controls the size of the plsql cursor cache, the cache of cursors plsql keeps open. I demonstrated that since 10g - native dynamic sql CAN use that cache - assuming you do the same sql statement over and over (it can only cache the last one - that is a design decision, if it sees you executing the same sql over and over - it will keep it open, otherwise - to avoid flooding the plsql cursor cache with unique sql that won't be reused - it closes it and opens a new one)
b) can help a soft parse become a softer soft parse. that was demonstrated in the original answer above.
so, in short, native dynamic sql fully utilizes both the plsql cursor cache when you execute the same sql repeatedly, and the session cache cursors (softer soft parse) cache as well.
Parse = Execute
Krishna, April 27, 2012 - 2:38 pm UTC
Hello Tom:
I have a very simple case and I am submitting the tkprof output. I executed one query over and over again continuously. I am getting count parse = execute. Should I not parse once and execute many times? Why is the fetch count 30 for getting 15 rows? This is a dev system and very few users are there.
Parameters:
session_cached_cursors = 20.
cursor_sharing=EXACT
open_cursors=1000
What should I look for?
select acct_no
from
mrmasterp where acct_no=9999999999
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.01 0.00 0 0 0 0
Execute 15 0.01 0.00 0 0 0 0
Fetch 30 0.00 0.00 2 45 0 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 60 0.02 0.00 2 45 0 15
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 10217
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX UNIQUE SCAN MRMASTERP_ACCT_NO (cr=3 pr=2 pw=0 time=423 us)(object id 817194)
Thanking you
Krishna
April 28, 2012 - 8:20 am UTC
The database parses whenever you tell it to parse. If you have code that does:
prepare statement
bind statement
execute statement
close statement
and you execute that code 15 times, you'll have 15 parses, 15 executes. You - and you alone - can control the parsing.
Fetch is 30 because you are using single row fetches and we have to find the end of the data - we have to fetch past the end of the result set in order to discover "there is no more data". Since you are single row fetching (you should fix that too), you'll have two fetches for every cursor you retrieve.
You should also avoid executing a teeny tiny sql statement like this over and over and over again in a loop - why didn't you just join the "bigger" sql statement that is providing inputs for this teeny tiny sql statement to this small sql statement? Why are you looping at all?
Followup
Krishna, April 28, 2012 - 3:34 pm UTC
Mr Tom:
Thank you for the explanation. I learned something. I ran this from sql*Plus for a case study when reading your Architecture book. session_shared_cursor was new to me.
Is it a fact that Sqlplus always asks for a parse and does all the steps as you mentioned everytime?
Thanks
Krishna
April 30, 2012 - 8:13 am UTC
yes, sqlplus - a very simple client tool to execute arbitrary sql - always parses every statement it receives.
Thanks
Krishna, April 30, 2012 - 9:01 am UTC
Mr Tom:
Thank you. Now I understand. Your "Effective Oracle by Design" is throwing more light in what our team here is doing wrong in coding.
Krishna
Effect of session_cached_cursors on literal SQL's
shaji, January 24, 2014 - 1:32 pm UTC
Tom,
Iam on 11.2.0.2
Does the literal SQL's use session cached cursors ? I have traced a session by running a literal SQL again & again. But on all cases, the trace showed that it was parsed. Although hard parse was only once.
Is this an expected behaviour ?
~Shaji