• 博客访问: 446529
  • 博文数量: 193
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-18 15:56
个人简介

暂无介绍

文章分类
文章存档

2014年(1)

2013年(3)

2012年(59)

2011年(98)

2010年(32)

我的朋友

分类: Linux操作系统

2010-05-19 13:58:12

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

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 not null

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

and table_name not like '%$%'

and temporary = 'N'

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

begin

dbms_stats.gather_schema_stats ('SYMBOLS');

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('&mynextdate03:00:00','YYYY-MM-DD HH24:MI:SS'),

                      'to_date(''&mynextdate03: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('&mynextdate03:10:00','YYYY-MM-DD HH24:MI:SS'),

                      'to_date(''&mynextdate03: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('&mynextdate18:30:00','YYYY-MM-DD HH24:MI:SS'),

                      'to_date(''&mynextdate18: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’;

这里用需要做moveschema替换。

1.6.2.   indexrebuild

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/SQLexplain窗口(或直接选种语句后,按F5),有时侯,我们已知语句比较慢,要寻找为什么会慢,也可以如此做。

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

2.2.ORACLE提供的调优工具

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

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

 
阅读(1582) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册