欢迎进入oracle社区论坛，与200万技术人员互动交流 >>进入 数据库的优化 概述 影响数据库性能的因素包括：系统、数据库、网络。 数据库的优化包括：优化数据库磁盘i/o、优化回滚段、优化rrdo日志、优化系统全局区、优化数据库对象。 监控数据库的性能： 在in
欢迎进入oracle社区论坛，与200万技术人员互动交流 >>进入
数据库的优化
概述
影响数据库性能的因素包括：系统、数据库、网络。
数据库的优化包括：优化数据库磁盘i/o、优化回滚段、优化rrdo日志、优化系统全局区、优化数据库对象。
监控数据库的性能：
在init.ora参数文件中设置timed_statistics=true和在你的会话层设置alter session setstatistics=true。运行svrmgrl 用 connect internal注册，在你的应用系统正常活动期间，运行utlbstat.sql开始统计系统活动，达到一定的时间后，执行utlestat.sql停止统计。统计结果将产生在report.txt文件中。(utlbstat.sql utlestat.sql 一般存放在$oracle_home/rdbms/admin子目录下)
优化数据库磁盘i/o
检查系统的i/o问题
在unix系统中工具sar-d能检查整个系统的iostat(io statistics)，在nt系统上则使用性能监视器(performance monitor)
反映oracle文件i/o的进程
文件
进程
lgwr
dbwn
arch
smon
pmon
ckpt
fore_ground
pq slave
数据库文件
y
y
y
y
y
y
log文件
y
归档文件
y
控制文件
y
y
y
y
y
y
y
y
使用v$filestat确定oracle数据文件i/o
文件
select name,phyrds,phywrts from v$datafile df,v$filestat fs wheredf.file#=fs.file# ;
使用分布i/o减少磁盘竞争
将数据文件和redo log文件分开
striping 表数据
分开表和索引
减少与oracle无关的磁盘i/o
避免动态空间管理
在创建如表或回滚段的数据库实体时，在数据库中会为这些数据分配空间，该空间被称为段。如果数据库操作引起数据增加并超出了分配的表空间，oracle会扩展该段，动态扩展会降低系统性能。
确定动态扩展
select name,value from v$sysstat wherename=’recursive calls’ ;
分配分区
确定实体的最大大小;
选择存储参数值，使oracle分配足够大的分区，在创建实体时可以装入所有数据
避免回滚段的动态空间管理
回滚段大小由其存储参数所决定，回滚段必须能保存所有交易的回滚入口;
使用settransaction 命令可以为回滚段赋予交易的合适的大小;
对长的查询的修改数据，应赋予大的回滚段，以保持所有的回滚入口;
对oltp交易，由于频繁交易，每个交易只修改小量的数据，因此赋予小的回滚段。
减少迁移和链接行
1.使用analyze 收集迁移和链接行的信息;
2.查询输出表：chained_rows;
3.如果有许多迁移和链接行，就需要消除迁移行，方法如下：
1.创建与原表相同列的中间表，以保存迁移和链接行;
2.从原表中删除迁移和链接行;
3.将中间表中的行插入到原表中;
4.删除中间表
1.删除第一步收集的信息;
2.重新使用analyze命令查询输出表
3.在输出表中出现的行都是链接行，只能通过增加数据块的大小来清除。
调整排序
内存中排序
使用动态表v$sysstat的信息反映排序
select name , value from v$sysstat where name in (‘sorts(memory)’,’sorts(disk)’);
sorts(memory)-不需要使用i/o操作而完全在内存完成的排序数;
sorts(disk)-需要使用i/o操作与磁盘临时段才能完成数据的排序数目。
增大sort_area_size以避免磁盘排序
使用nosort创建非排序的索引
createindex index_name on table table_name(column_name) nosort ;
调整checkpoints
一个checkpoint是oracle自动执行的一种操作，当检查点操作时，数据库中的所有缓冲区会写回磁盘，所有数据库的控制文件被更新。checkpoint频繁发生会加快数据库的恢复，但是增加了i/o次数，会降低系统的性能。
调整lgwr和dbwn i/o
调整lgwri/o
每次i/o写的大小依赖于log缓冲区的大小，该大小由log buffer 所设置，缓冲区太大会延迟写操作，太小可能导致频繁的小的i/o操作。如果i/o操作的平均大小很大，那么log文件就会成为瓶颈，可以使用stripe redo log文件避免这个问题。
调整dbwni/o
使用初始参数db_writer_processes，可以创建多个数据库写进程。
调整竞争
由多个进程同时请求使用相同的资源时，就产生了竞争
确定竞争问题
视图v$resource_limit提供了一些系统资源的使用限制。
如果系统存在无反应的现象，检查v$system_event，检查最大平均等待时间的事件;
如果存在过量的缓冲区等待，检查v$waitstat，确定哪个类型的块有最多的等待次数和最长的等待时间，再查询v$session_wait得到每个缓冲区的等待时间。
减少回滚段的竞争
通过检查v$waitstat可以确定回滚段的竞争：
select class,count from v$waitstat whereclass in (‘system uodo header’,’system uodo block’,’uodo header’,’uodo block’);
减少调度进程的竞争
检查调度进程的busy率
select network”protocol”, sum(busy)/(sum(busy)+sum(idle))“total busy rate” from v$dispatcher group by network ;
如果指定协议的调度进程忙的时间超过50%的有效工作时间，那么，增加调度进程可以提高使用该协议连接到oracle的性能。
检查调度进程相应队列的等待时间
select network “protocol”decode(sum(totalq),0.’no responses’,sum(wait)/sum(totalq)||’hundredths ofseconds’) “average wait time per response” from v$queue q,v$dispatcher d whereq.type=’dispatcher’ and q.paddr=d.paddr group by network ;
增加调度进程：使用mts_dispatchers参数和alter_system命令可以增加调度进程
减少共享服务器进程的竞争
共享服务器进程竞争可以由不断增加的请求等待时间所反映，使用如下查询：
select decode(totalq,0,’no requests’,wait/totalq||’hundredths of seconds’)“average wait time per requests” from v$queue where type=’common’ ;
使用如下查询可以得到当前运行的共享服务进程数：
select count(*) “shared server processes”from v$shared_servers where status!=’quit’;
oracle能自动增加共享服务进程，但是mts_max_servers的值可以更改。
减少redo log缓冲区latches竞争
在lgwr进程将redo入口从redo log缓冲区写入redo log文件后，该入口就会被新入口覆盖，供其他log的使用。
v$sysstat中redo buffer allocation retries 反映用户进程等待redo log空间的次数：
select name,value from v$sysstat wherename=’redo buffer allocation retries’ ;
redo buffer allocation retries的值应该接近0，如果该值持续增加，那么，说明进程需要等待缓冲区的空间。增大参数log_buffer的值可以增大redo log的大小。
确定redo log缓冲区latches竞争
redo分配latch;
redo复制latches。
一次只能有一个用户分配缓冲区中的空间，在分配了redo入口的空间后，用户进程将入口复制到缓冲区，其最大大小是由log_small_entry_max_size指定。redo复制latches的数目由参数log_simultaneous_copies指定。
检查redo log活动
对redo log缓冲区的频繁访问可能导致redo log缓冲区latches竞争，降低系统性能。oracle在动态表v$latch中收集了所有latch的统计信息。
其中：表v$latch反映willing-to-wait请求的列
gets-成功的willing-to-wait请求数;
misses-初始不成功的willing-to-wait请求数;
sleeps-请求不成功的等待时间;
表v$latch反映immediate请求的列：
immediate gets-成功的immediate请求数
immediate misses-不成功的immediate请求数
使用如下查询：
selectln.name,gets,misses,immediate_gets,immediate_misses from v$latch l,v$latchnameln where ln.name in (‘redo allocation ’,’redo copy’) and ln.latch#=l.latch# ;
可以计算出各类请求的等待率。
减少latch竞争
要减少redo allocation latch竞争，必须减少单个进程占用latch的时间。要减少这个时间，可以减少该redo allocationlatch的复制。减少log_small_entry_max_size初始参数可以减少在redo allocation latch的redo入口的复制次数和大小。
减少redo copy latches竞争可以用增加log_simultaneous_copies的值来增加latch数，最多可以达到cpu的两倍。
减少free list竞争
确定free list竞争，可以使用以下几步：
1.检查v$waitstat，确定data blocks的竞争;
2.检查v$system_event,确定buffer busy waits，如果数值高，表明存在竞争;
3.在这种情况下，检查v$session_wait查询每个缓冲区的忙等待、file、block及id;
4.使用如下查询得到实体和free list的名称：
selectsegment_name,segment_type from dba_extents where file_id=file and block betweenblock_id and block_id+blocks ;
[1] [2] [3] [4]