session_cached_cursors,cursor_space_for_time,gets,pin
上周末看到asktom上的一个帖子，其中有人提到session_cached_cursors和cursor_space_for_time对library cache gets&pin的影响，帖子的url为
其中tom还为我们展示了session_cached_cursors和cursor_space_for_time对library cache gets&pin在9i和10g中的影响是不同的。
先让我们来了解一下session_cached_cursors，cursor_space_for_time，library cache gets，pin
session_cached_cursors:
设置pga端的cache list的长度，当session_cached_cursors设置为0时，pga的cache list长度为0，这时候当sga中的cursor关闭的时候它相关的library cache handle的lock位被清0，从v$open_cursor里看不到这个被关闭的cursor，它服从于shared pool的lru机制，当shared pool需要新的buffer空间时，它将会被flush出shared pool。当session_cached_cursors设置为非0值时，pga的cache list长度为session_cached_cursors值的大小，同时pga cache list将会保留一份拷贝，这时候即使sga中的cursor关闭的时候它相关的library cache handle始终被加了null mode lock，当shared pool空间紧张时library cache handle始终将会被保留在shared pool中.而新的应用访问这个cursor的时候会直接去自己的pga cache list里面搜索。
cursor_space_for_time：
当设置了session_cached_cursors为非0值后，如果cursor_space_for_time值被设为false，那么当shared pool空间紧张时，虽然library cache handle不会被flush出去，但是它指向的library cached object(lco,其中包含了handle和children handle的地址，权限，类型，状态，指向kgl block的指针，其中kgl block包含了真正的代码段和执行计划等内容）将会被flush出去而不管它相关的cursor有没关闭，如果需要lco的时候将要reloads。
如果cursor_space_for_time值被设为true，那么当cursor在打开状态下，handle指向的lco将不会被flush出shared pool，这样就可以降低reloads出现的频率。不过对于sql共享做的不好的数据库，设置
cursor_space_for_time将会带来一些问题，share pool可能会出现04031的错误。
gets:
当试图parse一句sql时，oracle要先获得一个handle，在handle上加载一个lock，gets表示handle request times。
pin：
当获得handle后，定位到lco，然后pin住lco使它在被执行的时候不被flush出去。
既然理解了以上一些概念，那么我们可以通过一些代码演示session_cached_cursors,cursor_space_for_time对pin，gets的影响
在9i和10g中两个参数对pin,gets的影响也不一样
9i：
vi 1.sql
set wrap off
set linesize 100
set pagesize 0
set verify off
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
set linesize 100
set pagesize 0
set verify off
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
执行10次sql
SQL 9I>show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_space_for_time boolean FALSE
session_cached_cursors integer 0
------------------------------------ ----------- ------------------------------
cursor_space_for_time boolean FALSE
session_cached_cursors integer 0
SQL 9I>@$HOME/1.sql
NAMESPACE GETS GETHITS PINS PINHITS
--------------- ---------- ---------- ---------- ----------
SQL AREA 2942908 2939105 75218597 75211231
SQL AREA 2942909 2939106 75218600 75211234
SQL AREA 2942910 2939107 75218603 75211237
SQL AREA 2942911 2939108 75218606 75211240
SQL AREA 2942912 2939109 75218609 75211243
SQL AREA 2942913 2939110 75218612 75211246
SQL AREA 2942914 2939111 75218615 75211249
SQL AREA 2942915 2939112 75218618 75211252
SQL AREA 2942916 2939113 75218621 75211255
SQL AREA 2942917 2939114 75218624 75211258
--------------- ---------- ---------- ---------- ----------
SQL AREA 2942908 2939105 75218597 75211231
SQL AREA 2942909 2939106 75218600 75211234
SQL AREA 2942910 2939107 75218603 75211237
SQL AREA 2942911 2939108 75218606 75211240
SQL AREA 2942912 2939109 75218609 75211243
SQL AREA 2942913 2939110 75218612 75211246
SQL AREA 2942914 2939111 75218615 75211249
SQL AREA 2942915 2939112 75218618 75211252
SQL AREA 2942916 2939113 75218621 75211255
SQL AREA 2942917 2939114 75218624 75211258
可以看到gets每次增加1，pin每次增加3
修改session_cached_cursors＝100；
SQL 9I>alter session set session_cached_cursors=100;
SQL 9I>@$HOME/1.sql
SQL AREA 2942935 2939123 75218728 75211344
SQL AREA 2942935 2939123 75218730 75211346
SQL AREA 2942935 2939123 75218732 75211348
SQL AREA 2942935 2939123 75218734 75211350
SQL AREA 2942935 2939123 75218736 75211352
SQL AREA 2942935 2939123 75218738 75211354
SQL AREA 2942935 2939123 75218740 75211356
SQL AREA 2942935 2939123 75218742 75211358
SQL AREA 2942935 2939123 75218744 75211360
SQL AREA 2942935 2939123 75218746 75211362
SQL 9I>@$HOME/1.sql
SQL AREA 2942935 2939123 75218728 75211344
SQL AREA 2942935 2939123 75218730 75211346
SQL AREA 2942935 2939123 75218732 75211348
SQL AREA 2942935 2939123 75218734 75211350
SQL AREA 2942935 2939123 75218736 75211352
SQL AREA 2942935 2939123 75218738 75211354
SQL AREA 2942935 2939123 75218740 75211356
SQL AREA 2942935 2939123 75218742 75211358
SQL AREA 2942935 2939123 75218744 75211360
SQL AREA 2942935 2939123 75218746 75211362
gets不再增加，pin增加2
SQL 9I>show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_space_for_time boolean TRUE
session_cached_cursors integer 100
------------------------------------ ----------- ------------------------------
cursor_space_for_time boolean TRUE
session_cached_cursors integer 100
SQL 9I>@$HOME/1.sql
NAMESPACE GETS GETHITS PINS PINHITS
--------------- ---------- ---------- ---------- ----------
SQL AREA 932 508 3561 3159
SQL AREA 933 509 3563 3161
SQL AREA 934 510 3565 3163
SQL AREA 934 510 3566 3164
SQL AREA 934 510 3567 3165
SQL AREA 934 510 3568 3166
SQL AREA 934 510 3569 3167
SQL AREA 934 510 3570 3168
SQL AREA 934 510 3571 3169
SQL AREA 934 510 3572 3170
--------------- ---------- ---------- ---------- ----------
SQL AREA 932 508 3561 3159
SQL AREA 933 509 3563 3161
SQL AREA 934 510 3565 3163
SQL AREA 934 510 3566 3164
SQL AREA 934 510 3567 3165
SQL AREA 934 510 3568 3166
SQL AREA 934 510 3569 3167
SQL AREA 934 510 3570 3168
SQL AREA 934 510 3571 3169
SQL AREA 934 510 3572 3170
cursor_space_for_time改为ture后，pin变成每次增加1
10g：
SQL 10G>show parameter cursor
cursor_space_for_time boolean FALSE
session_cached_cursors integer 0
cursor_space_for_time boolean FALSE
session_cached_cursors integer 0
SQL 10G>@$HOME/1.sql
SQL AREA 3328 98 23112 21363
SQL AREA 3328 98 23113 21364
SQL AREA 3328 98 23114 21365
SQL AREA 3328 98 23115 21366
SQL AREA 3328 98 23116 21367
SQL AREA 3328 98 23117 21368
SQL AREA 3328 98 23118 21369
SQL AREA 3328 98 23119 21370
SQL AREA 3328 98 23120 21371
SQL AREA 3328 98 23121 21372
SQL AREA 3328 98 23112 21363
SQL AREA 3328 98 23113 21364
SQL AREA 3328 98 23114 21365
SQL AREA 3328 98 23115 21366
SQL AREA 3328 98 23116 21367
SQL AREA 3328 98 23117 21368
SQL AREA 3328 98 23118 21369
SQL AREA 3328 98 23119 21370
SQL AREA 3328 98 23120 21371
SQL AREA 3328 98 23121 21372
gets不变化，pin每次增加1
SQL 10G>alter session set session_cached_cursors=100;
SQL 10G>@$HOME/1.sql
SQL AREA 3513 98 28456 26335
SQL AREA 3513 98 28457 26336
SQL AREA 3513 98 28458 26337
SQL AREA 3513 98 28459 26338
SQL AREA 3513 98 28460 26339
SQL AREA 3513 98 28461 26340
SQL AREA 3513 98 28462 26341
SQL AREA 3513 98 28463 26342
SQL AREA 3513 98 28464 26343
SQL AREA 3513 98 28465 26344
SQL 10G>@$HOME/1.sql
SQL AREA 3513 98 28456 26335
SQL AREA 3513 98 28457 26336
SQL AREA 3513 98 28458 26337
SQL AREA 3513 98 28459 26338
SQL AREA 3513 98 28460 26339
SQL AREA 3513 98 28461 26340
SQL AREA 3513 98 28462 26341
SQL AREA 3513 98 28463 26342
SQL AREA 3513 98 28464 26343
SQL AREA 3513 98 28465 26344
设置了session set session_cached_cursors后没有变化
SQL 10G>show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_space_for_time boolean TRUE
session_cached_cursors integer 100
SQL 10G>@$HOME/1.sql
------------------------------------ ----------- ------------------------------
cursor_space_for_time boolean TRUE
session_cached_cursors integer 100
SQL 10G>@$HOME/1.sql
NAMESPACE GETS GETHITS PINS PINHITS
--------------- ---------- ---------- ---------- ----------
SQL AREA 1038 24 4404 3488
SQL AREA 1038 24 4405 3489
SQL AREA 1038 24 4406 3490
SQL AREA 1038 24 4407 3491
SQL AREA 1038 24 4408 3492
SQL AREA 1038 24 4409 3493
SQL AREA 1038 24 4410 3494
SQL AREA 1038 24 4411 3495
SQL AREA 1038 24 4412 3496
SQL AREA 1038 24 4413 3497
--------------- ---------- ---------- ---------- ----------
SQL AREA 1038 24 4404 3488
SQL AREA 1038 24 4405 3489
SQL AREA 1038 24 4406 3490
SQL AREA 1038 24 4407 3491
SQL AREA 1038 24 4408 3492
SQL AREA 1038 24 4409 3493
SQL AREA 1038 24 4410 3494
SQL AREA 1038 24 4411 3495
SQL AREA 1038 24 4412 3496
SQL AREA 1038 24 4413 3497
设置cursor_space_for_time为true后也没有变化
但是当设置cursor_space_for_time为true后pl/sql block将会使pin也停止增长
看tom的例子
ops$tkyte@ORA10G> create or replace procedure p
2 authid current_user
3 as
4 l_ns varchar2(4000);
5 l_gets number;
6 l_gethits number;
7 l_pins number;
8 l_pinhits number;
9 l_sgets number;
10 l_sgethits number;
11 l_spins number;
12 l_spinhits number;
13 begin
14 for i in 1 .. 1000
15 loop
16 execute immediate
17 'select namespace, gets, gethits, pins, pinhits
18 from v$librarycache
19 where namespace = ''SQL AREA'' '
20 into l_ns, l_gets, l_gethits, l_pins, l_pinhits;
21
22 if ( i in (1,1000) )
23 then
24 if ( i = 1 )
25 then
26 l_sgets := l_gets; l_sgethits := l_gethits;
27 l_spins := l_pins; l_spinhits := l_pinhits;
28 end if;
29 dbms_output.put_line
30 ( l_ns || to_char(l_gets,'999,999') ||
31 to_char(l_gethits,'999,999') ||
32 to_char(l_pins,'999,999') ||
33 to_char(l_pinhits,'999,999') );
34 if ( i = 1000 )
35 then
36 dbms_output.put_line
37 ( l_ns || to_char(l_gets-l_sgets,'999,999') ||
38 to_char(l_gethits-l_sgethits,'999,999') ||
39 to_char(l_pins-l_spins,'999,999') ||
40 to_char(l_pinhits-l_spinhits,'999,999') );
41 end if;
42 end if;
43 end loop;
44 end;
45 /
Procedure created.
2 authid current_user
3 as
4 l_ns varchar2(4000);
5 l_gets number;
6 l_gethits number;
7 l_pins number;
8 l_pinhits number;
9 l_sgets number;
10 l_sgethits number;
11 l_spins number;
12 l_spinhits number;
13 begin
14 for i in 1 .. 1000
15 loop
16 execute immediate
17 'select namespace, gets, gethits, pins, pinhits
18 from v$librarycache
19 where namespace = ''SQL AREA'' '
20 into l_ns, l_gets, l_gethits, l_pins, l_pinhits;
21
22 if ( i in (1,1000) )
23 then
24 if ( i = 1 )
25 then
26 l_sgets := l_gets; l_sgethits := l_gethits;
27 l_spins := l_pins; l_spinhits := l_pinhits;
28 end if;
29 dbms_output.put_line
30 ( l_ns || to_char(l_gets,'999,999') ||
31 to_char(l_gethits,'999,999') ||
32 to_char(l_pins,'999,999') ||
33 to_char(l_pinhits,'999,999') );
34 if ( i = 1000 )
35 then
36 dbms_output.put_line
37 ( l_ns || to_char(l_gets-l_sgets,'999,999') ||
38 to_char(l_gethits-l_sgethits,'999,999') ||
39 to_char(l_pins-l_spins,'999,999') ||
40 to_char(l_pinhits-l_spinhits,'999,999') );
41 end if;
42 end if;
43 end loop;
44 end;
45 /
Procedure created.
sys@ORA10G> alter system set session_cached_cursors=100 scope=spfile;
System altered.
sys@ORA10G> alter system set cursor_space_for_time=TRUE scope=spfile;
System altered.
sys@ORA10G> startup force
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 777956 bytes
Variable Size 145760540 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
sys@ORA10G> @connect /
sys@ORA10G> set termout off
ops$tkyte@ORA10G> @login
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G> REM GET afiedt.buf NOLIST
ops$tkyte@ORA10G> set termout on
ops$tkyte@ORA10G> exec p
SQL AREA 1,181 373 4,828 4,171
SQL AREA 1,181 373 4,828 4,171
SQL AREA 0 0 0 0
PL/SQL procedure successfully completed.
可以看到10g的pl/sql引擎也有了变化。
再来看一下设置session_cached_cursors后handle的dump
BUCKET 110279:
LIBRARY OBJECT HANDLE: handle=9df283a8 mutex=0x9df2845c(1)
name=select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA'
hash=f1deb637acf7a42dd55d86a8ae3baec7 timestamp=08-30-2005 17:07:35
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/MTX/[500100d4]
kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=7 hpc=0002 hlc=0002
LIBRARY OBJECT HANDLE: handle=9df283a8 mutex=0x9df2845c(1)
name=select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA'
hash=f1deb637acf7a42dd55d86a8ae3baec7 timestamp=08-30-2005 17:07:35
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/MTX/[500100d4]
kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=7 hpc=0002 hlc=0002
而不设置的话
BUCKET 110279:
LIBRARY OBJECT HANDLE: handle=9df283a8 mutex=0x9df2845c(1)
name=select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA'
hash=f1deb637acf7a42dd55d86a8ae3baec7 timestamp=08-30-2005 17:07:35
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/MTX/[500100d4]
kkkk-dddd-llll=0001-0001-0001 lock=0 pin=0 latch#=7 hpc=0002 hlc=0002
LIBRARY OBJECT HANDLE: handle=9df283a8 mutex=0x9df2845c(1)
name=select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA'
hash=f1deb637acf7a42dd55d86a8ae3baec7 timestamp=08-30-2005 17:07:35
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/MTX/[500100d4]
kkkk-dddd-llll=0001-0001-0001 lock=0 pin=0 latch#=7 hpc=0002 hlc=0002
如果执行alter system flush shared_pool 那么没有加载null mode lock的handle将被flush出shared pool。
http://wzwanghai.spaces.live.com/blog/cns!56626E237AFBD116!187.entry
http://wzwanghai.spaces.live.com/blog/cns!56626E237AFBD116!187.entry