95-最近几个oracle数据库优化项目的经验总结

最近完成了几个比较大型的oracle数据库的优化项目, 发现一些共性问题, 写出来供大家参考.

这些项目都是远程完成的, 远程收集信息(用ora工具), 远程分析, 最后腾讯会议汇报(同时也相当于一次实战培训).

其中的一个项目, 我没敢接. 这也是到目前为止唯一一个拒绝的项目. 为什么?

现场工程师把采集到的一些性能信息发给了我, 虽然系统运行在oracle的一体机exadata上(较早版本), 但SQL的执行效率还是非常不理想. 几百G的大表, 也做了分区, 但大部分没有使用分区字段上的条件, 仍然需要全表扫描;

客户的期望是能够通过一通操作, 很快能看到明显的优化效果, 这实在是做不到. 这个系统, 我能提出一些优化建议, 但是因为设计上的原因, 需要做很多大的改动, 短期想看到效果是不可能的. 涉及到需要开发商的配合和较大的改动, 这个时间就没法控制了.

给DBA的一些建议:

很多时候, 只有系统hang才会引起dba的关注, 而系统hang大部分都是性能问题导致, DBA可能会重启数据库来临时解决, 但是如果不能从根本上解决性能问题, 这种情况还会频繁出现, 而且频率会越来越高.

1. 参数不要乱改 很多DBA百度到了一些"专家"建议, 希望通过修改参数达到优化的目的, 实际可能起到相反的作用, 下面列举的是某些"专家"建议修改的一些参数:

_optimizer_cost_based_transformation

_optimizer_null_aware_antijoin

_optimizer_mjc_enabled

_optimizer_unnest_corr_set_subq

_optimizer_squ_bottomup

_optim_peek_user_binds

optimizer_index_caching

optimizer_index_cost_adj

......

我的参数设置原则是:

保持大部分参数为默认值, 对于存在bug的情况, 影响的是极少数特殊的SQL, 可以在sql级别单独处理 (hint或sql_patch); 只有对那些影响面比较大的参数, 才在system级别做全局调整.

(很多开发人员也期望有神奇的参数可以达到优化的效果, 可惜真没有)

2. 关于统计信息收集

不知道是出于什么原因, 有一部分数据库的自动收集统计信息被关闭.

oracle为收集统计信息做了自动任务, 这个自动任务建议是要开启的, 否则就有可能不定时的出现SQL执行计划变差导致的性能问题.

统计信息是优化器为sql生成正确执行计划的最重要依据, 统计信息不准确, 生成的执行计划就非常可能不是最优.

有些资深dba用自己的脚本收集, 不太建议; dba可以结合业务特点, 在默认收集任务的基础上做微调, 比如调整时间窗口, 与业务错开; 分区表增量收集;大表并行收集; 并发收集; 调整默认stale比例(默认10%); 调整采样比例; lock 归档历史表统计信息; 大分区表copy/set统计信息等方法.

关于手动收集统计信息:

遇到执行计划变差, 手动收集统计信息的几个重要的知识点(包括开发人员在程序代码里面调用收集统计信息命令):

使用dbms_stats.gather_table_stats命令,不要使用analyze table 收集统计信息, 因为:

analyze命令是oracle很早之前没有dbms_stats命令的时候用的,也能收集统计信息(有了dbms_stats后, analyze主要只用来做validate structure 和list chained rows), 命令写起来比较简单,但是有比较大的缺陷;

可能因为mysql数据库用的就是这个命令收集表的统计信息,也会误导一部分人在oracle数据库上也用这个命令.

dbms_stats.gather_table_stats和analyze table 具体有什么区别, 你可以自己动手做个比较, 这样会印象深刻.

还有人发现用dbms_stats.gather_table_stats收集完统计信息后, 执行计划没有马上改变. 这是因为没有使用 no_invalidate=>false参数. 这个参数, 只有那些经验丰富的dba才知道.

最后给DBA的一点补充建议:

给开发人员合理的采集性能信息权限, 比如生成awr , 查看数据字典等.

给oracle数据库开发设计人员的几个建议:

数据库性能问题, 90%以上(我个人的经验值)是开发设计上的问题, 下面列举几个常见的:

1. 基本上绝大部分业务端没有做负载控制(并发量控制) 遇到sql执行时间长, 很多active session长时间不释放, 新的业务请求仍不断增加, 系统不堪重负, 就hang了.

