Oracle系统调优详解

Oracle系统调优

在Oracle数据库系统中，起到调节作用的参数叫初始化参数，在Oracle 8i及以前的版本中，这些初始化参数记录在INITsid.ora文件中；而Oracle 9i/10g/11g中将这些参数记录在SPFILEsid.ora二进制文件中

基本参数：一组可调整的参数，如control_files,db_block_size,process

高级参数：一组精细调整的参数,如shared_servers

在进行系统优化时，可以使用alter system(整个数据库实例) /alter session（当前会话）

可以使用show parameter+参数名查看

系统全局区（SGA）优化，虽然11g增加了内存的自动调整

调整内存分配

一般内存的调整在应用程序和SQL语句做完调整后进行的。另外，建议用户在调整I/O 前先调整内存分配。

调整日志缓冲区

如何记录日志文件呢？

方式一：使用逻辑的记录方式，也就是用描述性的语句记录整个变化过程。

如，对某个update，只记录delete旧值和insert新值

优点：节省空间

确定：一旦需要进行恢复就会非常消耗资源

方式二：使用物理记录方式，也就是说，把每个数据块的改变前的镜像和改变后的镜像都记录下来

优点：恢复速度快，

缺点：占用很大的磁盘空间

而Oracle在记录日志的方式上，采用了逻辑和物理相结合的方式。

查询当前Oracle实例的日志缓冲区大小

Sys sysdba角色登录：

select distinct lebsz as 日志缓冲区大小from x$kccle --必须以sys身份登录

插入知识介绍：x$表

x$表包含了特定实例的各方面信息，在不同版本的oracle版本中很可能是不一样的，时Oracle 数据库的运行基础，包含当前的配置信息，连接实例的会话，以及丰富价值的性能信息。它并不是主流在数据库文件的永久或临时表

x$表仅仅驻留在内存中，当实例启动时，由Oracle应用程序动态创建，在内存中进行实时的维护。它们中的大多数至少需要装载或已经打开的数据库。x$表为sys用户所拥有，只读，x$表对于数据库来说很重要，所以不允许sysdba之外的用户直接访问，显示的授权也不被允许

调整共享池

主要组件有：

库缓存（library cache)：主要缓存共享SQL和PL/SQL语句的相关信息

数据字典缓存(data dictionary cache):缓存数据字典表（dba_tables,dba_users）等信息，用于解释权限、表结构等

UGA（user globa area）:在共享服务器模式下，当没有配置大池时（large_pool_size=0）UGA 会占用共享池

在SGA中，首先应该考虑调整shared pool，调整shared pool时，应该首先集中在库缓存上。如果共享池很小时，会消耗很多CPU资源并引起竞争；如果共享池很大，会消耗很多内存资源，缓冲的内容较多，此时查找也会变得较缓慢，如：通过v$librarycache来查询当前实例的库缓存在调用阶段的“请求存取数”和“不命中数"

select sum(pins) 请求存取数，sum(reloads) 不命中数from v$librarycache;

一般来说，库高速缓冲总不命中数与总存取数之比应当接近0。当该比率如果接近或大于1%时，就应当立即采取措施减少这种不命中，通常有以下两种方法：

1.增加初始化参数shared_pool_size，提高库高速缓存中可用的内存数量，同时为了取得好的效果，还要增加初始化参数open_cursors的值，以提高对话允许的光标数。需要注意的是：为库缓存分配太多的内存可能引起调页或交换

2写等价的SQL语句，尽可能让SQL语句和PL/SQL块共享一个SQL区，以减少库缓存的不命中。SQL语句或PL/SQL块的文本每一个字符都必须等价，包括大小写和空格

v$rowcache查询数据字典高速缓存的活动状况

如：通过v$rowcache动态性能视图查询当前实例的数据字典高速缓存在调用阶段的“请求存取数”和“不命中数”

select sum(gets) 请求存取数，sum(getmisses) 不命中数from v$rowcache;

一般来说，数据字典高速缓存总不命中数与总存取数之比应当接近0，改比率如果大于10%，甚至在应用过程中该比率还在增长，就应当立即通过增加shared_pool_size，以提高数据字典可用的内存数量，从而减少这种不命中。

v$sesstat v$statname查询Oracle收集对话信息使用的总内存统计

如：显示当前分配给所有会话的内存数

select sum(value)||'字节' 当前分配给所有会话的内存数

from v$sesstat,v$statname

where name='session uga memory' and v$sesstat.statistic#=v$statname.statistic#;

session uga memory用于显示分配给对话的内存字节数

如：显示曾经分配给所有会话的最大内存数

select sum(value)|| '字节' 曾经分配给所有会话的最大内存数

from v$sesstat,v$statname

where name='session uga memory max' and v$sesstat.statistic#=v$statname.statistic#;

调整数据库缓冲区

Oracle启动后不断地收集和统计数据存取的情况，并将其存放在v$sysstat中，有：

db block gets:该统计值为数据请求的总数

consistent gets:该统计值为通过内存缓冲区存取既能满足的请求书

physical reads:该统计值为磁盘文件存取的总数

如：查询一段时间内v$sysstat表中的统计信息

select name,value from v$sysstat where name in('db block gets','consistent gets','physical reads');

计算缓冲区高速缓存的命中率可以使用

1-physical reads/(db block gets+consistent gets)

如果命中率还不错，可以适当减少初始化参数db_block_buffers的值减少高速缓存的大小，从而可以将省出来的内存用于其他Oracle内存结构。如果命中率低于70%，就会造成性能下降，这时应该立即通过增加初始化参数db_block_buffers的值（最大为65535）来增加缓