最近完成了几个比较大型的oracle数据库的优化项目, 发现一些共性问题, 写出来供大家参考.
这些项目都是远程完成的, 远程收集信息(用ora工具), 远程分析, 最后腾讯会议汇报(同时也相当于一次实战培训).
其中的一个项目, 我没敢接. 这也是到目前为止唯一一个拒绝的项目. 为什么?
现场工程师把采集到的一些性能信息发给了我, 虽然系统运行在oracle的一体机exadata上(较早版本), 但SQL的执行效率还是非常不理想. 几百G的大表, 也做了分区, 但大部分没有使用分区字段上的条件, 仍然需要全表扫描;
客户的期望是能够通过一通操作, 很快能看到明显的优化效果, 这实在是做不到. 这个系统, 我能提出一些优化建议, 但是因为设计上的原因, 需要做很多大的改动, 短期想看到效果是不可能的. 涉及到需要开发商的配合和较大的改动, 这个时间就没法控制了.
给DBA的一些建议:
很多时候, 只有系统hang才会引起dba的关注, 而系统hang大部分都是性能问题导致, DBA可能会重启数据库来临时解决, 但是如果不能从根本上解决性能问题, 这种情况还会频繁出现, 而且频率会越来越高.
- 参数不要乱改 很多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%以上(我个人的经验值)是开发设计上的问题, 下面列举几个常见的:
- 基本上绝大部分业务端没有做负载控制(并发量控制) 遇到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都是从客户的业务代码中脱敏简化而来.
以上观点仅为个人看法, 如有不当欢迎批评指正.
(全文完)
