数据库性能检查指导方案－ Part II-数据库专栏,SQL Server-数据库其他-idc中文资讯站-客观公证的IDC产业权威媒体,关注域名、虚拟主机、服务器、网络、开源技术资讯，整合运营商资源，客观评价各地机房带宽线路质量、IDC企业名录等信息

数据库性能检查指导方案－ Part II-数据库专栏,SQL Server

来源：作者：发布时间:2007-12-25 13:48:11

    存储性能评估

在存储性能评估的时候，我们使用磁盘性能指数（dpi, disk performance index），下表列出了dpi中的各项指数，这个评分系统并不意味着对磁盘的使用和分配的全方位评估，而只是代表一个晴雨表，反映当前磁盘的使用和分配上是否存在需要改进或注意的地方。

mpi指数

分类

所需等级

最高分

调整表和索引

是

30

表的行连接问题

无

30

分离关键的oracle文档

是

30

回滚段的平衡

30

临时段的平衡

30

使用最多的前10个sql的磁盘使用率

<5%

60

是否已调整使用磁盘最多的前25个sql

是

40

mpi指数

总分

250

1．调整表和索引

由于表和索引的数据块通常是被同时读取的，所以应该尽量将表和其相关联的索引放置在不同的磁盘上，以便减少文档的i/o冲突。

检查方法：

select i.index_name, t.table_name, t.tablespace_name
from user_tables t, user_indexes i
where t.table_name = i.table_name
   and t.tablespace_name = i.tablespace_name;

返回结果是创建在相同表空间中的表和相关联的索引。建议创建新的表空间用于专门存放索引，并将当前的索引rebuild到新创建的表空间中。

alter index idx_name rebuild tablespace ts_name;

评估准则：

等级

分数

表和索引放在同一磁盘上

0

存储使用了磁盘阵列，没有进一步调整

20

存储使用了磁盘阵列，对于raid类型已作过调整

30

表和索引已规划在不同磁盘上

30

2．表的行链接问题

当更新一张表，而数据块中又没有足够的剩余空间来容纳所作的修改时，就会发生“行链接”现象，该记录被链接到另外一个有足够空间的数据块中，也就是一条记录跨越了多个数据块，这样在读取该记录的时候就会消耗更多的i/o，当数据库中有大量的“行链接”现象存在时，数据库的整体性能就会下降。

检查方法：

sqlplus /nolog

connect app_user/password

sql> @$oracle_home/rdbms/admin/utlchain.sql

sql> analyze table list chained rows;

sql> select count(*) chained_rows, table_name
from chained_rows
group by table_name;

假如没有返回任何行，则表示没有“行链接”现象。否则将按照已分析过的表显示每张表中有多少记录出现了“行链接”现象。

“行链接”现象的产生跟pctfree参数的配置不当有关系。pctfree值默认为10%，假如系统中存在大量行链接，表示这个参数指定的块保留空间过小，不足以容纳块中任何记录的更新操作。此时应该增大相应表的pctfree值。

评估准则：

等级

分数

存在行链接现象

0

不存在行链接现象

30

3．分离关键的oracle文档

无论是出于安全性的考虑还是性能的考虑，都建议将关键的oracle文档分布在可用的单独磁盘上。

首先在错误出现之后，用来被恢复的数据文档和用来恢复的控制文档，重作日志文档，归档日志文档应该分离存放。假如有可能，将下列各个关键文档分布在不同的磁盘上。

系统表空间（system），临时表空间（temp），回滚表空间（undo），联机重作日志文档（redo）和归档日志文档（arch），经常访问的用户表空间，经常访问的用户索引表空间，操作系统盘，$oracl_ebase中的关键oracle软件文档。

至少联机重作日志文档（redo）和归档日志文档（arch）应该跟其他文档存放在不同的磁盘上，并且由于日志文档的大部分时间为只写属性，所以需要考虑raid5在写方面的弱势，尽量不要将日志文档存放在raid5的阵列组上。

检查方法：

