Software Testing and Quality Management

发布新日志

  • 常用Oracle数据库调优工具介绍[转]

    2007-07-14 10:08:27

    1.   数据库级调优

    1.1. 修改数据库参数

    以sys用户登录，运行如下的命令:

    alter system set optimizer_index_cost_adj=10 scope=spfile
    alter system set optimizer_dynamic_sampling=5 scope=spfile

     

    optimizer_index_cost_adj取值0-1000 ，缺省100 ; 值越小oracle就越趋向使用索引

    optimizer_dynamic_sampling    配置oracle对没有统计信息的表会动态采样，会得到更准确的统计信息，同时让优化器得到更准确的执行计划。这个参数对临时表尤其有用。

    1.2. 分析表

    应该把所有没有做过分析的表都做一下分析：

    执行如下语句的返回结果。

    select

    'analyze table '||owner||'.'||table_name||' compute statistics; '

    from dba_tables

    where num_rows is null

    and owner not in ('SYS','SYSTEM')

    and table_name not like '%$%'

    and temporary = 'N'

     

    如果针对SCHEMA里的所有表做分析,以JZ21为例

    begin

    dbms_stats.gather_schema_stats ('JZ21');

    end;

    /

    1.3. 增加数据维护的job

    1.3.1.    将表的监视打开

     select ‘alter table ‘||owner||’.’||table_name||’ monitoring; ’  

    from dba_tables

    where owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’) 

    and table_name not like '%$%'

    and TEMPORARY=’N’;

     

      这个监视是在内存中做的，一般情况下对性能不会有什么影响，如果发现实在有影响的时候，

     

    下面语句关闭监视，

    select ‘alter table ‘||owner||’.’||table_name||’ nomonitoring; ’  

    from dba_tables

    where owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’) 

    and table_name not like '%$%'

    and TEMPORARY=’N’;

     

    1.3.2.    添加JOB

    用sys用户登录

    执行如下语句，mynextdate,mynextSatdate需要手工更改为下一日和下一个周六。

     

      define mynextdate='2004-03-03';  --to_char(sysdate + 1,'yyyy-mm-dd');

      define mynextSatdate='2004-03-06'; --to_char(NEXT_DAY(sysdate,7),'yyyy-mm-dd');

     

      --每天早上3点左右执行flush monitor info

      call sys.dbms_job.remove(101);

      call sys.dbms_job.isubmit( 101,

                           'sys.DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();',

                           to_date('&mynextdate 03:00:00','YYYY-MM-DD HH24:MI:SS'),

                           'to_date(''&mynextdate 03:00:00'',''YYYY-MM-DD HH24:MI:SS'') + 1');

      commit;                      

      --统计数据库信息

      call sys.dbms_job.remove(102);

      call sys.dbms_job.isubmit( 102,

                           'sys.dbms_stats.gather_database_stats (options => ''GATHER STALE'');',

                           to_date('&mynextdate 03:10:00','YYYY-MM-DD HH24:MI:SS'),

                           'to_date(''&mynextdate 03:10:00'',''YYYY-MM-DD HH24:MI:SS'') + 1');

      commit;                       

     

      --每周六下午6:30把执行对那些被更新特别频繁的表的索引做rebuild

     

      call sys.dbms_job.remove(201);

      call sys.dbms_job.isubmit( 201,

                           'execute immediate ''alter INDEX history.PK_INCREMENT_STOCK_BALANCE_HIS rebuild nologging'';'||

                           'execute immediate ''alter INDEX history.ind_inc_stock_balance_1 rebuild nologging'';'||

                           'execute immediate ''alter INDEX history.ind_inc_stock_balance_2 rebuild nologging'';'||

                           'execute immediate ''alter INDEX history.PK_TB_INCREMENT_BALANCE_HIS rebuild nologging'';'||

                           'execute immediate ''alter INDEX history.Ind_inc_balance rebuild nologging'';',

                           to_date('&mynextdate 18:30:00','YYYY-MM-DD HH24:MI:SS'),

                           'to_date(''&mynextdate 18:30:00'',''YYYY-MM-DD HH24:MI:SS'') + 7');

      commit;  

     

    1.4. 删除重复的索引

    查找重复的索引

    select  *

        from

    ( select    index_owner,

    index_name,

    table_owner,

    table_name,

                 max(decode(p, 1,     c,NULL)) ||

                 max(decode(p, 2,', '||c,NULL)) ||

                 max(decode(p, 3,', '||c,NULL)) ||

                 max(decode(p, 4,', '||c,NULL)) ||

                 max(decode(p, 5,', '||c,NULL)) ||

                 max(decode(p, 6,', '||c,NULL)) ||

                max(decode(p, 7,', '||c,NULL)) ||

                max(decode(p, 8,', '||c,NULL)) ||

                max(decode(p, 9,', '||c,NULL)) ||

                max(decode(p,10,', '||c,NULL)) ||

                max(decode(p,11,', '||c,NULL)) ||

                max(decode(p,12,', '||c,NULL)) ||

                max(decode(p,13,', '||c,NULL)) ||

                max(decode(p,14,', '||c,NULL)) ||

                max(decode(p,15,', '||c,NULL)) ||

                max(decode(p,16,', '||c,NULL)) index_cols

           from (select index_owner,index_name,table_owner,table_name, substr(column_name,1,30) c, column_position p

                      from dba_ind_columns )

          group by index_owner,index_name,table_owner,table_name  ) A,

       ( select index_owner,

    index_name,

    table_owner,

    table_name,

                 max(decode(p, 1,     c,NULL)) ||

                max(decode(p, 2,', '||c,NULL)) ||

                max(decode(p, 3,', '||c,NULL)) ||

                max(decode(p, 4,', '||c,NULL)) ||

                max(decode(p, 5,', '||c,NULL)) ||

                max(decode(p, 6,', '||c,NULL)) ||

                max(decode(p, 7,', '||c,NULL)) ||

                max(decode(p, 8,', '||c,NULL)) ||

                max(decode(p, 9,', '||c,NULL)) ||

                max(decode(p,10,', '||c,NULL)) ||

                max(decode(p,11,', '||c,NULL)) ||

                max(decode(p,12,', '||c,NULL)) ||

                max(decode(p,13,', '||c,NULL)) ||

                max(decode(p,14,', '||c,NULL)) ||

                max(decode(p,15,', '||c,NULL)) ||

                max(decode(p,16,', '||c,NULL)) index_cols

           from (select index_owner,index_name,table_owner,table_name, substr(column_name,1,30) c, column_position p

                      from dba_ind_columns )

          group by index_owner,index_name,table_owner,table_name  ) B

       where a.table_owner=b.table_owner

    and a.table_name=b.table_name

    and (a.index_owner<>b.index_owner

    or a.index_name <> b.index_name )

    and a.index_cols like b.index_cols || '%'

    and a.owner not in (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’) 

    and a.table_name not like '%$%'

     

    出来的结果中，排在后面的索引是可以删除的。

     

     

    1.5. 做为一项常规任务，每个月做一次停下应用的维护

    检查哪些表碎片比较多，对这些碎片很多的表做move，同时将失效的索引全部重建。

    这一项任务是在已经对表都做了分析的情况下来做。

     

    做scrīpt0，对表全部做分析

    begin

    dbms_stats.gather_schema_stats (‘JZ21’);

    end;

    /

     

    针对所有的schema都做一次。

     

    --查看哪些表的空间碎片比较大，目前是认为大于30%就做整理。

    select * from (

    Select owner,table_name,(num_rows*avg_row_len/8000),blocks,

    (num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb

    from dba_tables where owner in (‘JZ21’) 

    and TEMPORARY=’N’

    ) tt

    where tt.bb<0.7 and blocks>10;

     

    --生成整理脚本scrīpt1

    select ‘alter table ‘||owner||’.’||table_name||’ move; ’ from (

    Select owner,table_name,(num_rows*avg_row_len/8000),blocks,

    (num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb

    from dba_tables where owner in (‘JZ21’) 

    and TEMPORARY=’N’

    ) tt

    where tt.bb<0.7 and blocks>10;

     

    --预备好分析脚本，不过这个脚本应该最后做scrīpt3

    select 'exec dbms_stats.gather_table_stats( '''||owner||''','''||table_name||''' ,method_opt => ''for all columns'', cascade => true ); ' from (

    Select owner,table_name,(num_rows*avg_row_len/8000),blocks,

    (num_rows*avg_row_len/8000)/( decode(blocks,0,1,null,1,blocks)) bb

    from dba_tables where owner in (‘JZ21’) 

    and TEMPORARY='N'

    ) tt

    where tt.bb<0.7 and blocks>10;

     

     

    --生成重建索引脚本scrīpt2

    select ‘alter index ‘||owner||’.’||index_name||’ rebuild nologging;’ from dba_indexes where owner in (‘JZ21’)  and status='UNUSABLE' ;

     

    步骤：先执行scrīpt0

    结束后，生成scrīpt1,scrīpt2,scrīpt3

    按顺序执行，scritp1,scrīpt2,scrīpt3。

     

     

    1.6. 特殊情况下对数据库重新整理

    1.6.1.    对表做move

    alter table test_move move ;

     

      select ‘alter table ‘||owner||’.’||table_name||’ move; ’  from dba_tables where owner in (‘JZ21’)  and TEMPORARY=’N’;

     

    这里用需要做move的schema替换。

     

    1.6.2.    对index做rebuild

    alter INDEX idx_test_move rebuild nologging;

     

    select ‘alter index ‘||owner||’.’||index_name||’ rebuild nologging;’ from dba_indexes where owner in (‘JZ21’);

     

     

    1.6.3.    对所有的表做分析

     

    begin

    dbms_stats.gather_schema_stats (‘JZ21’);

    end;

    /

     

    针对所有的schema都做一次。

     

    1.7. 对索引打开监视开关，看哪些索引没有被使用(慎用)

    select ‘alter index ‘||owner||’.’||index_name||’ monitoring usage;’ from dba_indexes

    where owner not in (‘SYS’,’SYSTEM’);

     

    在运行了一个很完整的周期以后(对我们来说，例如2个完整的交易日)，检查有哪些索引没有被使用

     

    select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage

    where used='NO';

    如果这些索引不是主键或唯一索引，一般情况下这些索引可以去掉。

     

    检查完一次以后，将索引监视关闭，不需要老是监视索引的使用：

    select ‘alter index ‘||owner||’.’||index_name||’ nomonitoring usage;’ from dba_indexes where owner in (‘WHT’);

    2.   语句调优

    2.1. PL/SQL中的语法解释工具

    语句的执行效率是否符合需要，一个是简单的把语句提取出来直接执行一下，看效果如何，但很多情况下，语句的运行是根据实际的条件运行的，所以有时候我们写完语句有要先分析一下语句是否是最优的，这时候我们就可以用PL/SQL的explain窗口（或直接选种语句后，按F5），有时侯，我们已知语句比较慢，要寻找为什么会慢，也可以如此做。

    在EXPLAIN窗口，我们需要关注几个重要的东西，COST（成本），如果这个COST数字很大，说明语句效率不高，需要想办法；有无TABLE ACCESS FULL，如果有，只要数据量大一些，效率就不会高。我们要尽量避免产生table access full,方法之一就是建索引，另一个方法是改写连接方式，使能够用到索引。另一个需要关注的是join方式，nest loops outer 和hash join outer，这两种方式的效果是不一样的，有时有天壤之别，目前我没有类似的经验可以写，以后碰到后补充。

    2.2. ORACLE提供的调优工具

    ORACLE 本身提供了很好的客户端的性能调试和跟踪工具。ORACLE的工具非常好，可以帮助我们很容易的找到系统性能的瓶颈语句在哪里。ORACLE性能调整工具的位置在：

    开始—程序—Oracle – Orahome92 – Enterprise Management Packs –Diagnostics –Performance Manager，登陆后找到数据库栏，然后连接就可以看到。

    我常用其中的两项来调整性能（其他的也很有用处）：一，顶层会话；二，SQL ，分别做介绍。

    顶层会话：

    查看(1068) 评论(0) 收藏 分享 管理