系统运行一段时间后，提升其速度的方法总结

BI系统运行一段时间后性能明显变慢了肿么办施系统慢数据库优化
【问题描述】

系统在运行了一段时间以后，在没有变更程序，且数据量没有大幅增加的时候，系统的执行效率明显变慢了肿么办？是求助研发人员，然后各种扯皮、谩骂乃至人生攻击？还是自已动手，丰衣足食？

【问题分析】

如果没有升级程序（为什么每次升级程序总是会让系统变得更糟而不是更好呢？这是一个值得研究的问题），数据量又没有大幅增加，系统硬件环境也没有变化，那么这个现象很可能是数据库本身的问题，引起数据库变慢的原因有很多，以下是几个比较可能的原因：

1、  数据库在进行了大量的删除、插入、更新后（ETL经常会干这个事情），由于数据库里不完全从物理上删除相应的数据，所以会导致表里占用了很多无用的物理块，表的索引中也会存在很多废弃的索引键，因此会导致访问表的时候额外读取了很多的数据块（block），包括表和索引，增加了IO的代价，因此效率降低，所以这就是为什么清表的时候一定要用truncate table而不要用delete from table方式的原因；

2、  Oracle是基于代价（cost）的优化模式，也就是oracle会根据数据库的统计信息（如表有多少条记录、有哪些索引、占用了多少数据块）对执行SQL各种可能的路径进行分析，从而在其中找到一个oracle认为最优的执行计划进行查询，但是在对数据进行了大量的删除、插入、更新后会导致数据库的统计信息越来越不准确，从而导致oracle误判，选择了一个较差的执行计划，引起执行效率降低；

3、  在对记录进行更新（update）的时候，由于记录都是一行一行紧密地存储在物理块（block）中的，因此当新update的记录比原来更长（占用的字节byte更多）的时候，在该记录原有的位置已经无法容纳该记录了，这时候oracle就会把记录迁移到另一个空闲的块中，并在原有的位置记录一个链接，指向新的块地址，这就叫行迁移，这样当数据被经常这样长期的蹂躏后，物理数据的存储就会变得非常乱，到处都是各种指针，导致数据库读取数据的效率变低；

4、  操作系统或者数据库都是按block（还记得安装oracle的时候可以改的block_size吗？）进行管理的，也就是oracle每次读数据的时候都会读block_size的整数倍。当某一条记录无法被完全放进一个block的时候，就会产生行链接，将一条记录的一部分存在block1里，另一部分存在block2里，并在block1里记录一个指向block2的指针，这样当数据被经常这样长期的蹂躏后，物理数据的存储就会变得非常乱，到处都是各种指针，导致数据库读取数据的效率变低。

【问题解决】

实施人员应该养成定期生成新的数据库统计信息的习惯，最简单的方法是重新生成所有表的统计信息，用“analyze table xxx compute statistics;”这个语法，注意对于大表来说这个语句的执行会较慢，因此应该在夜深人静的时候执行。

实施人员应该经常关注数据库的碎片情况（行链接和行迁移），以便及时优化数据库，检查碎片情况可以用下面的语句：analyze table xxx list chained rows into chained_rows。注意“chained_rows”是一张物理表，默认是不生成的，可以用这个脚本来生成：$ORACLE_HOME/rdbms/admin/utlchain.sql。

解决的方法如下：

1、  定期重新生成统计信息：

1)         select ‘analyze table ‘||owner||’.’||table_name||’ validate structure ;’ from dba_tables where owner=’XXXX’ ;

2)         select ‘analyze index ‘||owner||’.’||index_name||’ compute statistics;’ from dba_tables where owner=’XXXX’;

3)         将上述语句的查询结果放到sqlplus里执行一遍，当然最好的方式是用oracle的批命令来做，怎么用批命令自己上网搜去，下面提供一个例子：

分析所有表.txt

SET HEADING OFF

SET FEEDBACK OFF



select 'analyze table bi_fbk."'||table_name||'" delete statistics;' from dba_tables where owner='BI_fbk'



SPOOL C:\1.SQL

/

SPOOL OFF



SET FEEDBACK ON



start C:\1.SQL



SET HEADING ON


然后在sqlplus中调用上面的文本：“sql > @分析所有表.txt”

2、  清除行链接和行迁移，这有几种方法：

1)         用exp/imp的方式把表（可以单表操作，也可以全库操作）重新导一遍，则会重新生成新的数据存储；

2)         先将原表的数据移到备份表，再移回来即可：

1.         create table 备份表 as select * from 原表；

2.         truncate table 原表；

3.         insert into 原表 select * from 备份表；

4.         drop table 备份表。

3)         建一个备份表空间，将事实表从源表空间移到备份表空间，再从备份表空间移回原表空间，步骤如下：

1.         Alter table xxx move tablespace 备份表空间;

2.         Alter table xxx move tablespace 原表空间;

3.         重建该表的所有索引（否则表无法正常访问）：

Alter index xxx rebuild;