ITPub博客

首页 > 数据库 > Oracle > 参数session_cached_cursors的工作原理及优缺点分析

参数session_cached_cursors的工作原理及优缺点分析

原创 Oracle 作者:talio 时间:2014-03-27 14:54:47 0 删除 编辑

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:

  1. sqlplus lt_test/lt_test
  2. show release
  3. release 1002000500
  4. create table test as select object_id,object_name from user_objects where 0=1;

We put a simple query on table lt_test.test:

  1. select * from test where object_id=100;

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

  1. select kglnaown OWNER,KGLNAOBJ NAME,KGLOBT03 sql_id,KGLHDPAR PAR_ADR,KGLHDADR CHILD_ADR,
  2. KGLOBHS0 Mem0,KGLOBHS1 Mem1,KGLOBHS2 Mem2,KGLOBHS3 Mem3,KGLOBHS4 Mem4,KGLOBHS5 Mem5,KGLOBHS6 Mem6,
  3. KGLOBHD0 Addr0,KGLOBHD1 Addr1,KGLOBHD2 Addr2,KGLOBHD3 Addr3,KGLOBHD4 Addr4,KGLOBHD5 Addr5,KGLOBHD6 Addr6,
  4. kglhdexc Executions,kglobpc0 Pins,decode(kglhdkmk,0,\'NO\',\'YES\') Keep,kglobt23 LOCKED_TOTAL,kglobt24 PINNED_TOTAL
  5. from x$kglob
  6. where kglnaobj like \'select * from test where object_id=100%\';
  7. OWNER
  8. ----------------------------------------------------------------
  9. NAME
  10. ------------------------------------------------------------------------------------------------------------------------
  11. SQL_ID PAR_ADR CHILD_ADR MEM0 MEM1 MEM2 MEM3 MEM4 MEM5
  12. ------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ----------
  13.       MEM6 ADDR0 ADDR1 ADDR2 ADDR3 ADDR4 ADDR5
  14. ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
  15. ADDR6 EXECUTIONS PINS KEE LOCKED_TOTAL PINNED_TOTAL
  16. ---------------- ---------- ---------- --- ------------ ------------
  17. select * from test where object_id=100
  18. fbqy56z7kmdxk 00000003DE9A2608 00000003D4419090 3672 0 0 0 0 0
  19.       8088 00000003D463CB78 00 00 00 00 00
  20. 00000003D17EADE8 1 0 NO 1 2
  21.  
  22. select * from test where object_id=100
  23. fbqy56z7kmdxk 00000003DE9A2608 00000003DE9A2608 2824 0 0 0 0 0
  24.          0 00000003D4635DE0 00 00 00 00 00
  25. 00 0 0 NO 1 1

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:

  1. select * from x$ksmsp where KSMCHPAR in (\'00000003D17EADE8\',\'00000003D463CB78\') order by KSMCHPAR,KSMCHPTR;
  2.  
  3. ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
  4. ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
  5. FFFFFFFF7B4241E8 5736 1 1 4 sql area 00000003CB728288 4096 freeabl 0 00000003D17EADE8
  6. FFFFFFFF7B424240 5735 1 1 4 sql area 00000003CB729288 4096 recr 4095 00000003D17EADE8
  7. FFFFFFFF7B34C6C8 15752 1 1 3 Heap0: KGL 00000003D17E9FA0 1112 freeabl 0 00000003D463CB78
  8. FFFFFFFF7B34C720 15751 1 1 3 Heap0: KGL 00000003D17EA3F8 1112 freeabl 0 00000003D463CB78
  9. FFFFFFFF7B34C778 15750 1 1 3 Heap0: KGL 00000003D17EA850 1112 recr 4095 00000003D463CB78

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:

  1. In session 1:
  2. conn lt_test/lt_test
  3. show parameter session_cached_cursors
  4.  
  5. NAME TYPE VALUE
  6. ------------------------------------ ----------- ------------------------------
  7. session_cached_cursors integer 20
  8. select * from test where object_id=100;
  9. /
  10. /

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

  1. grant select on test to system;

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:

  1. SYS@CNR10G > select * from x$ksmsp where KSMCHPAR in (\'00000003D17EADE8\',\'00000003D463CB78\') order by KSMCHPAR,KSMCHPTR;
  2.  
  3. ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
  4. ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
  5. FFFFFFFF7B32C618 15751 1 1 3 Heap0: KGL 00000003D17E9FA0 1112 freeabl 0 00000003D463CB78
  6. FFFFFFFF7B32C670 15750 1 1 3 Heap0: KGL 00000003D17EA3F8 1112 freeabl 0 00000003D463CB78
  7. FFFFFFFF7B32C6C8 15749 1 1 3 Heap0: KGL 00000003D17EA850 1112 recr 4095 00000003D463CB78

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:

  1. alter system flush shared_pool;
  2. System altered.
  3. select * from x$ksmsp where KSMCHPAR in (\'00000003D17EADE8\',\'00000003D463CB78\') order by KSMCHPAR,KSMCHPTR;
  4. no rows selected

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

  1. conn lt_test/lt_test
  2. alter session set session_cached_cursors=0;
  3. select * from test where object_id=100;

Checking memory info:

  1. select kglnaown OWNER,KGLNAOBJ NAME,KGLOBT03 sql_id,KGLHDPAR PAR_ADR,KGLHDADR CHILD_ADR,
  2. KGLOBHS0 Mem0,KGLOBHS1 Mem1,KGLOBHS2 Mem2,KGLOBHS3 Mem3,KGLOBHS4 Mem4,KGLOBHS5 Mem5,KGLOBHS6 Mem6,
  3. KGLOBHD0 Addr0,KGLOBHD1 Addr1,KGLOBHD2 Addr2,KGLOBHD3 Addr3,KGLOBHD4 Addr4,KGLOBHD5 Addr5,KGLOBHD6 Addr6,
  4. kglhdexc Executions,kglobpc0 Pins,decode(kglhdkmk,0,\'NO\',\'YES\') Keep,kglobt23 LOCKED_TOTAL,kglobt24 PINNED_TOTAL
  5. from x$kglob
  6. where kglnaobj like \'select * from test where object_id=100%\';
  7. OWNER
  8. ----------------------------------------------------------------
  9. NAME
  10. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  11. SQL_ID PAR_ADR CHILD_ADR MEM0 MEM1 MEM2 MEM3 MEM4 MEM5 MEM6 ADDR0 ADDR1 ADDR2 ADDR3
  12. ------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ----------------
  13. ADDR4 ADDR5 ADDR6 EXECUTIONS PINS KEE LOCKED_TOTAL PINNED_TOTAL
  14. ---------------- ---------------- ---------------- ---------- ---------- --- ------------ ------------
  15.  
  16. select * from test where object_id=100
  17. fbqy56z7kmdxk 00000003DE9A2608 00000003D4C2D8B0 3672 0 0 0 0 0 8088 00000003DE8F43B8 00 00 00
  18. 00 00 00000003CD404260 1 0 NO 1 2
  19.  
  20. select * from test where object_id=100
  21. fbqy56z7kmdxk 00000003DE9A2608 00000003DE9A2608 2824 0 0 0 0 0 0 00000003D4C2E550 00 00 00
  22. 00 00 00 0 0 NO 4 2

  23. select * from x$ksmsp where KSMCHPAR in (\'00000003DE8F43B8\',\'00000003CD404260\') order by KSMCHPAR,KSMCHPTR;
  24. ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
  25. ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
  26. FFFFFFFF7B6BD318 3195 1 1 4 sql area 00000003CBD2BAF0 4096 freeabl 0 00000003CD404260
  27. FFFFFFFF7B6BD370 3194 1 1 4 sql area 00000003CBD2CAF0 4096 recr 4095 00000003CD404260
  28. FFFFFFFF7B6611F8 6093 1 1 3 PCursor 00000003CD220188 1112 freeabl 0 00000003DE8F43B8
  29. FFFFFFFF7B6612A8 6091 1 1 3 PCursor 00000003CD220A38 1112 freeabl 0 00000003DE8F43B8
  30. FFFFFFFF7B661300 6090 1 1 3 PCursor 00000003CD220E90 1112 recr 4095 00000003DE8F43B8

Here,heap 0 is called Pcursor.

In session 1:

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

  1. grant select on test to system;

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

  1. select * from x$ksmsp where KSMCHPAR in (\\\\\\\\\\\\\\\'00000003DE8F43B8\\\\\\\\\\\\\\\',\\\\\\\\\\\\\\\'00000003CD404260\\\\\\\\\\\\\\\') order by KSMCHPAR,KSMCHPTR;
  2. no rows selected

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:

  1. --gen_ses_cac_cur_testing1.sql
  2. set echo off heading off verify off feedback off linesize 100 pagesize 500 trimspool on
  3. spool ses_cac_cur_testing1.sql
  4. prompt set pages 100 lines 200
  5. prompt spool ses_cac_cur_testing1.out
  6. prompt alter session set session_cached_cursors=20; --enable session cursor cache here
  7. prompt alter session set tracefile_identifier=\'lt\';
  8. prompt alter session set events \'10046 trace name context forever,level 12\';
  9. select \'select * from test where object_id=100;\' from all_objects where rownum<=10000;
  10. prompt alter session set events \'10046 trace name context off\';
  11. prompt spool off;
  12. spool off


  1. --gen_ses_cac_cur_testing2.sql
  2. set echo off heading off verify off feedback off linesize 100 pagesize 500 trimspool on
  3. spool ses_cac_cur_testing2.sql
  4. prompt set pages 100 lines 200
  5. prompt spool ses_cac_cur_testing2.out
  6. prompt alter session set session_cached_cursors=0; --disable session cursor cache here
  7. prompt alter session set tracefile_identifier=\\\'lt\\\';
  8. prompt alter session set events \\\'10046 trace name context forever,level 12\\\';
  9. select \\\'select * from test where object_id=100;\\\' from all_objects where rownum<=10000;
  10. prompt alter session set events \\\'10046 trace name context off\\\';
  11. prompt spool off;
  12. spool off

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.


  1. conn lt_test/lt_test
  2. @gen_sses_cac_cur_testing1.sql
  3. @gen_sses_cac_cur_testing2.sql

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:

  1. select kglnaown OWNER,KGLNAOBJ NAME,KGLOBT03 sql_id,KGLHDPAR PAR_ADR,KGLHDADR CHILD_ADR,kglobt23 LOCKED_TOTAL,kglobt24 PINNED_TOTAL
  2. from x$kglob
  3. where kglnaobj like \'select * from test where object_id=100%\';
  4. OWNER
  5. ----------------------------------------------------------------
  6. NAME
  7. ------------------------------------------------------------------------------------------------------------------------
  8. SQL_ID PAR_ADR CHILD_ADR LOCKED_TOTAL PINNED_TOTAL
  9. ------------- ---------------- ---------------- ------------ ------------
  10.  
  11. select * from test where object_id=100
  12. fbqy56z7kmdxk 00000003DE9A2608 00000003DE610028 3 4
  13.  
  14. select * from test where object_id=100
  15. fbqy56z7kmdxk 00000003DE9A2608 00000003DE9A2608 9 3

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:

session_cached_cursors=20

session_cached_cursors=0

Latch:Library cache lock

3

10000

Parse CPU time

0.37s

0.71s

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27243841/viewspace-1130735/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-05-14

  • 博文量
    17
  • 访问量
    273210