Oracle 文档中,物化视图作为数据仓库中的一项技术,但在OLTP(联机事务处理)系统中是用来进行数据同步,使用查询重写来优化SQL查询语句都是很方便的。 |
影响Oracle的性能的因素有很多,例如oracle自身的sga设置,磁盘I/O设置等。但需要强调的一点就是oracle调整的原始动机都是减少磁盘I/O。下面举一个数据来说明磁盘I/O的影响之大,ram中获取数据块和磁盘上获取数据块的时间差异相差两个数量级,可以认为ram比磁盘块14000倍,在磁盘上的访问时间是毫秒级(一秒的千分之一),在ram的速度是纳秒级的(一秒的10亿分之一)。从上面可以看出,如果在理论上将oracle的数据全部缓存在内存中,那访问速度是极快的。
鉴于当前学习oracle还不深,本文档的目的是如何通过statspack来分析数据库的命中率和调整oracle的SGA。从oracle自身来说,性能最大的两个瓶颈就是命中率和等待事件。等待事件作为接下来第二个学习点,本文档当作命中率和oracle内存机制熟悉的一次总结。
本次总结分为以下几点:
1、oracle的SGA介绍;
2、SGA各部分命中率介绍;
3、命中率相关的Statspack数据表作用介绍;
4、如何使用statspack获得和分析命中率。
一、 oracle的SGA
通常oracle实例包括了两个组件:系统全局区域SGA和oracle后台进程。当启动oracle的时候,oracle就会用malloc()命令去建立一个内存区域作为SGA。
SGA总容量=共享池(shared pool)+缓存区高速缓存(data buffer cache)《db_keep_cache_size,db_recycle_cache_size,db_nk_cache_size》+日志缓冲区(redo log buffer)《11Kredo log buffer的保护页》+大型池(large pool)+java存储区(java pool)+《streams_pool_size(10g中的新内存池)+16M(sga内部内存消耗,适合于9I及之前版本)》
1、共享池由库缓存(library cache)与字典缓存(dictionary cache)两部分,库缓存包括共享sql区(shared sql areas)、pl/sql存储过程和包以及控制结构(如锁、库缓存句柄)。
2、大型池属于可变区,用于共享服务(shared server mts方式)的会话内存和oracle分布式事务处理的oracle接口,oracle备份和恢复操作(启用了rman)。
3、Java池的内存用于存储所有会话中特定java代码和jvm中的数据。属于可变区。
参数介绍:
1、Sga_max_size ,SGA可用最大物理内存是由该参数决定的。可用show parameter sga查询。平常查看任务管理器,你会发现oracle进程显示的内存使用数量小于sga最大的内存数量,是因为oracle实例开启时,只载入内存区最小的大小,其他sga内存只作为虚拟内存分配,这样就会有一个风险,产生过多的page in/out操作,这种磁盘交换是很耗时间的。因而可以用pre_page_sga设置为true ,lock_sga(危险,导致数据无法开启,原因未研究,官方说法是跟PC机有关)设置为true解决。
2、db_block_size,执行许多全表收索的oracle数据仓库和系统将会从16K大小块中受益。另外db_file_multiblock_read_count与db_block_size之前有非常重要的关系,在unlx物理层上,oracle总是以最小64K的数据块读入。
3、db_cache_size指定data buffer cache的大小。在oltp系统中,推荐设置为sga_max_siz/2-sga_max_size*2/3.
非标准块尺寸的块大小可以在创建表空间(create tablespace)通过blocksize参数指定,而不同块尺寸的buffer cache的大小就由相应参数db_nk_cache_size来指定,其中n可以是2,4,8,16或者32。另外注意一点,db_nk_cache_size参数不能设定标准块尺寸的缓冲区大小,举例来说,如果db_block_size设定为4k,就不能再设定db_4k_cache_size参数了。
4、shared_pool_size决定共享区的大小
5、large_pool_size确定大型池的大小
6、java_pool_size确定java池的大小
二、 SGA各部分命中率介绍
1、数据缓存区命中率
select value from v$sysstat wherename='physical reads'
select value from v$sysstat wherename='physical reads direct'
select value from v$sysstat wherename='physical reads direct (lob)'
select value from v$sysstat where name='consistentgets'
select value from v$sysstat wherename='db block gets'
X= physical reads direct+ physicalreads direct (lob)
命中率=100-(physical reads-x)/(consistent gets+ db block gets-x)*100
通常发现命中率低于90%,则应该考虑增大数据缓冲区。
select (100 -(n1.value - n2.value - n3.value) /
(n4.value+ n5.value - n2.value - n3.value) * 100) as per
from (selectvalue from v$sysstat where name = 'physical reads') n1,
(selectvalue from v$sysstat where name = 'physical reads direct') n2,
(selectvalue
fromv$sysstat
wherename = 'physical reads direct (lob)') n3,
(selectvalue from v$sysstat where name = 'consistent gets') n4,
(selectvalue from v$sysstat where name = 'db block gets') n5
2、共享池命中率
select sum(pinhits)/sum(pins)*100"hit radio" from V$librarycache
通常共享池命中率低于95%,考虑增加共享池内存
3、排序
select name,value from V$sysstat wherename like '%sort%'
发现sort(disk)数值较高时,应增加sort_area_size的内存大小
4、重做日志缓冲区
select name,value from V$sysstat wherename='redo log space requests'
该查询结构的vlue值应接近于零,否则每次将log_buffer增大5%.不过不需要超过3M,超过3M无效。
三、 命中率相关的Statspack数据表作用介绍
1、stats$buffer_pool_statistics该表是记录缓冲池使用情况
free_buffer_wait 等待自由缓冲的数量计数
buffer_busy_wait 因冲突而不能够请求道数据块的次数
db_block_gets 数据块获取的数量
consistent_gets 逻辑读入的数量
physical_reads oracle发出的磁盘块获取数量
physical_writes oracle请求的物理磁盘写入的数量
2、stats$librarycache该表是保存库缓存活动信息
namespace 命名空间,值可以是sql区域、表、过程、包、触发器
pin 统计库缓存中对象的执行次数
reloads 统计解析在表示库缓存中不存在,并强制oracle分配私有sql区域来解析和执行语句的次数。
3、stats$rowcache_summary该表来度量辞典缓存活动
gets 提供请求这种类型对象的全部数量
getmisses 统计oracle不得不执行磁盘I/O来从它辞典表中获取行的次数。
四、 使用statspack获得和分析命中率
1. 获得数据缓存区命中率
使用rpt_bhr_all.sql脚本在stats$buffer_pool_statistics表中获取每次快照的命中率值、
要获得命中率趋势数据,可以按每天小时和每星期每天做分析。
使用rpt_bhr8i_hr.sql按照每小时的平均值给予。使用rpt_bhr8i_dy.sql按照每天的一个星期中各天的平均值给予。
2. 库缓存缺失率
Sql语句的编译包括了两个阶段:解析阶段和执行阶段,当解析一个sql语句的时候,oracle首先会检查在库缓存中是否已经存在解析过的语句表示,如果没有,oracle就会在库缓存中分配一块共享sql区域,然后解析sql语句。因而sql尽量少的解析过程可以提高性能。
通过rpt_lib_miss.sql脚本分析stats$librarycache表可以查看缺失率情况。
库缓存命中率是由所有载入到ram缓冲的辞典对象所决定,这些对象包括表/过程、触发器、索引、包以及簇,这些对象都不应该在库缓存内发生问题。
通过rpt_lib.sql脚本可以显示各个快照期间库缓存内对象的细节活动。
3. 数据辞典缓存率
数据辞典缓存用来保存来自内部的oracle元数据表的行,包括在包中存储的sql,每一次对sql语句进行解析以及sql收集动态存储进行执行的时候,都要访问数据辞典缓存。rpt_dict_alert.sql,rpt_dict_dy.sql,rpt_dict_detail.sql,rpt_dict_detail_alert.sql
注:
1.以上所有脚本执行的结果,放到execle上,做成图形可以做成很好的趋势图。