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)的总数，

Q ：查询硬解析更直观的方法？

A ：  

Q ：如何清空librarycache 和 row cache？

A ：

（慎用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

（可以解决字面值没有使用绑定变量的问题。尽量先让开发去改，实在不行再改这个）

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, 然后使用

查看执行计划。

小结：

1. 如何解决4031错误？

(1)alter system flush shared_pool;

(2) 共享sql

(3)dbms_shared_pool.keep(' 大的对象名');

(4) 保留区(在shared pool中单独划分出一个空间来，只用来放大对象)

把保留区设置的足够大，可减少很多4031的错误。

查询结果应该是0.如果大于0，要调整shared_pool_reserved_size 参数。

(5) 增加shared pool空间

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’))