数据库的优化
概述
影响数据库性能的因素包括：系统、数据库、网络。
数据库的优化包括：优化数据库磁盘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’ ;
使用如下查询可以得到当