This post is trying to explain how parameter ‘session_cached_cursors’ works, and also its advantages and disadvantages.

In oracle official document, parameter ‘SESSION_CACHED_CURSORS’ is explained as this:

“specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.”

Base on the explanation,it is hard to understand what difference brought actually if a cursor was moved into the session cursor cache, and what does “do not need to reopen the cursor” mean? Then what is the advantages and disadvantages of this facility?

This post did some further investigations on this.

First,let`s see what happened if a sql cursor is moved into session cursur cache.

When a parse call is issued,a session will usually locate the required cursor in the shared pool,assuming that the statement already exists,then this is called a soft parse. Despite using significantly less resources than hard parses,soft-parsing SQL statements still incurs library cache latch and CPU overhead that could prove significant in high throughput system.

The benefit of session_cached_cursors is avoiding the overhead of a session locating the SQL statement in the shared pool when it next attempts to re-parse it. Avoiding this search means there will be fewer library cache latch gets required to locate the cursor and also less CPU.

Let`s make a testing to verify this:

Before starting this testing, I want to introduce two fixed tables,they are X$KSMSP AND X$KGLOB.

X$KSMSP stands for “Kernal Storage Memory Management SGA Heap”, is used to record shared pool memory chunk allocation details;

X$KGLOB: Kernel Generic Library Cache Manager Object, is used to record database objects that are cached in the library cache.

Creating a testing table ‘test’ in schema lt_test:

We put a simple query on table lt_test.test:

Then, let`s check how oracle allocate memory for this cursor in shared pool:

Two rows returned from above query,the second row describes the parent cursor memory allocation,the key information associated with the parent cursor is the text of the SQL statement.The first row is describing the memory allocation for child cursor, key elements associated with the child cursor are the execution plan and the execution environment.this is the part we should care about in this testing.

From above query,in the first row,we can see oracle allocated 2 memory heaps for child cursor ‘select * from test where object_id=100’,they are KGLOBHS1 and KGLOBHS6, and their size are 3672 and 8088, their memory address are 00000003D463CB78 and 00000003D17EADE8 separately.

Let`s check the momory details from X$KSMSP:

Oracle allocated 5 chunks for above 2 heaps,Heap 0 is smaller one, it is the heap for library cache metadata(it is usually called heap 0,some times the name may be a little different.

And another heap is for the execution plan,called sql area.

Heap 0 is comprised of 3 memory chunks, each of them are 1112 bytes;

Heap sql area is comprised of 2 memory chunks,each of them are 4096 bytes.

If we setting parameter session_cached_cursors to cache session cursors, after 3 times of execution, this cursor will be moved into session cursor cache, at the memory level, this means heap 0 will be pinned in the memory.

Let`s verify this by a testing:

executing this sql 3 times,then this child cursor will be moved into session cursor cache.

the ‘GRANT’ statement is a DDL operation on table LT_TEST.TEST,it will make child cursor memory dependent on this table freed from shared pool.From below checking,we can see the sql area heap of this cursor has been freed, but the heap 0 is still there,it is pinned in memory:

But what happen if we disable the session cursor cache?

Exit session 1, flush shared pool,then all memory heaps of this cursor will be freed:

Open a new session and disable session cursor cache in it, called session 1:

Checking memory info:

Here,heap 0 is called Pcursor.

In session 1:

executing  sql ‘select * from test where object_id=100;’ 3  times:

Checking memory again, we can find  both two memory heaps has been freed:

This result is different with enable session_cached_cursors.

From the testing, we know:

after a cursor is moved into session cursor cache,Heap 0 will be pinned,for the whole child cursor, this is partially pinned, for heap ‘sql area’ is not pinned.

Then,let`s check what is the advantage and disadvantage of this facility?

Advantages

From previous description,we are told the benefit of this facility is it can reduce the library cache latch gets and less CPU.

Let`s verify it with testing.

Preparing testing script:

connect to lt_test user to run script gen_ses_cac_cur_testing1.sql and gen_ses_cac_cur_testing2.sql,they will generate scripts ses_cac_cur_testing1.sql and ses_cac_cur_testing2.sql separately,which will run sql 'select * from test where object_id=100;’ for 10000 times, and gather 10046 trace info for this running.

Checking lock status of child cursor 'select * from test where object_id=100;’ before running script ses_cac_cur_testing1.sql and ses_cac_cur_testing2.sql:

Pay attention to the kglobt23 column,this column count how many library cache lock was requested by this cursor. From above output,this child cursor requested library cache lock for 3 times.

(you may be curious of how do we know column kglobt23 means locked_total,you can know this by checking the definition of view V$DB_OBJECT_CACHE in oracle 11gR2).

Run script ses_cac_cur_testing1.sql with parameter session_cached_cursors set to 20, and run script ses_cac_cur_testing2.sql with parameter session_cached_cursors disabled.

After running these two scripts, we get the library cache lock latch statistics again by checking column kglobt23 in table x$kglob, and checking sql parse CPU time from 10046 trace.Comparing the result got from x$kglob before and after running testing scripts,we can get the library cache lock requests statistics. From TKPROF report of 10046 trace, we can get the CPU time statistics of parsing sql “select * from test where object_id=100;”

I didn`t list the details here, but summarized them in below table:

From this table,we can see,enable session_cached_cursors parameter will save library cache lock latch request a lot,library cache lock requests decreased from 10000 to 3,and then save CPU resource also.

Why enable session_cached_cursors can saving library cache lock latch request?

After a sql cursor is parsed and stored in library cache, it is a library cache object then. When using a library cache object,we need request two types of locking structure on it,they are KGL lock(library cache lock) and KGL pin(library cache pin).

When a session has a KGL lock on an library cache object(it`s sql cursor here),that means it has a lock on the parent cursor and on the relevant child cursor, then the oracle code has a mechanism that allows it to go directly to the address it has for the object rather than having to grab the library cache hash chain latch and search the hash chain.

Although a KGL lock will hold an library cache object in memory,it`s heap 0 ,from previous testing,there are parts of the object that are dynamically re-creatable(the execution plan for an SQL statement,for example),and these can still be discarded if there is a heavy demand for memory even if you have a KGL lock in place.However,when you are actually using an object,you need to ensure that the re-creatable bits can`t be pushed out of memory, so the KGL pin comes,it pin the object to protect it.

When we enable the session cursor cache by set parameter session_cached_cursors greater than 0,then if we call a statement often enough,oracle will attach a KGL lock to the statement`s cursor to hold it open(by pin heap 0,I think),and create a state object in UGA that links to the cursor so that we have a shot cut to the cursor and don`t need to search the library cache for it every time we use it.

What is the disadvantage of this facility?

In order to archieve this optimization,oracle must ensure that any cursor which moved into the session cursor caches must remain ‘partially pinned’ down in the shared pool.The more cursors pinned in the shared pool, the fewer the candidate cursors available for freeing memory.If there are a large number of cursors pinned,it may mean you will need to allocate more shared pool memory to ensure you don`t run out of memory.

Besides, when talking about ‘partially pinned’,it means heap 0 was pinned.Heap 0 is the smaller of the heaps,usually comprised of several 1K chunks, so oracle put this also as a cause of shared pool memory franment(in metalink doc: 146599.1).

Reference:

<>  --An Oracle While Paper

<>  --Jonathan Lewis