free空间的存在大小不同的空闲块,同时存在有很多链(chain)
在链上挂载着大小相近空闲块(trunk)。
不同的链挂载的块的大小不一样。即trunk的大小不一致
当硬解析的时候需要从free空间获取大小不同的块存放在library cache,
同时产生小的trunk(大量的小的trunk会报4031错误,当大量的硬解析、大sql语句)
ORA4031--大量的硬解析、大sql语句。
chain的好处:
1、可以把内存块串在一起
2、可以遍历。
latch是用来保护chain的
library cache
对sql进行运行运算 得到hash值,运算得到个编号。也就是这个链的编号。
对链进行遍历,查找到相应的chunk。从而避免硬解析。
查看解析次数
select name,vale from v$sysstat where name like 'parse%';
sharepool里面的chunk在表中都有信息
select count(*) from x$ksmsp;
求出chunk数
运行select count(*) from dba_objects;
查看chunk数是否有变化。
清理sharepool的空间
alter system flush shared_pool;
SQL共享,绑定变量
declare v_sql varchar2(50);
begin for i in 1..10000 loop
v_sql := 'insert /*hello*/ into test(id) values (:1)';
execute immediate v_sql using i;
end loop;
commit;
end;
select SQL_ID,sql_text,EXECUTIONS from v$sql where SQL_TEXT like '%hello%'
找出没有共享的SQL语句
如何找出不能共享cursor的sql
在v$sql查找执行次数较小的sql语句,观察这些sql语句是否是经常执行的。
select SQL_FULLTEXT from v$sql where EXECUTIONS=1 and sql_text like '%from t%';
select SQL_FULLTEXT from v$sql where EXECUTIONS=1 order by sql_text;
解析命中率
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcache where gets>0;
解决4031错误的方法
1、alter system flush shared_pool;
2、共享SQL
3、select * from v$db_object_cache where sharable_mem > 10000
and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type='PROCEDURE')
and kept = 'NO';
执行dbms_shared_pool.keep('对象名');
DBMS_SHARED_POOL
@?/rdbms/admin/dbmspool.sql
4、保留区(sharepool专门划出保存大sql对象的内存空间)
show parameter shared_pool
shared_pool_reserved_size big integer 48M
select REQUEST_MISSES from v$shared_pool_reserved;
5、增加shared pool空间
select COMPONENT,CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
show parameter sga_target (动态)
show parameter sga_max_size(静态)
alter system set shared_pool_size=150M scope=both;
设置shared pool的大小
SELECT
shared_pool_size_for_estimate "SP",
estd_lc_size "EL",
estd_lc_memory_objects "ELM",
estd_lc_time_saved "ELT", estd_lc_time_saved_factor "ELTS",
estd_lc_memory_object_hits "ELMO"
FROM v$shared_pool_advice;
SELECT 'Shared Pool' component,shared_pool_size_for_estimate estd_sp_size,estd_lc_time_saved_factor parse_time_factor,
CASE
WHEN current_parse_time_elapsed_s + adjustment_s < 0
THEN 0
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (SELECT shared_pool_size_for_estimate,shared_pool_size_factor,estd_lc_time_saved_factor,a.estd_lc_time_saved,e.VALUE/100 current_parse_time_elapsed_s,c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s FROM v$shared_pool_advice a,(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,(SELECT estd_lc_time_saved FROM v$shared_pool_advice WHERE shared_pool_size_factor = 1) c);