需要中间件起到一个闸门的作用, 不要以为数据库有求必应, 数据库的负载是有限度的, 超出极限负荷就会出问题. 在active session达到一定的阀值后, 就不要再增加新的请求. (有时虽然应用端没有新的请求,但是数据库本身的定时任务也是没办法避免的)

2. 没有合理使用绑定变量

导致大量的SQL硬解析, 95%以上的开发人员不知道什么是绑定变量的写法; 这个百度一下, 很简单, 只是知道和不知道的关系.

如果一开始没有这个概念,等系统上线前测试或者已经上线了, 再发现问题,那改动起来就比较难了.

对于mysql/pg等其他数据库, 不使用绑定变量, 问题可能还没有那么严重, 但是对于oracle, 会带来很多问题,比如并发量上不去, ora-4031, 系统越来越慢(buffer cache被shared pool侵占), top sql不容易被发现等问题.

3. 使用模糊查询作为大表的主要过滤条件

如 instr(col_name,'xxx')>0 或者 col_name like '%xxxxx%', 这种一般需要全表扫描, 随着记录数的增加还会越来越慢; 虽然可以通过改写, 使用index fast full scan,也是提升有限; 最好还是调整业务需求, 能改成 like 'xxxxx%' 为最佳(去掉一个百分号,去掉前面那个最好, 去掉后面那个也行, 就是需要做一些特殊处理).

4. sql写法与业务逻辑的实现方法:

大结果集的分页查询,还有用分页查询的逻辑做数据导出 , 都是不建议的. 分页就尽量把结果集缩小; 导出就一次性导出;

大结果集使用标量子查询 ,执行时间会很长, 而且并行也帮不上忙. 有的需要改成外关联, 有的可能要借助物化视图的快速刷新(比如一些报表);

......

5.plsql结合上下文的写法优化:

如果不看plsql代码的上下文, 单个sql是没有问题的, 下面举几个例子:

5.1 两步可以合并为一步, 这个比较简单

原写法: delete + insert两步:

delete from xxxxxx rp

where rp.resource_id = :b1 and rp.entitytype_id = :b2;

insert into xxxxxx (georegion_id, entitytype_id, resource_id)

values (:b3, :b2, :b1);

改进写法, 直接一步update(表中其他字段置为null):

update xxxxxx set georegion_id=:b3

, other_col1=null,other_col2=null,....

where entitytype_id=:b2 and resource_id= :b1;

5.2 两步可以合并为一步, 也比较简单

原写法, 先insert , 再update:

insert into temp_xxx (aaa, bbb, ... memo)

select * From ext_xxx;

update temp_xxx na set na.aaa = 29010 where na.bbb = '111';

update temp_xxx na set na.aaa = 29011 where na.bbb = '222';

update temp_xxx na set na.aaa = 29012 where na.bbb = '333';

改进写法, 一次insert即可, 不需要update:

insert into temp_xxx (aaa, bbb, ... memo)

select decode(bbb,'111',29010,'222',29011,'333',29012,aaa) ,bbb, .....,memo From ext_xxx;

5.3 一个存储过程调用一个函数实现的逻辑, 其实可以用一个简单SQL就能实现:

--函数(sql存在没有使用绑定变量的情况, 可以增加一个rownum=1的条件):

create function func_getcnt(p_fids clob) return number is

v_count number;

v_sql varchar2(3000);

begin

v_sql := 'select count(distinct id)

from tf

where id in (' || p_fids || ')';

execute immediate v_sql

into v_count;

if v_count > 0 then

return 1;

else

return 0;

end if;

end func_getcnt;

/

--存储过程, 调用上面的函数, 计算出两个值:

create procedure proc_getjituan( p_out1 out number, p_out2 out number)

is

v_count1 number := 0;

begin

select count(distinct pid) into p_out1 from tp;

for x in (select pid,wm_concat(fid) fids from tp group by pid) loop

if func_getcnt(x.fids) = 1 then

v_count1 := v_count1 + 1;

end if;

end loop;

p_out2 := v_count1;

end;

/

SQL怎么写, 你可以动手试试, 也不难.

注: 上面几个SQL都是从客户的业务代码中脱敏简化而来.

以上观点仅为个人看法, 如有不当欢迎批评指正.

(全文完)