select file_name, tablespace_name, bytes
from dba_data_files
union all
select file_name, tablespace_name, bytes
from dba_temp_files
union all
select name file_name, null, null
from v$controlfile
union all
select member file_name, to_char(a.group#) tablespace_name, b.bytes bytes
from v$logfile a, v$log b
where a.group# = b.group#
union all (select value file_name, null, null
             from v$parameter
            where name 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

已调整

30

4．回滚段的平衡

在oracle 9i和oracle9i之前假如没有使用回滚段自动管理，那么对于回滚段的性能仍然是需要监控并且调整的。

检查是否使用了回滚段自动管理：

select name, value from v$parameter where name like %undo_%;

假如返回结果中undo_management的值是auto，则表示使用了回滚段自动管理，同时undo_tablespace值显示了自动管理使用的回滚表空间，undo_retention值显示了在回滚表空间中保留回滚数据的时限，以秒为单位。

注意：假如undo_management的值是auto但是undo_tablespace没有配置相应的值，那么就会使用system表空间中的system回滚段，这个是绝对应该避免的现象。

假如没有使用回滚段自动管理，那么需要监控用户使用回滚段的频度，原则上认为不应该有超过1个用户同时使用1个回滚段。

检查方法：

select a.name,
       b.extents,
       b.rssize,
       b.xacts,
       b.waits,
       b.gets,
       optsize,
       status
from v$rollname a, v$rollstat b
where a.usn = b.usn;

检查输出结果，对于任何回滚段而言，假如xacts（活动事务）和waits（段头等待）经常超出1，那么就表明需要增加回滚段数目，以避免可能出现的争用。

增加回滚段的方法：

create rollback segment rs_name tablespace rbs storage(initial 1m next 2m);
alter rollback segment rs_name online;

假如使用了回滚段自动管理，那么能够从v$undostat, v$rollstat, dba_undo_extents等视图中查询当前回滚段的使用和分配情况。

评估准则：

等级

分数

有回滚段等待现象

0

无回滚段等待现象

30

使用了回滚段自动管理

30

5．临时段的平衡

当初始化参数中定义的sort_area_size大小无法满足排序需要的空间，就会使用临时表空间中的临时段进行排序，磁盘排序比内存排序要慢100-10000倍，所以尽量减少磁盘排序是性能调整工作的一个重要部分。

可能引起排序的操作有create index, distinct, order by, group by等。

检查方法：

select name, value from v$sysstat where name like %sorts%;

返回结果中的sorts (memory)表示内存排序，而sorts (disk)则表示磁盘排序，假如存在大量的磁盘排序，则表明我们需要增加sort_area_size或hash_area_size等排序区的大小，或需要检查现在系统中消耗大量磁盘的sql是否已经过调整（检查前25位消耗磁盘的sql在后面部分将提到）。

检查使用磁盘排序的会话信息，能够定位执行了大量磁盘排序的会话。

检查方法：

select b.name, a.sid, a.value
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
   and b.name = sorts (disk)
   and a.value > 0
order by a.value desc;

假如有可能我们应该将临时表空间中的多个临时数据文档分布在不同的磁盘上，以减少排序时可能会产生的磁盘冲突。

在oracle9i中，我们能够配置pga_aggregate_size初始化参数来指定任何会话将使用的pga大小，同时也必须配置workarea_size_policy参数为auto。其他周详信息见内存性能评估中“4。内存中的排序”部分。

评估准则：

等级

分数

对于存在的磁盘排序没有评估

0

已就存在的磁盘排序进行过调整

30

6．最浪费磁盘读操作的前10个语句占任何语句的比例

通常一个没有优化系统中，10个最常用的sql语句的访问量会占到整个系统中磁盘读操作的50%以上。这些sql是最需要进行优化的部分，也是优化工作中优先级很高的部分。通常我们的优化目标是将这些sql的磁盘读操作百分比降低到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;

评估准则：

等级

分数

<5%

60

5-19%

50

20-25%

30

>25%

0

7．调整前25个最浪费磁盘读操作的语句

在没有调整的情况下，绝大多数系统中，访问量占前25位的语句的磁盘读操作将占用整个系统任何磁盘读操作的75%，对这部分语句进行调整是至关重要的。这部分脚本用于获得访问量占前25位的sql语句。输出结果中的exec表示该sql被执行的次数。

检查方法：

set serveroutput on size 1000000
declare
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;
begin
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 len1 > 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 b
where a.tablespace_name = b.tablespace_name
   and b.extent_management = dictionary
group by a.tablespace_name
order by sum(a.extents);

检查输出结果，假如显示某个表空间中的extents总数超过了4096，那么需要扩大这个表空间的extent大小，过多的extent对于dmt的空间管理有负面影响。

2)      本地管理表空间中单个segement的extent数不超过1024

检查方法：

select segment_name, segment_type, extents, bytes, b.tablespace_name
from dba_segments a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
   and b.extent_management = local
   and a.extents > 1024;

检查输出结果，返回的记录都是单个段的区间大于1024的对象，对于这些对象，应该创建一个单独的具备更大extent大小的表空间，然后将这些对象move到新的表空间中去。

3)      检查字典管理表空间的pctincrease值是否是0

为了表空间中的任何extent大小相同，建议表空间中的任何段都不要配置单独的storage参数。对于表空间的pctincrease参数，建议配置为0，同时应该配置minextents参数，确保初始分配足够的空间给新创建的对象。

对于lmt表空间，storage参数中的pctincrease和next参数均无效，建议配置适当的uniform参数管理表空间的extent分配。

4)      考虑使用分区来避免磁盘争用

分区表在管理的方便性和性能的提高上都有较强的实用性，甚至能够认为分区是提高和大型表有关的性能的最好方法。通过访问一个表或索引的较小片段，而不是访问整个表或索引，分区能够很好地提高效率。假如一个表或索引的分区位于不同的磁盘上，就更能够大大增加数据吞吐量，获得很好的数据库性能。

对于分区的使用，暂时不在本文的叙述范围内，请参阅其他的分区文档。

5)      对于分区表的非分区键索引是否是全局分区索引

由于分区表的数据量通常比较巨大，所以假如在分区表的非分区键上创建索引，那么建议创建为全局分区索引，这样能够更好地提高性能。注意：假如截断了一个分区的数据或删除了一个分区，那么必须rebuild这个分区表中的全部全局索引，否则这些全局索引将处于invalid状态，导致使用到这些索引的sql语句失败。

喜欢本文，那就收藏到：