Oracle系列培训教程之七数据库性能优化.docx
《Oracle系列培训教程之七数据库性能优化.docx》由会员分享，可在线阅读，更多相关《Oracle系列培训教程之七数据库性能优化.docx（20页珍藏版）》请在冰豆网上搜索。
Oracle系列培训教程之七数据库性能优化
目录
目录1
数据库的优化2
概述2
监控数据库的性能：
2
优化数据库磁盘I/O2
建立和优化数据库文件的方针：
6
监控磁盘I/O的方法：
7
优化回滚段7
检测回滚段争用：
7
通过以下公式计算等待比率：
8
若任何一个的比率大于1％则建议再创一个回滚段：
8
避免动态分配空间8
优化Redo日志8
检测Redo日志缓冲区锁存：
8
减少Redo日志转储I/O争用：
8
优化系统全局区（SGA）9
优化数据库缓冲区高速缓存:
错误！
未定义书签。
优化共享池：
错误！
未定义书签。
优化数据字典高速缓存：
14
优化游标：
15
注释：
以上增加SGA分配的调整以SGA不被换出实存为限，否则SGA部分换出实存反而降低Oracle性能。
15
优化数据库对象：
15
管理表的动态分配。
15
避免链接行。
错误！
未定义书签。
优化索引存储参数。
16
数据库的优化
概述
影响数据库性能的因素包括：
系统、数据库、网络。
数据库的优化包括：
优化数据库磁盘I/O、优化回滚段、优化Rrdo日志、优化系统全局区、优化数据库对象。
监控数据库的性能：
在init.ora参数文件中设置TIMED_STATISTICS=TRUE和在你的会话层设置ALTERSESSIONSETSTATISTICS=TRUE。
运行svrmgrl用connectinternal注册，在你的应用系统正常活动期间，运行utlbstat.sql开始统计系统活动，达到一定的时间后，执行utlestat.sql停止统计。
统计结果将产生在report.txt文件中。
（utlbstat.sqlutlestat.sql一般存放在$ORACLE_HOME/RDBMS/ADMIN子目录下）
优化数据库磁盘I/O
检查系统的I/O问题
在UNIX系统中工具sar－d能检查整个系统的iostat（IOstatistics），在NT系统上则使用性能监视器（PerformanceMonitor）.
反映oracle文件I/O的进程
文件
进程
LGWR
DBWN
ARCH
SMON
PMON
CKPT
Fore_ground
PQSlave
数据库文件
Y
Y
Y
Y
Y
Y
Log文件
Y
归档文件
Y
控制文件
Y
Y
Y
Y
Y
Y
Y
Y
使用V$FILESTAT确定oracle数据文件I/O
SELECTNAME,PHYRDS,PHYWRTSFROMV$DATAFILEDF,V$FILESTATFSWHEREDF.FILE#=FS.FILE#;
使用分布I/O减少磁盘竞争
●将数据文件和redolog文件分开
●Striping表数据
●分开表和索引
●减少与oracle无关的磁盘I/O
避免动态空间管理
在创建如表或回滚段的数据库实体时，在数据库中会为这些数据分配空间，该空间被称为段。
如果数据库操作引起数据增加并超出了分配的表空间，oracle会扩展该段，动态扩展会降低系统性能。
●确定动态扩展
selectname,valuefromv$sysstatwherename=’recursivecalls’;
●分配分区
确定实体的最大大小；
选择存储参数值，使oracle分配足够大的分区，在创建实体时可以装入所有数据
●避免回滚段的动态空间管理
回滚段大小由其存储参数所决定，回滚段必须能保存所有交易的回滚入口；
使用settransaction命令可以为回滚段赋予交易的合适的大小；
对长的查询的修改数据，应赋予大的回滚段，以保持所有的回滚入口；
对OLTP交易，由于频繁交易，每个交易只修改小量的数据，因此赋予小的回滚段。
●减少迁移和链接行
1.使用ANALYZE收集迁移和链接行的信息；
2.查询输出表：
chained_rows;
3.如果有许多迁移和链接行，就需要消除迁移行，方法如下：
A.创建与原表相同列的中间表，以保存迁移和链接行；
B.从原表中删除迁移和链接行；
C.将中间表中的行插入到原表中；
D.删除中间表
4.删除第一步收集的信息；
5.重新使用ANALYZE命令查询输出表
6.在输出表中出现的行都是链接行，只能通过增加数据块的大小来清除。
调整排序
内存中排序
使用动态表V$SYSSTAT的信息反映排序
SELECTNAME,VALUEFROMV$SYSSTATWHERENAMEIN（‘SORTS（MEMORY）’,’SORTS（DISK）’）;
SORTS（MEMORY）－不需要使用I/O操作而完全在内存完成的排序数；
SORTS（DISK）－需要使用I/O操作与磁盘临时段才能完成数据的排序数目。
增大SORT_AREA_SIZE以避免磁盘排序
使用NOSORT创建非排序的索引
CREATEINDEXINDEX_NAMEONTABLETABLE_NAME（COLUMN_NAME）NOSORT;
调整Checkpoints
一个checkpoint是oracle自动执行的一种操作，当检查点操作时，数据库中的所有缓冲区会写回磁盘，所有数据库的控制文件被更新。
Checkpoint频繁发生会加快数据库的恢复，但是增加了I/O次数，会降低系统的性能。
调整LGWR和DBWnI/O
调整LGWRI/O
每次I/O写的大小依赖于LOG缓冲区的大小，该大小由LOGBUFFER所设置，缓冲区太大会延迟写操作，太小可能导致频繁的小的I/O操作。
如果I/O操作的平均大小很大，那么LOG文件就会成为瓶颈，可以使用STRIPEREDOLOG文件避免这个问题。
调整DBWNI/O
使用初始参数DB_WRITER_PROCESSES，可以创建多个数据库写进程。
调整竞争
由多个进程同时请求使用相同的资源时，就产生了竞争
确定竞争问题
视图V$RESOURCE_LIMIT提供了一些系统资源的使用限制。
如果系统存在无反应的现象，检查V$SYSTEM_EVENT，检查最大平均等待时间的事件；
如果存在过量的缓冲区等待，检查V$WAITSTAT，确定哪个类型的块有最多的等待次数和最长的等待时间，再查询V$SESSION_WAIT得到每个缓冲区的等待时间。
减少回滚段的竞争
通过检查V$WAITSTAT可以确定回滚段的竞争：
SELECTCLASS,COUNTFROMV$WAITSTATWHERECLASSIN（‘SYSTEMUODOHEADER’,’SYSTEMUODOBLOCK’,’UODOHEADER’,’UODOBLOCK’）;
减少调度进程的竞争
检查调度进程的busy率
SELECTNETWORK”PROTOCOL”,SUM（BUSY）/（SUM（BUSY）+SUM（IDLE））“TOTALBUSYRATE”FROMV$DISPATCHERGROUPBYNETWORK;
如果指定协议的调度进程忙的时间超过50％的有效工作时间，那么，增加调度进程可以提高使用该协议连接到oracle的性能。
检查调度进程相应队列的等待时间
SELECTNETWORK“PROTOCOL”DECODE（SUM（TOTALQ）,0.’NORESPONSES’,SUM（WAIT）/SUM（TOTALQ）||’HUNDREDTHSOFSECONDS’）“AVERAGEWAITTIMEPERRESPONSE”FROMV$QUEUEQ,V$DISPATCHERDWHEREQ.TYPE=’DISPATCHER’ANDQ.PADDR=D.PADDRGROUPBYNETWORK;
增加调度进程：
使用MTS_DISPATCHERS参数和ALTER_SYSTEM命令可以增加调度进程
减少共享服务器进程的竞争
共享服务器进程竞争可以由不断增加的请求等待时间所反映，使用如下查询：
selectdecode（totalq,0,’NoRequests’,wait/totalq||’hundredthsofseconds’）“AverageWaitTimePerRequests”fromv$queuewheretype=’COMMON’;
使用如下查询可以得到当前运行的共享服务进程数：
selectcount（*）“SharedServerProcesses”fromV$shared_serverswherestatus!
=’QUIT’;
oracle能自动增加共享服务进程，但是MTS_MAX_SERVERS的值可以更改。
减少redolog缓冲区latches竞争
在LGWR进程将redo入口从redolog缓冲区写入redolog文件后，该入口就会被新入口覆盖，供其他log的使用。
V$SYSSTAT中redobufferallocationretries反映用户进程等待redolog空间的次数：
Selectname,valuefromv$sysstatwherename=’redobufferallocationretries’;
redobufferallocationretries的值应该接近0，如果该值持续增加，那么，说明进程需要等待缓冲区的空间。
增大参数LOG_BUFFER的值可以增大redolog的大小。
确定redolog缓冲区latches竞争
redo分配latch；
redo复制latches。
一次只能有一个用户分配缓冲区中的空间，在分配了redo入口的空间后，用户进程将入口复制到缓冲区，其最大大小是由LOG_SMALL_ENTRY_MAX_SIZE指定。
Redo复制latches的数目由参数LOG_SIMULTANEOUS_COPIES指定。
检查redolog活动
对redolog缓冲区的频繁访问可能导致redolog缓冲区latches竞争，降低系统性能。
Oracle在动态表V$LATCH中收集了所有LATCH的统计信息。
其中：
表v$latch反映willing-to-wait请求的列
gets－成功的willing-to-wait请求数；
misses－初始不成功的willing-to-wait请求数；
sleeps－请求不成功的等待时间；
表v$latch反映immediate请求的列：
immediategets－成功的immediate请求数
immediatemisses－不成功的immediate请求数
使用如下查询：
selectln.name,gets,misses,immediate_gets,immediate_missesfromv$latchl,v$latchnamelnwhereln.namein（‘redoallocation’,’redocopy’）andln.latch#=l.latch#;
可以计算出各类请求的等待率。
减少latch竞争
要减少redoallocationlatch竞争，必须减少单个进程占用latch的时间。
要减少这个时间，可以减少该redoallocationlatch的复制。
减少LOG_SMALL_ENTRY_MAX_SIZE初始参数可以减少在redoallocationlatch的redo入口的复制次数和大小。
减少redocopyLatches竞争可以用增加LOG_SIMULTANEOUS_COPIES的值来增加LATCH数，最多可以达到CPU的两倍。
减少FreeList竞争
确定FreeList竞争，可以使用以下几步：
1.检查V$WAITSTAT，确定DATABLOCKS的竞争；
2.检查V$SYSTEM_EVENT,确定BUFFERBUSYWAITS，如果数值高，表明存在竞争；
3.在这种情况下，检查V$SESSION_WAIT查询每个缓冲区的忙等待、FILE、BLOCK及ID；
4.使用如下查询得到实体和FREELIST的名称：
SELECTSEGMENT_NAME,SEGMENT_TYPEFROMDBA_EXTENTSWHEREFILE_ID=fileANDBLOCKBETWEENblock_idANDblock_id+blocks;
5.使用如下查询找到FREELIST:
SELECTSEGMENT_NAME,FREELISTSFROMDBA_SEGMENTSWHERESEGMENT_NAME=segmentANDSEGMENT_TYPE=type;
增加FreeList
要减少表的Freelist的竞争，可以使用freelists参数重新创建表，方法有：
1.删除旧表，重新创建表；
2.通过从旧表选择数据插入到新表，删除旧表，重命名，完成表的重新创建；
3.使用Export和Import，export旧表，删除旧表，import该表。
建立和优化数据库文件的方针：
为表和索引创建不同的表空间。
将表和索引的表空间放在不同的磁盘上。
将REDO日志和回滚段放在不同的磁盘上。
将Oracle可执行文件和数据库文件放在单独的磁盘上。
确定最常用的表、索引，以及他们的表空间，并放在单独的磁盘上。
不要在Oracle数据磁盘上安装其他第三方软件。
监控磁盘I/O的方法：
用以下查询语句可以得到各表空间读写次数，phyrds+phywrts即是磁盘I/O量。
应按前面讲的方针调整数据文件的分布方式。
selectname,phyrds,phywrtsfromv$datafile,v$filestatwherev$datafile.file#=v$filestat.file#
结果如下：
NAMEPHYRDSPHYWRTS
----------------------------------------------------------------------
/u/oracle/OraHome/oradata/pb/system01.dbf25118
/u/oracle/OraHome/oradata/pb/tools01.dbf52
/u/oracle/OraHome/oradata/pb/rbs01.dbf1820
/u/oracle/OraHome/oradata/pb/temp01.dbf42
/u/oracle/OraHome/oradata/pb/users01.dbf455
/u/oracle/OraHome/oradata/pb/indx01.dbf42
/u/oracle/OraHome/oradata/pb/drsys01.dbf42
7rowsselected.
优化回滚段
检测回滚段争用：
selectclass,countfromv$waitstat
whereclassin（'undoheader','undoblock','systemundoheader','systemundobolck'）
CLASSCOUNT
----------------------------
systemundoheader0
undoheader0
undoblock0
selectsum（value）fromv$sysstatwherenamein（'consistentgets','dbblockgets'）
SUM（VALUE）
----------
20589
通过以下公式计算等待比率：
systemheaderwaits=systemundoheader/total
readssystemblockwaits=systemblock/total
readsrollbackheaderwaits=undoheader/totalreads
rollbackblockwaits=undoblock/totalreads
若任何一个的比率大于1％则建议再创一个回滚段：
createrollbacksegmentrbs21tablespacerbsstorage（inittial10koptimal20knext10kmaxextents8）;
避免动态分配空间
用以下语句检查回滚段的动态分配：
selectname,shrinksfromv$rollstat,v$rollnamewherev$rollstat.usn=v$roll;
NAMESHRINKS
--------------------------
SYSTEM0
RBS0100
RBS11
若动态分配次数较多可增大回滚段的初始容量。
ALTERROLLBACKSEGMENTRBS0
STORAGE（inittial20koptimal40knext10kmaxextents8）;
优化Redo日志
检测Redo日志缓冲区锁存：
selectname,valuefromv$sysstat
wherename='radologspacerequests';
value值应接近0若较大则应加大INITXXX.ORA中的LOG_BUFFER项的值。
减少Redo日志转储I/O争用：
将Redo日志分布在不同磁盘中。
1.优化系统全局区（SGA）
调整操作系统的内存需求
●减少页的换入换出；
●将SGA置于主存之中
使用SGA的主要目的是为了在内存中存储数据，以利于快速访问。
通过设置初始化参数PRE_PAGE_SGA=YES，在数据库启动时，可以将整个SGA读入内存，这样会减少在启动后ORACLE达到全部性能的总的时间。
使用如下命令可以查看SGA所分配的内存以及其内部的结构：
SVRMGR>SHOWSGA
TotalSystemGlobalArea107720688bytes
FixedSize69616bytes
VariableSize90701824bytes
DatabaseBuffers16777216bytes
RedoBuffers172032bytes
●为单个用户分配足够的内存
调整redolog缓冲区
参数LOG_BUFFER指定了REDOLOG的缓冲区的保留大小。
LOG写进程（LGWR）在该缓冲区被填充时总是运行的，在新的LOG进入缓冲区时，原来的LOG应已经写入磁盘。
调整私有sql和pl/sql区
●标识不必要的语法分析调用
1.在sql跟踪工具有效时运行应用
2.查看视图V$SQLAREAview
SVRMGR>selectsql_text,parse_calls,executionsfromv$sqlarea;
如果parse_calls值接近execution值，可能就是不断地对sql语句进行语法分析
3.执行如下查询：
select*fromv$statnamewherenamein（‘parse_count（hard）’,’execute_count’）;
其结果类似于:
statistic#,name
----------------------------------
100parse_count
90execute_count
然后执行如下查询：
select*fromv$sesstatwherestatistics#in（90,100）orderbyvalue,sid;
●减少不必要的语法分析调用
初始化参数open_cursors的最大值依赖于操作系统，最少值为5
调整共享池
数据字典或库快存的没有命中，其开销大大多于缓冲快存的没有命中，因此，首先应该为共享池分配足够的空间。
使用如下语句可以确定库快存和数据字典快存的命中率：
select（sum（pins-reloads））/sum（pins）“LibCache”fromv$librarycache；
select（sum（gets-getmisses-usage-fixed））/sum（gets）“RowCache”fromv$librarycache;
共享池中的自由内存可以查看：
select*fromv$sgastatwherename=’freememory’;
当然，如果共享池满了并不一定存在问题，只要上面所说的比率接近于1，就不需要增加共享池大小；
如果自由内存接近于0而且库快存或数据字典快存的命中率小于0.95，那么需要增加共享池的大小。
●调整LibraryCache
1．检查库快存的活动
selectsum（pins）“Executions”,sum（reloads）”CacheMisseswhileExecuting”fromv$librarycache;
“Executions”列指明sql语句，pl/sql块和实体定义被访问执行了的次数，”CacheMisseswhileExecuting”指明其中没有命中的次数。
2．减少库快存的非命中
1、分配更多的库快存（可以增加初始化参数sharedpoolsize的值；为了利用增加的共享sql区，增加初始化参数opencursors的值）；
2、尽可能使用标准的sql语句（sql语句或pl/sql块必须一致，包括字符和空格；sql语句或pl/sql块中对schema实体的引用必须解析到同一schema的同一对象；sql语句中试用的变量的名字和数据类型必须匹配；sql语句必须使用相同的优化方法和优化目标）；
尽可能使用标准的sql语句，策略：
●语句中尽量使用变量而不要使用常量
●确保应用用户不会改变优化方法和目标
●标准化的变量命名和空格转换
●尽可能使用存储过程
3、使用cursor_space_for_time加速共享sql区的访问：
该参数指定是否共享sql区可以释放，如果为false（默认值），一个共享sql区就可以被释放；如果为true，一个共享的sql区只存在所有与其相关的游标关闭后才可以被释放。
如果库快存在语句执行时有非命中，不要将其设置为true，否则对新的sql语句将没有空间。
●调整DataDictionaryCache
1、监视数据字典快存的活动
selectsum（gets）“DataDictionaryGets”,sum（getmisses）“DataDictionaryCacheGetMisses”fromv$rowcache;
2、减少数据字典快存的非命中
对频繁访问的数据字典快存没命中和命中比应少于10％－15％。
要增加数据字典快存
升级会员 