Oracle9i数据库性能检查指导方案

1、oracle 9i 数据库性能检查指导方案以sysdba身份登录数据库：sqlplus “sys/password as sysdba”一内存性能评估在内存性能评估的时候，我们使用内存性能指数（mpi, memory performance index），下表列出了mpi中的各项指数，这个评分系统并不意味着对内存的使用和分配的全方位评估，而只是代表一个晴雨表，反映当前系统内存的使用和分配状况。mpi指数分类所需等级最高分缓冲区命中率（buffer cache）98%30数据字典命中率（dictionary cache）98%30库缓存命中率（library cache）98%30内存中的排序（

2、sort in memory）98%30空闲的数据缓冲区比例10-25%30使用最多的前10个sql占用的内存5%60是否已经调整使用最多的前25个sql是30是否尝试固定高速缓存中经常使用的对象是10mpi指数总分2501 缓冲区命中率显示了对于数据总读取量而言，非磁盘读取（缓冲区命中）的百分比。当然，十分高的命中率并不代表数据库性能一定优良，也有可能是糟糕的sql引起了大量的缓冲区读操作，只有在已经调整过首要的查询之后，这个命中率才能更好地反映数据库性能。检查方法：select (1 - (sum(decode(name, physical reads, value, 0) / (sum(

3、decode(name, db block gets, value, 0) + sum(decode(name, consistent gets, value, 0) * 100 hit ratio from v$sysstat;评估准则：等级分数98%302 数据字典命中率显示了对数据字典和其它对象的内存读操作的百分比。检查方法：select (1 - (sum(getmisses) / sum(gets) * 100 hit ratio from v$rowcache;评估准则：等级分数98%303 库缓存命中率显示了对sql和pl/sql对象的内存读操作的百分比。同样注意，很高的命中率并

4、不总是反映数据库性能优秀。检查方法：select sum(pins) / (sum(pins) + sum(reloads) * 100 hit ratio from v$librarycache;评估准则：等级分数98%304 内存中的排序根据初始化参数pga_aggregate_target或者sort_area_size的值，用户的排序操作可能在内存中执行，也可能在临时表空间中执行。这个检查用以显示在内存中排序占总排序的百分比。检查方法：select a.value disk sorts, b.value memory sorts, round(100 * b.value) / deco

5、de(a.value + b.value), 0, 1, (a.value + b.value), 2) pct memory sorts from v$sysstat a, v$sysstat b where = sorts (disk) and = sorts (memory);评估准则：等级分数98%305 空闲的数据缓冲区比例空闲的记录数除以x$bh表中的记录总数（即所分配的数据块缓冲区的总数）得到的空闲缓冲区百分比。同样注意，拥有众多空闲缓冲区的数据库不一定是最佳环境，因为可能是缓冲区设置过大，浪费内存。检查方法：select decode(state,0

6、,free,1, decode(lrba_seq, 0, available, being used), 3,being used,state) block status,count(*) from x$bh group by decode(state,0,free,1, decode(lrba_seq, 0, available, being used), 3,being used,state);评估准则：等级分数25%06 最浪费内存的前10个语句占全部内存读取量的比例通常一个没有优化系统中，10个最常用的sql语句的访问量会占到整个系统中内存读操作的50%以上。这些sql是最需要进行优化

7、的部分，也是优化工作中优先级很高的部分。检查方法：select sum(pct_bufgets) from (select rank() over(order by buffer_gets desc) as rank_bufgets, to_char(100 * ratio_to_report(buffer_gets) over(), 999.99) pct_bufgets from v$sqlarea) where rank_bufgets 11;评估准则：等级分数25%07 调整前25个最浪费内存的语句在没有调整的情况下，绝大多数系统中，访问量占前25位的语句的内存读操作将占用整个系统所有

8、内存读操作的75%，对这部分语句进行调整是至关重要的。这部分脚本用于获得访问量占前25位的sql语句。检查方法：set serveroutput on size 1000000declare top25 number; text1 varchar2(4000); x number; len1 number; cursor c1 is select buffer_gets, substr(sql_text, 1, 4000) from v$sqlarea order by buffer_gets desc;begin dbms_output.put_line(gets | | text); db

9、ms_output.put_line(- | | -); open c1; for i in 1 . 25 loop fetch c1 into top25, text1; dbms_output.put_line(rpad(to_char(top25), 9) | | substr(text1, 1, 66); len1 := length(text1); x := 66; while len1 x - 1 loop dbms_output.put_line( | substr(text1, x, 66); x := x + 66; end loop; end loop;end;/评估准则：

10、本部分没有评估准则，需要开发人员或者dba去确认在这25个sql中属于应用系统的语句是否都已经作过调优。8 固定缓存对象尝试在内存中固定（pin）经常使用的对象，包括表，存储过程等。检索需要在共享池中要求大于100k连续空间的对象：select * from v$db_object_cache where sharable_mem 100000 and type in (package, package body, procedure, function);考察返回的结果，确认是否需要pin到共享池中，返回结果中的kept字段如果是yes，那么表示该对象已经固定在了共享池中，为no，则表示还没

11、有固定。如果需要固定，使用下面的语句：exec dbms_shared_pool.keep(sys.standard);数据库默认安装的时候没有创建dbms_shared_pool包，所以需要先创建该包。cd $oracle_home/rdbms/adminsqlplus “/ as sysdba”dbmspool.sql如果我们要固定表，那么可以在创建表的时候或者修改表属性时使用cache关键字，将表放置到buffer cache的lru列表的mru端。通常我们需要对于较小的但是频繁使用的表进行这种操作。alter table table_name cache;我们也可以将需要频繁使用的表放

12、置到另外一个独立的buffer cache中，比如keep池。这种操作可以使这些表的数据不至于很快被清除出default buffer cache。alter table table_name storage (buffer pool keep);评估准则：本部分没有评估准则，需要开发人员或者dba在系统分析以后谨慎执行。二存储性能评估在存储性能评估的时候，我们使用磁盘性能指数（dpi, disk performance index），下表列出了dpi中的各项指数，这个评分系统并不意味着对磁盘的使用和分配的全方位评估，而只是代表一个晴雨表，反映当前磁盘的使用和分配上是否存在需要改进或注意的地方

13、。mpi指数分类所需等级最高分调整表和索引是30表的行连接问题无30分离关键的oracle文档是30回滚段的平衡30临时段的平衡30使用最多的前10个sql的磁盘使用率 $oracle_home/rdbms/admin/utlchain.sqlsql analyze table list chained rows;sql select count(*) chained_rows, table_name from chained_rows group by table_name;假如没有返回任何行，则表示没有“行链接”现象。否则将按照已分析过的表显示每张表中有多少记录出现了“行链接”现象。“行链

14、接”现象的产生跟pctfree参数的配置不当有关系。pctfree值默认为10%，假如系统中存在大量行链接，表示这个参数指定的块保留空间过小，不足以容纳块中任何记录的更新操作。此时应该增大相应表的pctfree值。评估准则：等级分数存在行链接现象0不存在行链接现象203 分离关键的oracle文档无论是出于安全性的考虑还是性能的考虑，都建议将关键的oracle文档分布在可用的单独磁盘上。首先在错误出现之后，用来被恢复的数据文档和用来恢复的控制文档，重作日志文档，归档日志文档应该分离存放。假如有可能，将下列各个关键文档分布在不同的磁盘上。系统表空间（system），临时表空间（temp），回滚表

15、空间（undo），联机重作日志文档（redo）和归档日志文档（arch），经常访问的用户表空间，经常访问的用户索引表空间，操作系统盘，$oracl_ebase中的关键oracle软件文档。至少联机重作日志文档（redo）和归档日志文档（arch）应该跟其他文档存放在不同的磁盘上，并且由于日志文档的大部分时间为只写属性，所以需要考虑raid5在写方面的弱势，尽量不要将日志文档存放在raid5的阵列组上。检查方法：select file_name, tablespace_name, bytes from dba_data_filesunion allselect file_name, tables

16、pace_name, bytes from dba_temp_filesunion allselect name file_name, null, null from v$controlfileunion allselect member file_name, to_char(a.group#) tablespace_name, b.bytes bytes from v$logfile a, v$log bwhere a.group# = b.group#union all (select value file_name, null, null from v$parameter where n

17、ame like log_archive_dest_% and value is not null minus select value file_name, null, null from v$parameter where name like log_archive_dest_state%);返回数据库中任何关键文档存储的位置，由dba和sa考察返回的结果，确认已对于关键文档的存储位置作过符合实际情况的调整。评估准则：等级分数没有调整，全部在单个磁盘上0没有调整，全部在raid上20已调整304 回滚段的平衡在oracle 9i和oracle9i之前假如没有使用回滚段自动管理，那么对于回滚

18、段的性能仍然是需要监控并且调整的。检查是否使用了回滚段自动管理：select name, value from v$parameter where name like %undo_%; 假如返回结果中undo_management的值是auto，则表示使用了回滚段自动管理，同时undo_tablespace值显示了自动管理使用的回滚表空间，undo_retention值显示了在回滚表空间中保留回滚数据的时限，以秒为单位。注意：假如undo_management的值是auto但是undo_tablespace没有配置相应的值，那么就会使用system表空间中的system回滚段，这个是绝对应该避

19、免的现象。假如没有使用回滚段自动管理，那么需要监控用户使用回滚段的频度，原则上认为不应该有超过1个用户同时使用1个回滚段。检查方法：select ,b.extents,b.rssize,b.xacts,b.waits,b.gets,optsize,statusfrom v$rollname a, v$rollstat b where a.usn = b.usn;检查输出结果，对于任何回滚段而言，假如xacts（活动事务）和waits（段头等待）经常超出1，那么就表明需要增加回滚段数目，以避免可能出现的争用。增加回滚段的方法：create rollback segment rs_na

20、me tablespace rbs storage(initial 1m next 2m);alter rollback segment rs_name online;假如使用了回滚段自动管理，那么能够从v$undostat, v$rollstat, dba_undo_extents等视图中查询当前回滚段的使用和分配情况。评估准则：等级分数有回滚段等待现象0无回滚段等待现象30使用了回滚段自动管理305 临时段的平衡当初始化参数中定义的sort_area_size大小无法满足排序需要的空间，就会使用临时表空间中的临时段进行排序，磁盘排序比内存排序要慢100-10000倍，所以尽量减少磁盘排序是

21、性能调整工作的一个重要部分。可能引起排序的操作有create index, distinct, order by, group by等。检查方法：select name, value from v$sysstat where name like %sorts%; (oracle 9)select name, value from v$sysstat where name like %sorts%; (oracle 9)返回结果中的sorts (memory)表示内存排序，而sorts (disk)则表示磁盘排序，假如存在大量的磁盘排序，则表明我们需要增加sort_area_size或hash_

22、area_size等排序区的大小，或需要检查现在系统中消耗大量磁盘的sql是否已经过调整（检查前25位消耗磁盘的sql在后面部分将提到）。检查使用磁盘排序的会话信息，能够定位执行了大量磁盘排序的会话。检查方法：select , a.sid, a.value from v$sesstat a, v$statname b where a.statistic# = b.statistic# and = sorts (disk) and a.value 0 order by a.value desc;假如有可能我们应该将临时表空间中的多个临时数据文档分布在不同的磁盘上，以减少

23、排序时可能会产生的磁盘冲突。在oracle9i中，我们能够配置pga_aggregate_size初始化参数来指定任何会话将使用的pga大小，同时也必须配置workarea_size_policy参数为auto。其他周详信息见内存性能评估中“4。内存中的排序”部分。评估准则：等级分数对于存在的磁盘排序没有评估0已就存在的磁盘排序进行过调整306 最浪费磁盘读操作的前10个语句占任何语句的比例通常一个没有优化系统中，10个最常用的sql语句的访问量会占到整个系统中磁盘读操作的50%以上。这些sql是最需要进行优化的部分，也是优化工作中优先级很高的部分。通常我们的优化目标是将这些sql的磁盘读操作

24、百分比降低到5-19%。检查方法：select sum(pct_bufgets)from (select rank() over(order by disk_reads desc) as rank_bufgets, to_char(100 * ratio_to_report(disk_reads) over(), 999.99) pct_bufgets from v$sqlarea) where rank_bufgets 11;评估准则：等级分数25%07 调整前25个最浪费磁盘读操作的语句在没有调整的情况下，绝大多数系统中，访问量占前25位的语句的磁盘读操作将占用整个系统任何磁盘读操作的75

25、%，对这部分语句进行调整是至关重要的。这部分脚本用于获得访问量占前25位的sql语句。输出结果中的exec表示该sql被执行的次数。检查方法：set serveroutput on size 1000000declare execution number; top25 number; text1 varchar2(4000); x number; len1 number; cursor c1 is select executions, disk_reads, substr(sql_text, 1, 4000) from v$sqlarea order by disk_reads desc;be

26、gin dbms_output.put_line(exec | | reads | | text); dbms_output.put_line(- | | - | | -); open c1; for i in 1 . 25 loop fetch c1 into execution, top25, text1; dbms_output.put_line(rpad(to_char(execution), 5) | | rpad(to_char(top25), 8) | | substr(text1, 1, 66); len1 := length(text1); x := 66; while le

27、n1 x - 1 loop dbms_output.put_line(- | substr(text1, x, 66); x := x + 66; end loop; end loop;end;/评估准则：本部分没有具体的评估准则，需要研发人员或dba去确认在这25个sql中属于应用系统的语句是否都已作过调优。8 其他存储相关的调整1) 字典管理表空间中的extent总数不超过4096检查方法：select a.tablespace_name, sum(a.extents) from dba_segments a, dba_tablespaces bwhere a.tablespace_name = b.tablespace_name and b.extent_management = dictionarygroup by a.tablespace_nameorder by sum(a.extents);检查输出结果，假如显示某个表空间中的extents总数超过了4096，那么需要扩大这个表空间的extent大小，过多的extent对于dmt的空间管理有负面影响。2) 本地