一、shared_pool
share pool分为两个部分
1、library cache(库高速缓存): 加速解析 减少语句重解析,曾经使用的SQL,PL/SQL 的语句和执行计划
2、data dictionary cache 又叫rowcache(数据字典高速缓存):加速sql的解析,使用过的字典 就会缓存在这里 再次使用这个字典就直接从内存获取,这些统计的信息在 v$rowcache中记录
select * from v$sgainfo ----------查看当前SGA各个部件的大小
v$sgastat ----查看sga的状态
select * from v$sgastat where pool='shared pool' and name='free memory'; -----查看sga中shared_pool的空余空间;
① library cache
select sum(pinhits)/sum(pins) from v$librarycache; ----查看librarycache的sql命中率
如果sql的命中率在98%以下就有点低了,这是我们就要想办法优化了。
1、加大 shared_pool_size 的大小,但也不要太大,太大会增加管理的额外费用(这条是在sga手动管理的情况下)
2、编写程序的时候使用变量传入,而不是使用常量(对dba有点不实际,对开发人员说的~!)
3、将大的包定在内存中
4、修改初始化参数 cursor_sharing(这个我们重点说一下)
可以做一个试验:① create table t1 as select * from emp ;建立一个10W行左右的表,然后让修改empno这一列,让第一行位2000,剩下的都为1000,然后在empno上建立索引
② 修改参数cursor_sharing的值,打开执行计划,观察各种情况下select * from t1 where empno=1000; 和select * from t1 where empno=2000;下有什么不同
完成上面的试验,总结得到:
1、强制匹配(force)命中高:将where的条件都用变量来处理,提高了命中率,但是不能区分列值的敏感性,会导致部分sql的语句的执行计划不正确!
上面试验empno=2000的情况,拿到了empno=1000的执行计划,走了全表扫描,而没走索引!
2、近似匹配(similar):将where 条件都用变量来处理,提高了SQL 的命中率,但可以区分列值的数据敏感性,既保证了语句的复用,又提高的命中率,又可以区分列的条件差异。但oralce 有的时候会有bug,导致美好的东西变成了泡影,所以我们改了以后要测试一下性能。
上面试验empno=2000的情况,走了索引,没用empno=1000的执行计划!
3、精确匹配(exact)命中低:原语句不处理,是什么就是什么,降低了sql的命中,但是保证执行计划是正确的。oracle默认值! -----最好是用这个,通过修改程序的方法来提高命中
试验中empno2000,走索引,empno=1000,走全表扫描!
写到这里我有一个疑问,如果我的程序都用的是绑定变量,那么,我的sql语句也可能会拿到不正确的执行计划吧?
②data dictionary cache(存放的使用过的数据字典)
Data Dictionary Cache缓存被使用的数据库定义,即存放有关表,列和其它对象定义及权限。它包括关于数据库文件、表、索引、列、用户、权限以及其它数据库对象的信息。在语义分析阶段,Server Process访问数据字典中的信息以解析对象名和对存取操作进行验证。数据字典信息缓存在内存中有助于缩短响应时间。
查看数据字典缓存的命中率:
select parameter , sum(gets),sum(getmisses), sum(gets-getmisses)/sum(gets) *100
from v$rowcache where gets>0
group by parameter ;
③share pool保留区:对于大的内存且为连续的内存空间请求,如果在共享池中未找到,则会动用共享池中的保留池,可以用作PL/SQL或触发器编译使用或用于装载JAVA对象的临时空间
相关字典:v$shared_pool_reserved
二、db_buffer_cache
数据库缓冲缓存区 缓冲被访问的数据块,每次从磁盘向内存缓冲数据的单位由 db_block_size=8192 决定,保留时间由LRU算法决定
1、查看命中率:
SQL>select name,value from v$sysstat where name in ('db block gets','consistent gets','physical reads');
NAME VALUE
---------------------------------------------------------------- ----------
db block gets 4548941426
consistent gets 787199501
physical reads 14484963
Hit Radio=1-(physical reads/(db block gets+consistent gets))
SQL> select 1-(14484946/(4548939487+787193908)) from dual;
1-(14484946/(4548939487+787193908))
-----------------------------------
.997285498
*注意一点:buffer_cache的命中率高不一定就没问题,有可能是由于逻辑读过高,导致物理读被掩盖了,我们还是要看一下每秒的物理量读是不是过高(vmstat 、iostat)
2、查看当前时间存在buffer cache中的非系统块(能查出哪个对象在内存中有多少个块):
SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);
3、估算db_cache 放大或减小后对I/o 的影响:
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON'
/
-------------------------------------------------
查看当前buffer cache size
SQL> select name ,bytes/1024/1024 Mb from v$sgainfo where name ='Buffer Cache Size';
NAME MB
-------------------------------- ----------
Buffer Cache Size 1008 这个buffer cache size 指的是 default + keep +recycle 的和
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
1008 这里 我没有配置 keep pool ,也没有配置 recycle pool
****************************************************************************************
database buffer cache 中包括:db_cache_size(defalut), db_keep_cache_size(keep), db_recycle_cache_size(recycle)
所以buffer cache size =db_cache_szie + db_keep_cahce_size + db_recycle_cache_size
但是我们一般不设置keep和recycle,所以 buffer cache size = db_cache_size
*buffer cache size 是database buffer cache的大小
搞清楚database buffer cache=buffer cache size =db_cache_size+db_keep_cache_size+db_recycle_cache_size
****************************************************************************************
简单的说一下latch閂锁:
4种常见的等待时间:1、latch:cache buffer chains 2、latch:libaray cache 3、latch:shared pool 4、latch free
3种常见的latch(锁存器):1、cache buffer chains (对应database buffer cache)、2、library cache(对应library cache) 3、shared pool (对应shared pool)
①发生cache buffer chains latch 主要是因为某些blocks被频繁的访问由于两种原因引起:1、低效率的sql造成过多的逻辑读 2、高并发对某个块(表)的集中访问(热块) (业务逻辑不合理,应用太集中,造成某个表的频繁访问?) 解决办法:先找到热块对应的表,进一步找到对应的sql,优化sql,优化表建立索引等、分散应用
②发生library cache latch 和shared pool latch主要是因为过多的硬解析(有两重解决办法:1、绑定变量 2、初始化参数 cursor_sharing=force)
参考张烈讲义中的试验P353
x$bh中的字段hladdr表示该block在哪个cache buffer chain latch上
******************************************************************************************
三、redo log buffer cache
show sga
在redo buffer中每条记录成为redo entries
LGWR把redo buffer中的redo entrie写入到重做日志中,LGWR有只有一个
LGWR在下列情况时对重做日志进行写入:
①commit发生时 ②每3秒 ③ redo buffer 1/3满时 ④redo buffer 1M时 ⑤在DBWn写之前
1、"重做缓冲区再分配"事件反映用户进程等待重做日志缓冲区空间的次数:SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME = ‘redo buffer allocation retries’;(越小越好)
2、当向redo buffer 中添加信息时,但空间不足时产生“log buffer space”等待事件:select * from v$system_event where event='log buffer space'; (应避免)
多数是因为日志缓冲区中写入数据的速度大于 LGWR 写出数据的速度,可以增大redo buffer的大小
日志缓冲的优化主要有三个办法:
加大Log_buffer 的大小
提高硬盘的写入速度
减少日志的产生
----------------------------------------------------------------------------------------------------------------------------------------------------------
四 、PGA的优化
PGA:大表的连接和排序的重要区域,当PGA空间不足时,会用到临时表空间去做排序
pga从9i起可以自动管理了:
1.pga_aggregate_target来指定所有session总计可以使用的最大pga内存(10M-4096G),这个参数可以动态修改。
2.workarea_size_policy控制pag自动管理功能开启或关闭,auto表示开启(default), manual表示关闭
3、PAG自动管理后,*_area_size都将被忽略
sql在workarea中有3种方式:
1、optimal:最优的方式,所有处理都在内存中完成 (90%以上)
2、onepass:大部分操作在内存中执行,但交换到临时表一次
3、多次交换到临时表,产生大量的disk sort ,性能最差 (最好没有)
SQL> select * from v$sysstat where name like 'work%';
workarea executions - optimal 64 2783
workarea executions - onepass 64 0
workarea executions - multipass 64 0
可以根据v$pga_target_advice 调整pga的大小