对于shared pool的学习,发现越尝试去了解,发现自己对它越不了解。里面的东西很杂。
自己想用几个问题来作为引子来说明更加会有条理一些。
shared pool的大小设置
对于shared pool的大小设置,从早期版本到现在一直都带有争论。
从操作上来说,需要设置shared_pool_size就可以了,如果启用了sga_target或者11g里的memory_target,那shared pool的大小设置都是自动管理的了。
还有shared_pool_reserved_size会在shared pool中保留一块固定的内存区域留给一些大对象使用,它的空间是独立的。
从理论上来说,shared pool中含有的free list,或者是bucket,上面有很多的chunk,如果一条sql语句进入library cache,需要申请一块新的内存空间的时候,就需要遍历free list,如果free list很长,在这个过程中也会持有latch,直到解析完成。所以从这个角度来说,设置小的shared pool可能能减少latch的持有时间,但是反过来说,如果shared pool太小,可能空间老是紧张,会有频繁的换入换出的chunk操作,无论扫描还是对chunk的管理都是需要持有latch的,都在一定程度上影响性能,如果设置shared Pool大一些,可能能够延缓一下latch的争用,但是如果随着free list中的碎片增多。导致free类型的chunk越来越多,最后也还是会造成争用。
shared pool的sub pool
这个问题可以从第一个问题得到延伸,如果单纯设置shared pool过大存在问题,设置太小也有问题,从oracle的设计角度来说,就根据系统的情况,可以指定sub pool,比如我的机器配置足够好。可以设置多个sub pool来,每个sub pool都是都有单独的free list,和保留区域,但是彼此之间还是通过latch来并发共享。这样也可以在一定程度上提高shared pool的性能。
11g开始,每个sub pool都为512M
可以通过隐含参数来查看当前的库中sub pool的设置。
1* select a.ksppinm,b.ksppstvl from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.ksppinm='_kghdsidx_count'
SQL> /
KSPPINM KSPPSTVL
-------------------- ----------------------------------------
_kghdsidx_count 1
我本地的环境配置比较差,目前只有一个sub pool,因为shared_pool的大小是200M,没有足够的资源。
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 10M
shared_pool_size big integer 200M
关于绑定变量和硬解析
提到shared pool,不得不提绑定变量,在线业务系统中是很关键的一个指标。
可以举一个例子来简单说明一下。
首先创建一个表test_var,然后传入两个变量值,类型不同,看看执行的情况。
create table test_var as select object_id id,object_name name from user_objects where rownum<3; --只以两条数据为例子
update test_var set name='aaa' where rownum<2; --修改一条数据
update test_var set name='bbb' where name!='aaa'; --修改另外一条数据
SQL> alter system flush shared_pool;
System altered.
SQL> variable name varchar2(100);
SQL> exec :name:='aaa';
PL/SQL procedure successfully completed.
SQL> select *from test_var where name=:name;
ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
21014 aaa
运行语句之后,查看sql_id,和hash值,从v$sqlarea中可以查看version_count,如果发生了硬解析,version_count就会递增。
SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';
SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS
------------- ---------- ---------------- ----------------
3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850
SQL> col sql_text format a50
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select *from test_var%';
SQL_TEXT VERSION_COUNT
-------------------------------------------------- -------------
select *from test_var where name=:name 1
然后再来赋另外一个值,看看version_count会不会递增。
SQL> exec :name:='bbb';
PL/SQL procedure successfully completed.
SQL> select *from test_var where name=:name;
ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
15287 bbb
SQL> col sql_text format a50
SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';
SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS
------------- ---------- ---------------- ----------------
3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850
SQL>
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select *from test_var%';
SQL_TEXT VERSION_COUNT
-------------------------------------------------- -------------
select *from test_var where name=:name 1
可以看到,没有任何的变化,说明绑定变量起作用了,没用再次硬解析。
来改一下数据类型,看看效果。我把变量类型从varchar2改为了char
SQL> variable name char(3);
SQL> exec :name:='aaa';
PL/SQL procedure successfully completed.
SQL> select *from test_var where name=:name;
ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
21014 aaa
SQL> select sql_text,version_count from v$sqlarea where sql_id='3vm96qwzm0mg2'
2 /
SQL_TEXT VERSION_COUNT
-------------------------------------------------- -------------
select *from test_var where name=:name 2
SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';
SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS
------------- ---------- ---------------- ----------------
3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850
3vm96qwzm0mg2 1060130274 000000006B751178 000000006A039F40
不同之处就是child_address,说明走了两次硬解析。
可以想象如果在繁忙的业务系统中如果大量的sql语句走反复解析的话,会耗费大量的cpu资源和时间。导致系统性能的下降。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1323552/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-1323552/