Oracle数据库性能优化.docx
- 文档编号:10506096
- 上传时间:2023-02-17
- 格式:DOCX
- 页数:25
- 大小:29.53KB
Oracle数据库性能优化.docx
《Oracle数据库性能优化.docx》由会员分享,可在线阅读,更多相关《Oracle数据库性能优化.docx(25页珍藏版)》请在冰豆网上搜索。
Oracle数据库性能优化
目录
目录1
数据库的优化2
概述2
监控数据库的性能:
2
优化数据库磁盘I/O2
检查系统的I/O问题2
使用分布I/O减少磁盘竞争3
避免动态空间管理3
调整排序4
调整Checkpoints4
调整LGWR和DBWnI/O5
建立和优化数据库文件的方针:
8
监控磁盘I/O的方法:
8
优化回滚段9
检测回滚段争用:
9
通过以下公式计算等待比率:
10
若任何一个的比率大于1%则建议再创一个回滚段:
10
避免动态分配空间10
优化Redo日志10
检测Redo日志缓冲区锁存:
10
减少Redo日志转储I/O争用:
11
1.优化系统全局区(SGA)11
调整操作系统的内存需求11
调整redolog缓冲区11
调整私有sql和pl/sql区12
调整共享池12
调整快速缓存15
调整多缓冲池17
调整排序区19
重新分配内存19
减少整个内存使用19
优化数据字典高速缓存:
19
优化游标:
20
注释:
以上增加SGA分配的调整以SGA不被换出实存为限,否则SGA部分换出实存反而降低Oracle性能。
20
优化数据库对象:
20
管理表的动态分配。
20
优化索引存储参数。
21
数据库的优化
概述
影响数据库性能的因素包括:
系统、数据库、网络。
数据库的优化包括:
优化数据库磁盘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)“Da
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 性能 优化