In first part of this article, we
- Learnt
- What is ‘Session Cursor Caching’?
- How to Enable Session Cursor Caching?
- Explored ‘Session Cursor Caching’ in detail by means of following practical scenarios:
- Caching of the cursor in SQL*Plus
- Caching of the cursor in PL/SQL Block
In this second and final part of this article, we will find out the impact of session cursor caching on parsing when a SQL statement is issued using
- SQL*Plus
- PL/SQL
- Anonymous Block
- Stored Procedure
We will also learn how to monitor and use SESSION_CACHED_CURSORS for tuning.
I. Impact of session cursor caching on parsing
We already know that when cursor of a SQL statement gets cached in session cache, it greatly reduces the cost of soft parsing. Next we will try to find out whether session cursor caching reduces the number of soft parses as well:
-
- Using SQL*Plus
- Using Anonymous PL/SQL Block
- Using PL/SQL Stored Procedure
a) Impact of session cursor caching on parsing using SQL*Plus
We will execute a SQL statement 100 times in a SQL*Plus session and try to find out the number of times it is parsed when session cursor caching is disabled / enabled.
Case-I: Session Cursor Caching disabled
- Flush shared pool and start an HR session with SESSION_CACHED_CURSORS=0
SYS>alter system flush shared_pool;
HR>conn hr/hr
alter session set session_cached_cursors=0
sho parameter cached
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
- After hard parsing the statement, check initial parsing statistics for the SQL statement and HR session
HR>select /*+ session_cache */ count(*) from hr.employees;
SYS>select sql_text, parse_calls, executions from V$sql
where sql_text like '%/*+ session_cache */%';
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select /*+ session_cache */ count(*) from hr.emplo 1 1
yees
. . .
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 61 CACHED_CURSORS 0
HR 35 61 CURSORS_HITS 0
HR 35 61 HARD PARSES 65
HR 35 61 PARSES 488
- Execute the statement 100 times in SQL*Plus session and observe that the statement is soft parsed (searched in library cache) as many times (100) as it is executed.
HR>@exec_sql
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 61 CACHED_CURSORS 0
HR 35 61 CURSORS_HITS 0
HR 35 61 HARD PARSES 65
HR 35 61 PARSES 588
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select /*+ session_cache */ count(*) from hr.emplo 101 101
yees
select sql_text, parse_calls, executions from 1 1
V$sql where sql_text like '%/*+ session_cache
*/%'
Case-II: Session Cursor Caching enabled
- Now, we will enable session cursor caching in a new HR session by setting SESSION_CACHED_CURSORS=1
SYS>alter system flush shared_pool;
HR>conn hr/hr
alter session set session_cached_cursors = 1;
- Execute the SQL statement 4 times in the session so that it gets cached in session cache.
HR>select /*+ session_cache */ count(*) from hr.employees;
select /*+ session_cache */ count(*) from hr.employees;
select /*+ session_cache */ count(*) from hr.employees;
select /*+ session_cache */ count(*) from hr.employees;
SYS>@search_sess_cache
USERNAME SID SQL_TEXT CURSOR_TYPE
---------- ---------- ---------------------------------------- -----------
HR 35 select /*+ session_cache */ count(*) fro DICTIONARY
m hr.employees LOOKUP CURS
OR CACHED
- Find out initial parse statistics for the SQL and HR session
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select /*+ session_cache */ count(*) from hr.emplo 4 4
yees
. . .
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 65 CACHED_CURSORS 1
HR 35 65 CURSORS_HITS 15
HR 35 65 HARD PARSES 24
HR 35 65 PARSES 162
- Execute the statement 100 times in a SQL*Plus HR session and observe that after the statement is found in session cache 100 times (CURSORS_HITS = 115): the cursor’s presence and validity is checked in library cache (soft parsed) as many times (100) as it is executed.
HR>@exec_sql
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 65 CACHED_CURSORS 1
HR 35 65 CURSORS_HITS 115
HR 35 65 HARD PARSES 24
HR 35 65 PARSES 262
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select /*+ session_cache */ count(*) from hr.emplo 104 104
yees
Hence, we have observed that if a SQL statement is repeatedly executed in a SQL*Plus session, it is parsed as many times as it is executed even if session cursor caching is enabled,. In other words, SQL*Plus
- Makes the soft parse a little less costly by avoiding syntax and semantics checks
- Cannot reduce parsing since for every execution; even if the statement is found in session cache, the presence and validity of its cursor in library cache needs to be confirmed.
- Is a simple command-line tool to run ad hoc SQL without the ability to cache the cursor handle.
Since SQL*Plus cannot cache the cursor handles, it is not designed to scale and hence is not really a programming environment to write database applications in. Now, we will explore PL/SQL, which can cache the cursor handles in PL/SQL cache and provides an environment in whichto code database applications. We will explore the impact of session cursor caching on parsing the SQL statements issued using anonymous PL/SQL block and PL/SQL stored procedure.
b) Impact of session cursor caching on parsing using anonymous PL/SQL Block
Now, we will issue the same SQL statement 100 times using an anonymous PL/SQL block and observe the number of times it is parsed when session cursor caching is disabled / enabled.
Case-I: Session Cursor Caching disabled.
- Flush shared pool and start an HR session with SESSION_CACHED_CURSORS=0
SYS>alter system flush shared_pool;
HR>conn hr/hr
alter session set session_cached_cursors=0
sho parameter cached
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
- Execute an anonymous PL/SQL block in which the SQL is executed 100 times. Get parsing statistics for PL/SQL, SQL and HR session. Observe that since session cursor caching is disabled, SQL is parsed as many times (100) as it is executed.
HR> begin
for i in 1 .. 100
loop
execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
end loop;
end ;
/
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 51 CACHED_CURSORS 0
HR 35 51 CURSORS_HITS 0
HR 35 51 HARD PARSES 61
HR 35 51 PARSES 488
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select sql_text, parse_calls, executions from 1 1
V$sql where sql_text like '%/*+ session_cache
*/%'
begin for i in 1 .. 100 loop 1 1
execute immediate 'select /*+ session_
cache */ count(*) from hr.employees' ; end
loop; end ;
select /*+ session_cache */ count(*) from hr.emplo 100 100
yees
- Execute the anonymous PL/SQL block a second time and observe that the PL/SQL block is hard parsed since it is anonymous. Also, SQL is again parsed as many times (100) as it is executed.
HR> begin
for i in 1 .. 100
loop
execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
end loop;
end ;
/
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 51 CACHED_CURSORS 0
HR 35 51 CURSORS_HITS 0
HR 35 51 HARD PARSES 62
HR 35 51 PARSES 589
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select sql_text, parse_calls, executions from 2 2
V$sql where sql_text like '%/*+ session_cache
*/%'
begin for i in 1 .. 100 loop 2 2
execute immediate 'select /*+ session_
cache */ count(*) from hr.employees' ; end
loop; end ;
select /*+ session_cache */ count(*) from hr.emplo 200 200
yees
Hence, when session cursor caching is disabled and a SQL statement is executed multiple times within an anonymous PL/SQL block, the statement is parsed every time it is executed.
Case-II: Session Cursor Caching enabled.
- Now, we will start a new HR session and set SESSION_CACHED_CURSORS=1 so that PL/SQL cache can now keep one cursor open.
SYS>alter system flush shared_pool;
HR>conn hr/hr
alter session set session_cached_cursors = 1;
- Execute the same anonymous PL/SQL block in this session.
HR> begin
for i in 1 .. 100
loop
execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
end loop;
end ;
/
- Capture parsing and cursor statistics for the SQL and HR session. Note that for 100 executions of the SQL statement, it has been parsed only once since its cursor handle got cached in PL/SQL cache after the first execution of the SQL and kept open thereafter.
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 53 CACHED_CURSORS 0
HR 35 53 CURSORS_HITS 113
HR 35 53 HARD PARSES 61
HR 35 53 PARSES 375
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
begin for i in 1 .. 100 loop 1 1
execute immediate 'select /*+ session_c
ache */ count(*) from hr.employees' ; end l
oop; end ;
select sql_text, parse_calls, executions from 1 1
V$sql where sql_text like '%/*+ session_cache
*/%'
select /*+ session_cache */ count(*) from hr.emplo 1 100
yees
- Execute the anonymous PL/SQL block once again and note that the cursor for the SQL is found in the PL/SQL cache for all 100 executions (CURSORS_HITS = 213). Note that, since the cursor of the SQL was closed after the previous execution of the PL/SQL block, it is reopened on the first execution of the SQL this time, thereby causing a soft parse. The subsequent 99 parse calls find the cursor in the cache and do not need to reopen the cursor, so that the SQL statement is parsed only once for all the 100 executions.
HR> begin
for i in 1 .. 100
loop
execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
end loop;
end ;
/
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 53 CACHED_CURSORS 0
HR 35 53 CURSORS_HITS 213
HR 35 53 HARD PARSES 61
HR 35 53 PARSES 377
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
begin for i in 1 .. 100 loop 2 2
execute immediate 'select /*+ session_c
ache */ count(*) from hr.employees' ; end l
oop; end ;
select sql_text, parse_calls, executions from 2 2
V$sql where sql_text like '%/*+ session_cache
*/%'
select /*+ session_cache */ count(*) from hr.emplo 2 200
yees
Hence, if a SQL statement is executed multiple times within an anonymous PL/SQL block with session cursor caching enabled, the statement is parsed once per execution of the block; i.e., if above anonymous PL/SQL block is executed 5 times, while the SQL statement will be executed 500 times, it will be parsed only 5 times. During an execution of an anonymous PL/SQL block in which a SQL is repeatedly executed, the cursor handle of the SQL gets cached after its first execution and kept open thereafter so that subsequent parse calls do not need to reopen it; i.e. ,no more parses. After the block has been executed, the cursor of the SQL is closed so that the next execution of the block requires library cache to be visited to confirm the presence and validity of the cursor at the location pointed to by the pointer in session cache, thereby causing soft parse.
c) Impact of session cursor caching on parsing using PL/SQL Stored Procedure
Now, we will issue the same SQL statement 100 times from within a PL/SQL block in a stored procedure and observe the number of times it is parsed when session cursor caching is disabled / enabled.
Case-I: Session Cursor Caching disabled.
- Flush shared pool and start an HR session. Create a stored PL/SQL procedure sess_cache in which the SQL is executed 100 times. Set SESSION_CACHED_CURSORS=0 in HR session.
SYS>alter system flush shared_pool;
HR>conn hr/hr
create or replace procedure sess_cache as
begin
for i in 1 .. 100
loop
execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
end loop;
end ;
/
alter procedure sess_cache compile;
alter session set session_cached_cursors = 0;
sho parameter cached
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
- Execute the procedure sess_cache. Get initial parsing statistics for PL/SQL, SQL and HR session. Note that the SQL is parsed as many times as it is executed since session cursor caching is not enabled.
HR> exec sess_cache;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 71 CACHED_CURSORS 0
HR 35 71 CURSORS_HITS 0
HR 35 71 HARD PARSES 179
HR 35 71 PARSES 1255
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select sql_text, parse_calls, executions from 1 1
V$sql where sql_text like '%/*+ session_cache
*/%'
select /*+ session_cache */ count(*) from hr.emplo 100 100
yees
- Execute the procedure a second time and observe that the SQL is parsed as many times (100) as it is executed. The PL/SQL block is soft parsed since it is part of a stored procedure.
HR> exec sess_cache;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 71 CACHED_CURSORS 0
HR 35 71 CURSORS_HITS 0
HR 35 71 HARD PARSES 179
HR 35 71 PARSES 1356
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select sql_text, parse_calls, executions from 2 2
V$sql where sql_text like '%/*+ session_cache
*/%'
select /*+ session_cache */ count(*) from hr.emplo 200 200
yees
Hence, when session cursor caching is disabled and a SQL statement is executed multiple times from within a PL/SQL block in a stored procedure, the statement is parsed every time it is executed.
Case-II: Session Cursor Caching enabled.
- Now, we will start a new HR session and set SESSION_CACHED_CURSORS=1 so that PL/SQL cache can now keep one cursor open.
SYS>alter system flush shared_pool;
HR>conn hr/hr
create or replace procedure sess_cache as
begin
for i in 1 .. 100
loop
execute immediate 'select /*+ session_cache */ count(*) from
hr.employees' ;
end loop;
end ;
/
alter procedure sess_cache compile;
alter session set session_cached_cursors = 1;
- Execute the PL/SQL stored procedure for the first time in this session. Note that the SQL is parsed once for 100 executions since its cursor gets cached in PL/SQL cache after the first execution.
HR> exec sess_cache;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
———- ———- ———- ————– ———-
HR 35 73 CACHED_CURSORS 0
HR 35 73 CURSORS_HITS 113
HR 35 73 HARD PARSES 178
HR 35 73 PARSES 1138
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
————————————————– ———– ———-
select sql_text, parse_calls, executions from 1 1
V$sql where sql_text like '%/*+ session_cache
*/%'
select /*+ session_cache */ count(*) from hr.emplo 1 100
yees
- Execute the stored procedure a second time. Observe that the cursor for the SQL is found in the session cache for all the 100 executions (CURSORS_HITS = 213). Also, PARSE_CALLS remain at the same value (1) as earlier, since the cursor cached in PL/SQL cache is kept open even after execution of the procedure is over so the next execution does not require reopening it and a soft parse is avoided.
HR> exec sess_cache;
SYS>@sesstat
USERNAME SID SERIAL# STATISTIC VALUE
---------- ---------- ---------- -------------- ----------
HR 35 73 CACHED_CURSORS 0
HR 35 73 CURSORS_HITS 213
HR 35 73 HARD PARSES 178
HR 35 73 PARSES 1139
SYS>@sql_stats
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
select sql_text, parse_calls, executions from 2 2
V$sql where sql_text like '%/*+ session_cache
*/%'
select /*+ session_cache */ count(*) from hr.emplo 1 200
yees
Hence, if a SQL statement is executed multiple times from within a PL/SQL block in a stored procedure with session cursor caching enabled, the statement is parsed once per session for any number of executions of the procedure; i.e., if the above PL/SQL procedure is executed 5 times, while the SQL statement will be executed 500 times, it will be parsed only once. During an execution of a stored procedure in which a SQL is repeatedly executed, the cursor handle of the SQL gets cached after its first execution and kept open for the life of the session; i.e., even after the procedure has been executed, the cursor of the SQL is kept open. As a result, as long as the cursor is cached in PL/SQL cache, subsequent executions of the procedure need not reopen it, thereby avoiding any more soft parses.
II. Monitor and use SESSION_CACHED_CURSORS for tuning
It is evident that, whereas session cursor caching will not reduce hard parses, it can
- Make soft parses less costly
- Reduce the number of soft parses (only if PL/SQL is used)
In other words, session cursor caching can improve performance only when same SQL statements are resubmitted. This can occur in many applications including FORMS based applications where users often switch between forms. Every time a user switches to a new form, all the SQL statements opened for the old form will be closed. The SESSION_CACHED_CURSORS parameter will cause closed cursors to be cached within the session so that a subsequent call to parse the statement will bypass the parse phase.
Hence, we should start by finding out whether the application executes the same SQL statements repeatedly. For this, we need to look at the ‘SQL ordered by Parse Calls’ section of the AWR / Statspack report. If the parse to execute ratio of many of the SQL statements / procedures is found to be close to 1, increasing SESSION_CACHED_CURSORS might help.
Once we know that the application is submitting same SQL statements repeatedly, next, we need to know if the current value of SESSION_CACHED_CURSORS is appropriate.
If SESSION_CACHED_CURSORS is low, it is quite probable that the cursor for a SQL has aged out of session cache by the time same it is resubmitted, resulting in low session cursor cache hits. We can look at following sections of the AWR report:
- Instance Efficiencies: A low value of Execute to Parse % ratio in the AWR report indicates that the SQL is being parsed most of the time when it is executed. This could be because of either or both of the following:
- The SQL is being issued repeatedly from SQL*Plus. It would be advisable to issue such SQL statements using the PL/SQL stored procedure.
- The current value of the parameter SESSION_CACHED_CURSORS is insufficient, causing the aging out of cursors from session cache. If this is the case, you may benefit by increasing the the value of SESSION_CACHED_CURSORS.
- Instance Activity Statistics: If the statistic session cursor cache hits << ( parse count (total) - parse count (hard) ) it indicates that very few soft parses are finding the cursor in session cache, which might be because of cursors getting aged out of session cache due to insufficient size of the cache. Increasing the value of SESSION_CACHED_CURSORS might be helpful.
Moreover, we can also issue following query against v$sysstat to compare session cursor cache hits with soft parses in the instance since startup.
SQL>select cach.value cache_hits, prs.value all_parses, hprs.value hard_parses,
prs.value - hprs.value Soft_parses
from v$sysstat cach, v$sysstat prs, v$sysstat hprs,
v$statname nm1, v$statname nm2, v$statname nm3
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and hprs.statistic#=nm3.statistic#
and nm3.name= 'parse count (hard)' ;
CACHE_HITS ALL_PARSES HARD_PARSES SOFT_PARSES
---------- ---------- ----------- -----------
64139 49182 2547 46635
If CACHE_HITS << SOFT_PARSES, it may indicate that cursors are not getting cached in the session as size of the cache is insufficient.
Since SESSION_CACHED_CURSORS can also be set at the session level, we next need to find out if the value is appropriate for the sessions that are caching a large number of cursors. For this, we will monitor the statistic "session cursor cache count" for all the sessions to identify such sessions.
SQL> select s.sid, s.username, a.value "session cursor cache count"
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'session cursor cache count'
and s.username is not null
order by a.value desc;
SID USERNAME session cursor cache count
---- --------------- --------------------------
38 HR 50
23 SYS 1
29 HR 1
Here, it can be seen that the HR session with SID 38 is caching the highest number of cursors. Now, we would like to find out whether the setting of parameter SESSION_CACHED_CURSORS is appropriate in such sessions. We will monitor the statistic 'session cursor cache count' for such sessions in concurrence with the corresponding setting of the SESSION_CACHED_CURSORS parameter and the statistic 'session cursor cache hits’.
SQL>select sess.username usr, sess.sid , hits.value CACHE_HITS,
cached.value curr_cached, par.value max_cacheable,
prs.value all_parses, hprs.value hard_parses,
prs.value - hprs.value Soft_parses
from v$session sess, v$parameter2 par,
v$sesstat cached, v$statname cached_stat,
v$sesstat hits, v$statname hits_stat,
v$sesstat prs, v$statname prs_stat,
v$sesstat hprs, v$statname hprs_stat
where sess.sid = &sid
and par.name = 'session_cached_cursors'
and hits.statistic# = hits_stat.statistic#
and hits_stat.name = 'session cursor cache hits'
and hits.sid=sess.sid
and cached.statistic# = cached_stat.statistic#
and cached_stat.name = 'session cursor cache count'
and cached.sid=sess.sid
and prs.statistic# = prs_stat.statistic#
and prs_stat.name = 'parse count (total)'
and prs.sid=sess.sid
and hprs.statistic# = hprs_stat.statistic#
and hprs_stat.name = 'parse count (hard)'
and hprs.sid=sess.sid;
Enter value for sid: 38
old 9: where sess.sid = &sid
new 9: where sess.sid = 38
USR SID CACHE_HITSCURR_CACHED MAX_CACHEABLE ALL_PARSES HARD_PARSES SOFT_PARSES
---- ---- ---------- ----------- --------------- ---------- ----------- -----------
HR 38 105 5050 172 45 127
If CURR_CACHED is close to MAX_CACHEABLE (SESSION_CACHED_CURSORS parameter) and CACHE_HITS is low compared to SOFT_PARSES, it may indicate that even though the session is using the session cache to the fullest, cache hits are low because cached cursors are getting aged out because of insufficient size of the cache. In such cases, increasing the SESSION_CACHED_CURSORS parameter for the session may reduce latch contention and improve performance.
Note that CACHE_HITS could also be low because your application is not submitting the same queries for parsing repeatedly. In such cases, even if CURR_CACHED is close to MAX_CACHEABLE, caching cursors by session won't help at all. Hence, before increasing the setting of SESSION_CACHED_CURSORS, it is advisable to look at the ‘SQL ordered by Parse Calls’ section of AWR report to confirm the presence of the PL/SQL procedure(s) with ‘Parse Calls’ close to ‘Executions’; i.e., you are parsing almost for every execute.
Once we know that the parameter SESSION_CACHED_CURSORS needs to be increased, we should consider that it can be modified both at the session and the instance level. Also, modifying it at the instance level will require instance restart as the parameter is static. If repeated executions are from specific sessions, the parameter should be increased for those sessions only, as cursor caching increases the amount of memory consumed by a session.
First, issue the following query to find out the highest number of cursors cached across all the sessions.
SQL> select max(value) from v$sesstat
where STATISTIC# in (select STATISTIC# from v$statname
where name='sessioncursor cache count');
MAX(VALUE)
----------
50
If this value equals the instance parameter SESSION_CACHED_CURSORS, you should consider increasing it.
SQL> select NAME, value from v$parameter where name = 'session_cached_cursors';
NAME VALUE
------------------------------ ----------
session_cached_cursors 50
It is worth mentioning that oversized cursor cache
- increases the amount of memory consumed by a session
- causes more time to be spent in searching the larger cache
Hence, if the highest number of cursors cached across all the sessions << SESSION_CACHED_CURSORS, the parameter may be reduced.
To monitor the benefit of increasing SESSION_CACHED_CURSORS, we can look in the AWR report at
- The 'session cursor cache hits' statistic in the 'Instance Activity Statistics' section
- The ‘execute to parse ratio %’ statistic in the 'Instance efficiencies' section
If both these statistics increase, it indicates that the repeated SQL being submitted from PL/SQL is able to take advantage of the increased value of SESSION_CACHED_CURSORS. If these statistics do not register a significant increase, it is advisable to switch to a lower value of the parameter.
Summary
- SQL*Plus
- Makes soft parse a little less costly
- Cannot reduce parsing,since for every execution, even if the statement is found in session cache, the presence and validity of its cursor in library cache needs to be confirmed.
- Is a simple command-line tool to run ad hoc SQL without the ability to cache the cursor handle.
- PL/SQL can cache cursor handles in PL/SQL cache and hence reduce parsing.
- During an execution of an anonymous PL/SQL block with repeatedly submitted SQL, if session cursor caching is enabled, the cursor handle of the SQL gets cached after its first execution and is kept open thereafter, so that subsequent parse calls do not need to reopen it. After the block has been executed, the cursor of the SQL is closed. On the next execution of the block, even if the cursor is still cached, the library cache needs to be visited to confirm the presence and validity of the cursor at the location it points to, thereby causing soft parse.
- During an execution of a stored procedure in which a SQL is repeatedly executed, if session cursor caching is enabled, the cursor handle of the SQL gets cached after its first execution and is kept open for the life of the session; i.e., even after the procedure has been executed, the cursor of the SQL is kept open. As a result, as long as the cursor is cached in PL/SQL cache, subsequent executions of the procedure in the session need not reopen it. thereby avoiding any more soft parses.
- Session cursor caching can
- Improve performance only when same SQL statements are resubmitted.
- Reduce parsing only when same SQL statements are resubmitted using PL/SQL.
Scripts used
- search_sess_cache.sql
Find out type of the cursor cached in the HR session
col cursor_type for a15
col sid for 999
col sql_text for a40
select s.username, c.sid, c.sql_text, cursor_type
from v$open_cursor c, v$session s
where s.username='HR' and
c.sid = s.SID
and c.sql_text like '%/*+ session_cache */%';
- sesstat.sql
Find out parse and session cursor cache statistics for the HR session
col username for a10
select s.username, s.sid, s.serial#, 'CACHED_CURSORS'
Statistic,
a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.username='HR'
and b.name in ('session cursor cache count')
union
select s.username, s.sid, s.serial#, 'CURSORS_HITS',
a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.username='HR'
and b.name in ( 'session cursor cache hits')
union
select s.username, s.sid, s.serial#, 'PARSES',
a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.username='HR'
and b.name in ( 'parse count (total)')
union
select s.username, s.sid, s.serial#, 'HARD PARSES',
a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.username='HR'
and b.name in ( 'parse count (hard)') ;
- sql_stats.sql
Find out parsing and execution statistics for the SQL statement
select sql_text, parse_calls, executions from V$sql
where sql_text like '%/*+ session_cache */%'
- exec_sql.sql
Issue the SQL statement 100 times using SQL*Plus
Contains following statement repeated 100 times
select /*+ session_cache */ count(*) from hr.employees;
References:
Oracle Core: Essential Internals for DBAs and Developers by Jonathan Lewis
http://www.orafaq.com/node/758
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:865497961356
https://hoopercharles.wordpress.com/2011/07/21/session_cached_cursors-possibly-interesting-details/
https://dioncho.wordpress.com/2009/03/13/the-secret-of-session_cached_cursors/
https://jonathanlewis.wordpress.com/2013/03/27/open-cursors/
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4621380200346754746
Start the discussion at forums.toadworld.com