Oracle-OCP学习笔记:Shared Pool本章主要阐述SGA中的shared pool。
Shared pool {
1.free
2.library cache(缓存sql语句及其执行计划)
3.row cache(数据字典缓存)
}
ps：对Oracle了解的多少的一个重要指标就是看你对数据字典了解多少。
--Shared pool
oracle 中只可以整体设置sharedpool的大小，不可以对shared pool中的内存区域分别设置大小。
shared pool中容易出问题的是free和library cache；
1.free(shared pool中空闲的内存部分)
Q：何时需要从free中找chunk呢？
A：在硬解析的时候。
注意：如果硬解析失败，会报一个非常经典的错误Ora-4031。
产生这个错误的原因：①系统产生大量的硬解析；②大量的硬解析产生了大量碎片，此时又来一个大的sql需要解析，而free空间不够（有可能本身空间有剩余，但是都是碎片，没有符合需求所要大小的块，如下图4-1），导致报错。
2.library cache(缓存sql语句及其执行计划)
library cache 的内存块：
Q：shared pool中有多少chunk?
A：查询x表(x$ksmsp)的总数，
select count(*) from x$ksmsp; |
Q：查询硬解析更直观的方法？
A：
select name, value from v$sysstat where name like 'parse%'; |
Q：如何清空librarycache 和 row cache？
A：
1 | alter system flush shared_pool; |
（慎用flush命令，之后会产生大量的硬解析，能解决4031错误，但治标不治本。）
Q：如何减少硬解析？
A：共享sql，让sql完全相同。统一书写风格；使用绑定变量。
sql=>ASCII码值=>hash值=>运算=>挂哪个链上。
--使用绑定变量使得sql共享
declare v_sql varchar2(50);
begin for i in 1..10000 loop
v_sql := 'insert /*jingyu*/ into test values(:1)';
execute immediate v_sql using i;
end loop;
commit;
end;
--oracle有一个参数cursor_sharing
show parameter cursor alter system set cursor_sharing='force'; |
（可以解决字面值没有使用绑定变量的问题。尽量先让开发去改，实在不行再改这个）
Q：如何找到没有共享的sql语句？
A：spool 1.txt
select sql_fulltext
from v$sql
where executions=1
order by sql_text;
spool off
注：结果不一定都是没有共享的sql语句，有可能是该sql语句本来就执行了一次。
Q：查看library cache命中率（容易出问题，正常很接近100%）
A：
select sum(pinhits)/sum(pins)*100 from v$librarycache;
Q：查看row cache命中率（一般没问题）
A：
select sum(gets), sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcache;
Q：如何看一条sql语句的执行计划
A：先在v$sql中确认sql_id, 然后使用
select * from table(dbms_xplan.display_cursor('g4pkmrqrgxg3b')); |
查看执行计划。
小结：
1.如何解决4031错误？
(1)alter system flush shared_pool;
(2)共享sql
(3)dbms_shared_pool.keep('大的对象名');
(4)保留区(在shared pool中单独划分出一个空间来，只用来放大对象)
把保留区设置的足够大，可减少很多4031的错误。
1 | select request_misses from v$shared_pool_reserved; |
查询结果应该是0.如果大于0，要调整shared_pool_reserved_size 参数。
(5)增加shared pool空间
1 2 3 4 | 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; |
10g之后，统一的一个参数sga_target（动态参数）
sga_max_size（静态参数，用来约束sga_target的）
注意：如果设置了sga_target，自动分配的sharedpool的大小。
手工配置时，如果小于自动分配的大小，oracle直接忽略，此时showparameter sga,就不准确了。
2.shared pool到底该设置多大？
如果sql语句没有共享，硬解析非常多，shared pool的作用是反的，就是设置越大反而越不好。
10g以前。不超过1个g。,(允许有多个sub sharedpool,可以设置大于1G的shared pool)
10g以后，可以超过1个g，2个g ，5个g。但也不建议设置太大。
利用一个sql语句看应该设置多少合适：
select shared_pool_size_for_estimate"SP",ESTD_LC_SIZE "EL",ESTD_LC_MEMORY_OBJECTS"ELM", ESTD_LC_MEMORY_OBJECTS"ELT",ESTD_LC_TIME_SAVED_FACTOR as "ELTS",
ESTD_LC_MEMORY_OBJECT_HITS as"ELMO"
FROM V$shared_pool_advice;
或者用EM 指导中心--内存指导--建议，看一下应该设置多大
3.row cache(数据字典缓存)
一般也不会出什么问题，暂不深入研究。
查看执行计划：
Select * from table (dbms_xplan.display_cursor(‘097adffwefwefwef’))
