数据库性能检查指导方案.docx
《数据库性能检查指导方案.docx》由会员分享，可在线阅读，更多相关《数据库性能检查指导方案.docx（13页珍藏版）》请在冰豆网上搜索。
数据库性能检查指导方案
在系统稳定之后，应该按照本指导方案每个月检查一次产品数据库。
该指导方案适用于Oracle9i数据库，因为有些脚本在9i中才可以运行。
检查方式均为以sysdba身份登录数据库以后在SQLPLUS中执行命令脚本（每小节的“检查方法”部分有详细的命令脚本）。
登陆数据库的命令：
sqlplus“sys/passwordassysdba”
一．内存性能评估
在内存性能评估的时候，我们使用内存性能指数（MPI,MemoryPerformanceIndex），下表列出了MPI中的各项指数，这个评分系统并不意味着对内存的使用和分配的全方位评估，而只是代表一个晴雨表，反映当前系统内存的使用和分配状况。
MPI指数
分类
所需等级
最高分
缓冲区命中率（BufferCache）
>98%
30
数据字典命中率（DictionaryCache）
>98%
30
库缓存命中率（LibraryCache）
>98%
30
内存中的排序（SortinMemory）
>98%
30
空闲的数据缓冲区比例
10-25%
30
使用最多的前10个SQL占用的内存
<5%
60
是否已经调整使用最多的前25个SQL
是
30
是否尝试固定高速缓存中经常使用的对象
是
10
MPI指数
总分
250
1．缓冲区命中率
显示了对于数据总读取量而言，非磁盘读取（缓冲区命中）的百分比。
当然，十分高的命中率并不代表数据库性能一定优良，也有可能是糟糕的SQL引起了大量的缓冲区读操作，只有在已经调整过首要的查询之后，这个命中率才能更好地反映数据库性能。
检查方法：
select（1-（sum（decode（name,'physicalreads',value,0））/
（sum（decode（name,'dbblockgets',value,0））+
sum（decode（name,'consistentgets',value,0）））））*100
"HitRatio"
fromv$sysstat;
评估准则：
等级
分数
<90%
0
90-94%
10
95-98%
20
>98%
30
2．数据字典命中率
显示了对数据字典和其它对象的内存读操作的百分比。
检查方法：
select（1-（sum（getmisses）/sum（gets）））*100"HitRatio"
fromv$rowcache;
评估准则：
等级
分数
<85%
0
86-92%
10
92-98%
20
>98%
30
3．库缓存命中率
显示了对SQL和PL/SQL对象的内存读操作的百分比。
同样注意，很高的命中率并不总是反映数据库性能优秀。
检查方法：
selectsum（pins）/（sum（pins）+sum（reloads））*100"HitRatio"
fromv$librarycache;
评估准则：
等级
分数
<90%
0
90-94%
10
94-98%
20
>98%
30
4．内存中的排序
根据初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE的值，用户的排序操作可能在内存中执行，也可能在临时表空间中执行。
这个检查用以显示在内存中排序占总排序的百分比。
检查方法：
selecta.value"DiskSorts",
b.value"MemorySorts",
round（（100*b.value）/
decode（（a.value+b.value）,0,1,（a.value+b.value））,
2）"PctMemorySorts"
fromv$sysstata,v$sysstatb
wherea.name='sorts（disk）'
andb.name='sorts（memory）';
评估准则：
等级
分数
<90%
0
90-94%
10
94-98%
20
>98%
30
5．空闲的数据缓冲区比例
空闲的记录数除以X$BH表中的记录总数（即所分配的数据块缓冲区的总数）得到的空闲缓冲区百分比。
同样注意，拥有众多空闲缓冲区的数据库不一定是最佳环境，因为可能是缓冲区设置过大，浪费内存。
检查方法：
selectdecode（state,0,'FREE',1,
decode（lrba_seq,0,'AVAILABLE','BEINGUSED'）,
3,'BEINGUSED',state）"BlockStatus",count（*）
fromx$bh
groupbydecode（state,0,'FREE',1,
decode（lrba_seq,0,'AVAILABLE','BEINGUSED'）,
3,'BEINGUSED',state）;
评估准则：
等级
分数
<5%
0
5-19%
30
20-25%
20
>25%
0
6．最浪费内存的前10个语句占全部内存读取量的比例
通常一个没有优化系统中，10个最常用的SQL语句的访问量会占到整个系统中内存读操作的50%以上。
这些SQL是最需要进行优化的部分，也是优化工作中优先级很高的部分。
检查方法：
selectsum（pct_bufgets）
from（selectrank（）over（orderbybuffer_getsdesc）asrank_bufgets,
to_char（100*ratio_to_report（buffer_gets）over（）,'999.99'）pct_bufgets
fromv$sqlarea）
whererank_bufgets<11;
评估准则：
等级
分数
<5%
60
5-19%
50
20-25%
30
>25%
0
7．调整前25个最浪费内存的语句
在没有调整的情况下，绝大多数系统中，访问量占前25位的语句的内存读操作将占用整个系统所有内存读操作的75%，对这部分语句进行调整是至关重要的。
这部分脚本用于获得访问量占前25位的SQL语句。
检查方法：
setserveroutputonsize1000000
declare
top25number;
text1varchar2（4000）;
xnumber;
len1number;
cursorc1is
selectbuffer_gets,substr（sql_text,1,4000）
fromv$sqlarea
orderbybuffer_getsdesc;
begin
dbms_output.put_line（'Gets'||''||'Text'）;
dbms_output.put_line（'--------'||''||'---------------'）;
openc1;
foriin1..25loop
fetchc1
intotop25,text1;
dbms_output.put_line（rpad（to_char（top25）,9）||''||
substr（text1,1,66））;
len1:
=length（text1）;
x:
=66;
whilelen1>x-1loop
dbms_output.put_line（'"'||substr（text1,x,66））;
x:
=x+66;
endloop;
endloop;
end;
/
评估准则：
本部分没有评估准则，需要开发人员或者DBA去确认在这25个SQL中属于应用系统的语句是否都已经作过调优。
8．固定缓存对象
尝试在内存中固定（pin）经常使用的对象，包括表，存储过程等。
检索需要在共享池中要求大于100K连续空间的对象：
select*
fromv$db_object_cache
wheresharable_mem>100000
andtypein（'PACKAGE','PACKAGEBODY','PROCEDURE','FUNCTION'）;
考察返回的结果，确认是否需要pin到共享池中，返回结果中的KEPT字段如果是YES，那么表示该对象已经固定在了共享池中，为NO，则表示还没有固定。
如果需要固定，使用下面的语句：
execdbms_shared_pool.keep（'SYS.STANDARD'）;
数据库默认安装的时候没有创建dbms_shared_pool包，所以需要先创建该包。
cd$ORACLE_HOME/rdbms/admin
sqlplus“/assysdba”
@dbmspool.sql
如果我们要固定表，那么可以在创建表的时候或者修改表属性时使用CACHE关键字，将表放置到BufferCache的LRU列表的MRU端。
通常我们需要对于较小的但是频繁使用的表进行这种操作。
altertabletable_namecache;
我们也可以将需要频繁使用的表放置到另外一个独立的BufferCache中，比如KEEP池。
这种操作可以使这些表的数据不至于很快被清除出DefaultBufferCache。
altertabletable_namestorage（bufferpoolkeep）;
评估准则：
本部分没有评估准则，需要开发人员或者DBA在系统分析以后谨慎执行。
二．存储性能评估
在存储性能评估的时候，我们使用磁盘性能指数（dpi,diskperformanceindex），下表列出了dpi中的各项指数，这个评分系统并不意味着对磁盘的使用和分配的全方位评估，而只是代表一个晴雨表，反映当前磁盘的使用和分配上是否存在需要改进或注意的地方。
MPI指数
分类
所需等级
最高分
调整表和索引
是
30
表的行连接问题
无
30
分离关键的oracle文档
是
30
回滚段的平衡
30
临时段的平衡
30
使用最多的前10个sql的磁盘使用率
<5%
60
是否已调整使用磁盘最多的前25个sql
是
40
MPI指数
总分
250
1．调整表和索引
由于表和索引的数据块通常是被同时读取的，所以应该尽量将表和其相关联的索引放置在不同的磁盘上，以便减少文档的i/o冲突。
检查方法：
selecti.index_name,t.table_name,t.tablespace_name
fromuser_tablest,user_indexesi
wheret.table_name=i.table_name
andt.tablespace_name=i.tablespace_name;
返回结果是创建在相同表空间中的表和相关联的索引。
建议创建新的表空间用于专门存放索引，并将当前的索引rebuild到新创建的表空间中。
alterindexidx_namerebuildtablespacets_name;
评估准则：
等级
分数
表和索引放在同一磁盘上
0
存储使用了磁盘阵列，没有进一步调整
20
存储使用了磁盘阵列，对于raid类型已作过调整
30
表和索引已规划在不同磁盘上
30
2．表的行链接问题
当更新一张表，而数据块中又没有足够的剩余空间来容纳所作的修改时，就会发生“行链接”现象，该记录被链接到另外一个有足够空间的数据块中，也就是一条记录跨越了多个数据块，这样在读取该记录的时候就会消耗更多的i/o，当数据库中有大量的“行链接”现象存在时，数据库的整体性能就会下降。
检查方法：
sqlplus/nolog
connectapp_user/password
sql>@$oracle_home/rdbms/admin/utlchain.sql
sql>analyzetablelistchainedrows;
sql>selectcount（*）chained_rows,table_name
fromchained_rows
groupbytable_name;
假如没有返回任何行，则表示没有“行链接”现象。
否则将按照已分析过的表显示每张表中有多少记录出现了“行链接”现象。
“行链接”现象的产生跟pctfree参数的配置不当有关系。
pctfree值默认为10%，假如系统中存在大量行链接，表示这个参数指定的块保留空间过小，不足以容纳块中任何记录的更新操作。
此时应该增大相应表的pctfree值。
评估准则：
等级
分数
存在行链接现象
0
不存在行链接现象
20
3．分离关键的oracle文档
无论是出于安全性的考虑还是性能的考虑，都建议将关键的oracle文档分布在可用的单独磁盘上。
首先在错误出现之后，用来被恢复的数据文档和用来恢复的控制文档，重作日志文档，归档日志文档应该分离存放。
假如有可能，将下列各个关键文档分布在不同的磁盘上。
系统表空间（system），临时表空间（temp），回滚表空间（undo），联机重作日志文档（redo）和归档日志文档（arch），经常访问的用户表空间，经常访问的用户索引表空间，操作系统盘，$oracl_ebase中的关键oracle软件文档。
至少联机重作日志文档（redo）和归档日志文档（arch）应该跟其他文档存放在不同的磁盘上，并且由于日志文档的大部分时间为只写属性，所以需要考虑raid5在写方面的弱势，尽量不要将日志文档存放在raid5的阵列组上。
检查方法：
selectfile_name,tablespace_name,bytes
fromdba_data_files
unionall
selectfile_name,tablespace_name,bytes
fromdba_temp_files
unionall
selectnamefile_name,null,null
fromv$controlfile
unionall
selectmemberfile_name,to_char（a.group#）tablespace_name,b.bytesbytes
fromv$logfilea,v$logb
wherea.group#=b.group#
unionall（selectvaluefile_name,null,null
fromv$parameter
wherenamelikelog_archive_dest_%
andvalueisnotnull
minus
selectvaluefile_name,null,null
fromv$parameter
wherenamelikelog_archive_dest_state%）;
返回数据库中任何关键文档存储的位置，由dba和sa考察返回的结果，确认已对于关键文档的存储位置作过符合实际情况的调整。
评估准则：
等级
分数
没有调整，全部在单个磁盘上
0
没有调整，全部在raid上
20
已调整
30
4．回滚段的平衡
在oracle9i和oracle9i之前假如没有使用回滚段自动管理，那么对于回滚段的性能仍然是需要监控并且调整的。
检查是否使用了回滚段自动管理：
selectname,valuefromv$parameterwherenamelike%undo_%;
假如返回结果中undo_management的值是auto，则表示使用了回滚段自动管理，同时undo_tablespace值显示了自动管理使用的回滚表空间，undo_retention值显示了在回滚表空间中保留回滚数据的时限，以秒为单位。
注意：
假如undo_management的值是auto但是undo_tablespace没有配置相应的值，那么就会使用system表空间中的system回滚段，这个是绝对应该避免的现象。
假如没有使用回滚段自动管理，那么需要监控用户使用回滚段的频度，原则上认为不应该有超过1个用户同时使用1个回滚段。
检查方法：
selecta.name,b.extents,b.rssize,b.xacts,b.waits,b.gets,optsize,status
fromv$rollnamea,v$rollstatbwherea.usn=b.usn;
检查输出结果，对于任何回滚段而言，假如xacts（活动事务）和waits（段头等待）经常超出1，那么就表明需要增加回滚段数目，以避免可能出现的争用。
增加回滚段的方法：
createrollbacksegmentrs_nametablespacerbsstorage（initial1mnext2m）;
alterrollbacksegmentrs_nameonline;
假如使用了回滚段自动管理，那么能够从v$undostat,v$rollstat,dba_undo_extents等视图中查询当前回滚段的使用和分配情况。
评估准则：
等级
分数
有回滚段等待现象
0
无回滚段等待现象
30
使用了回滚段自动管理
30
5．临时段的平衡
当初始化参数中定义的sort_area_size大小无法满足排序需要的空间，就会使用临时表空间中的临时段进行排序，磁盘排序比内存排序要慢100-10000倍，所以尽量减少磁盘排序是性能调整工作的一个重要部分。
可能引起排序的操作有createindex,distinct,orderby,groupby等。
检查方法：
selectname,valuefromv$sysstatwherenamelike‘%sorts%’;（oracle9）
selectname,valuefromv$sysstatwherenamelike%sorts%;（oracle9）
返回结果中的sorts（memory）表示内存排序，而sorts（disk）则表示磁盘排序，假如存在大量的磁盘排序，则表明我们需要增加sort_area_size或hash_area_size等排序区的大小，或需要检查现在系统中消耗大量磁盘的sql是否已经过调整（检查前25位消耗磁盘的sql在后面部分将提到）。
检查使用磁盘排序的会话信息，能够定位执行了大量磁盘排序的会话。
检查方法：
selectb.name,a.sid,a.valuefromv$sesstata,v$statnameb
wherea.statistic#=b.statistic#andb.name=sorts（disk）anda.value>0
orderbya.valuedesc;
假如有可能我们应该将临时表空间中的多个临时数据文档分布在不同的磁盘上，以减少排序时可能会产生的磁盘冲突。
在oracle9i中，我们能够配置pga_aggregate_size初始化参数来指定任何会话将使用的pga大小，同时也必须配置workarea_size_policy参数为auto。
其他周详信息见内存性能评估中“4。
内存中的排序”部分。
评估准则：
等级
分数
对于存在的磁盘排序没有评估
0
已就存在的磁盘排序进行过调整
30
6．最浪费磁盘读操作的前10个语句占任何语句的比例
通常一个没有优化系统中，10个最常用的sql语句的访问量会占到整个系统中磁盘读操作的50%以上。
这些sql是最需要进行优化的部分，也是优化工作中优先级很高的部分。
通常我们的优化目标是将这些sql的磁盘读操作百分比降低到5-19%。
检查方法：
selectsum（pct_bufgets）
from（selectrank（）over（orderbydisk_readsdesc）asrank_bufgets,
to_char（100*ratio_to_report（disk_reads）over（）,999.99）pct_bufgets
fromv$sqlarea）whererank_bufgets<11;
评估准则：
等级
分数
<5%
60
5-19%
50
20-25%
30
>25%
0
7．调整前25个最浪费磁盘读操作的语句
在没有调整的情况下，绝大多数系统中，访问量占前25位的语句的磁盘读操作将占用整个系统任何磁盘读操作的75%，对这部分语句进行调整是至关重要的。
这部分脚本用于获得访问量占前25位的sql语句。
输出结果中的exec表示该sql被执行的次数。
检查方法：
setserveroutputonsize1000000
declare
executionnumber;
top25 number;
text1 varchar2（4000）;
x number;
len1 number;
cursorc1is
selectexecutions,disk_reads,substr（sql_text,1,4000）
fromv$sqlarea
orderbydisk_readsdesc;
begin
dbms_output.put_line（exec|| ||reads|| ||text）;
dbms_output.put_line（-----||||--------||||
-------------）;
openc1;
foriin1..25loop
fetchc1
intoexecution,top25,text1;
dbms_output.put_line（rpad（to_char（execution）,5）||||
rpad（to_char（top25）,8）||||
升级会员 