Oracle Buffer Cache初步诊断调优
来源:互联网 发布:未来人6小时视频知乎 编辑:程序博客网 时间:2024/05/29 14:36
1 Buffer Cache调优目标
- Servers find data in memory
- No waits on the buffer cache
2 Buffer Cache诊断指标
- wait events
- cache hit ratio
- the v$db_cache_advice view
2.1 针对wait events的诊断和调优
3个主要指标:
- Free Buffer Inspected: Number of Buffer Cache buffers inspected by user Server Process before finding a free buffer.
- Free Buffer Waits: 找不到空闲块,必须等待DBWn将脏数据写回腾出新的块
- Buffer Busy Waits: 找到的块还有其他的进程在使用。多个进程同时准备修改一个块。
这三个指标的查询方法:
<span style="font-size:14px;">SQL> select name, value from v$sysstat where name='free buffer inspected';SQL> select event, total_waits from v$system_event where event in ('free buffer waits', 'buffer busy waits');</span>(如果事件发生,才会从v$sysstat中查找出;没有发生就找不到。)
Buffer Busy Waits Cause
未完待续。。。。。。。。
Free Buffer Waits Cause
原因基本是: DBWn may not be keeping up with writing dirty buffers in the following situations.
- The I/O system is slow.
solution: 文件放在不同的磁盘上;没效果就换磁盘
- The I/O is waiting for resources, such as latches.
solution: 文件放在不同的磁盘上;没效果就换磁盘
- The buffer cache is so small the DBWn spends most of its time cleaning out buffers for server processes.
solution: Increase the buffer cache size.
- The buffer cache is so large that one DBWn process cannot free enough buffers in the cache to satisfy requests.
soluition: Decrease the buffer cache size or initialize more database writer processes.
2.2 针对Cache Hit Ratio的诊断和调优
可以从awr中获取到Buffer Cache Hit Ratio
影响命中率的因素有如下几个:
- Full table scans
- Data or application design
- Large table with random access
- Uneven distribution of cache hits (热块和冷块)
Hit Ratio is not everything
联合这三个视图来检查瓶颈
v$session_wait/v$session_event/v$system_event
2.3 根据Dynamic Advisory Parameter来调整Buffer Cache大小
首先需要将DB_CACHE_ADVICE这一参数设置为on,然后再从V$DB_CACHE_ADVICE中查询核实的Buffer Cache大小。
查询SQL语句:
<span style="font-size:14px;">SQL> col name format a10;SQL> select name, size_for_estimate, estd_physical_read_factor, estd_physical_reads fromv$db_cache_advice order by name, size_for_estimate;</span>其中estd_physical_read_factor是估计物理读次数和实际物理读此数的比值,estd_physical_reads是估计物理读次数。当estd_physical_read_factor和estd_physical_reads不再明显降低时,取此时的buffer cache大小。
3 什么时候应该增大Buffer Cache Size?
Increase the cache size ratio under the following conditions:
- Any wait events have been tuned (三种wait event消失了)
- SQL statements have been tuned
- There is no undue page faulting ()
- The previous increase of the buffer cache was effective
- Low cache hit ratio.
As a general rule, investigate increasing the size of the cache if the cache hit ratio
is low and your application has been tuned to avoid performing full table scans.
0 0
- Oracle Buffer Cache初步诊断调优
- oracle调优的初步诊断方法
- Oracle Buffer Cache 原理
- Oracle Buffer Cache 原理
- oracle中的buffer cache
- Oracle BUFFER CACHE研究
- oracle buffer cache
- oracle中的buffer cache
- oracle knowledge-buffer cache
- oracle buffer cache
- Oracle tuning the buffer cache
- oracle Buffer Cache优化思路
- oracle cache buffer lru chain
- oracle buffer cache的基本原理
- buffer cache性能诊断关注的统计信息
- oracle性能调优之--Buffer cache 的调整与优化
- Redo Log Buffer初步调优
- Oracle HowTo:如何强制刷新Buffer Cache
- Redis学习手册(开篇)
- listView的真实position
- Unigine 监听键盘事件和模拟按键
- Write CSDN (github) blog with org mode
- Android 手势检测实战 打造支持缩放平移的图片预览效果;单图+viewpager版下载地址
- Oracle Buffer Cache初步诊断调优
- 并发模型
- FusionCharts使用详解+实例+图解+Demo
- PostgreSQL-存储过程
- jvm为什么有2个survivor
- vc++ 各种类型转换
- C实现 LeetCode->Reverse Integer
- 查看Activity栈
- tableView 各个属性