数据库调优的三板斧[已结贴]

kelsoncong

性能调优是每个DBA职业生涯中都能遇到的任务，大到世界五百强的核心系统，小到乡镇企业的进销存，几乎都会有要调优的时候。面对形形色色的系统，林林总总的需求，调优的手段也是丰富多彩。定位问题，分析问题，解决问题环环相扣，缺一不可，且又手段多样，效果也各有千秋。

今天的每周一议就是和大家讨论下:
面对调优需求，你最常用的套路是啥，有没有什么大招，如果能结合相应的案例就更加的好了。

非常感谢大家的积极参与，下面我来做一个总结陈词：
性能调优是每个DBA职业生涯必定会遇到的任务只一，数据库只要在运转，都会有性能表现，好的无所谓，差的就成为性能问题了，性能问题原因很多，常见的有：
1 无意义的需求，兔子的意见砍砍砍，主要是针对这个的
2 不合理的设计，下面几乎一般以上的问题都是针对这个的
3 不合理的配置
4 贫乏的硬件资源
5 长期缺少必要的维护

性能调优的工作有人形容为是一个艺术的创作，这个主要是由于这项工作和实际需求和工作环境密切相关，没有最好的只有最合适的，怎么样帮自己的系统找到最合适的配置和设计，是调优工作的灵魂。

调优的工作是分阶段的
第一阶段，需求确定期
性能调整效果最好的可能是剔除不合理的需求和修改不合理的设计，这样可以有效的减轻数据库负载，极大的提升性能。
第二阶段，项目设计期
在需求问题解决好以后，一些底层设计上面的考虑对性能影响也非常巨大，这也是为什么大型项目都要求DBA的前期参与设计，DBA可以通过自身的知识储备，选择对系统而言最合适的技术，比如我们下面讨论提到的分区表，压缩表，物化视图，临时表用，索引设计，并发设计等等，这样可以保证系统在前期就能够有好的设计和规划。
第三阶段，开发期
这一阶段主要设计SQL语句的使用，下面提到的一些SQL相关的招数，都能够在这一阶段使用，DBA要利用自己对SQL的深入理解帮助开发人员写出高效合理的SQL。
第四阶段，上线后维护
系统上线以后，进行大规模的调整已经变的不现实了，只能进行一些局部的微调，主要就是定位问题，分析问题和解决问题。每步都有相应的手段，
比如定位问题，可以采用 AWR,警告日志，10046 trace等等。分析问题可以利用执行计划，而解决问题，则牵涉到一部分SQL的调整，系统参数的调整，索引的维护，Hint的使用等等。
很多DBA遇到的调优问题，可能是停留在这个阶段的，但是郁闷的是，这个阶段对系统性能的影响往往是最轻微的，效果也是最不明显的。

除了调优以外，这个帖子的另外一个收获是由福哥引出的关于压缩表技术的细节讨论，对压缩表有兴趣的兄弟可以看下或者参与讨论。
另外非常感谢Vage提供的由MySQL性能问题引发Oracle性能问题的一个精彩案例，这个帮助大家在调优的时候开拓了思路。
下面是我挑选的大家的精彩发言，一个有趣的现象是，招数最多的一个兄弟finalarrow是从事开发而非DBA工作的，这也印证了好的性能是设计出来的而不是调出来的。

hwtong

找性能问题,,根据客户反映或者AWR报告找到性能较差的sql
分析性能问题,调试SQL,检查执行计划,查看统计信息
解决性能问题,表分区,加hint,索引等

〇〇

在awr报告里找top sql

g644516804

我们procedure里面很多temptable 然后再将个temp table的数据结合起来

dingjun123

砍砍砍 没有必要做的动作，坚决不做

lorikyo

加索引，加hint,收集统计信息

koham

1.查看执行计划；
2. 收集统计信息
3. 打补丁

Lyoohan

大而化小:
比如说需要全国数据,但是分省查再进行汇总的话可以大幅减轻服务器负担
另外对一些大数据尽量走索引很多时候走索引效率确实很高

湖湘文化

收集信息，包括操作系统参数配置、资源使用情况、AWR报告、告警日志等
初步分析一下，哪里有问题再进一步具体问题具体分析

福哥(直接复制你的账号，系统提示有不良信息，不能保存，咋回事？)

1分区，并定时迁移走历史业务数据，用好11G新出的那几类分区，几乎无往不利。
2 除了采集表的统计信息，还要采集 system, fix_table,dict 等统计信息，

3 10046，跟踪整个语句的执行过程，哪里耗时调哪里。 这招最管用。

lei

仓库系统里，基本原则就是拆分，数据结构上化整为零，然后就可以各种并行快速处理（加载转换都可以），这个思路与分布式计算也是类似的。
parallel确实很强大，但得IO够强大，楼主那个情况，不只是48core那么简单，估计存储IO也不弱。
在10g的仓库系统里，要小心误走NestLoop的执行计划，吃了很多苦头，无奈只能hint抑制。

finalarrow

1.看session，找出SQL，观察执行信息
2. 看分析计划
3. 看表分析情况，获取之前的正常运行信息，联系开发人员分析瓶颈，有条件就分析一次再运行
4. 如果有update动作，当数据量非常大时，采用分批 update 并批量提交，尽早释放资源
5. 复杂的SQL必要时可以拆分；分开执行的SQL根据业务规则可以适当合并（非DBA干的活了）
6. 因为是数据仓库，可以适当运用bitmap index
7. 注意partition表的分析计划，因为没数据时是空的，容易导致插入数据后因为没有作analyze导致执行计划出错
8. 根据需要采用temp table（又是开发人员的活了）
9. 数据仓库有时采用分析函数会大大加快性能
10. 先从架构上去调整，把一些在OLTP上不必要运算改在OLAP中进行，避免OLTP在交易高峰日的处理风险
11. 采用 Partition & truncate (注意空白Partition表分析)
12. 有些逻辑能合并的就合并，不适合合并的就拆分；另外，业务处理根据实际情况上调整一下顺序(我本人就是其中开发人员之一)
13. 采用temp table(temp table本身在处理完就自动清除的)
14. 如果有update动作，当数据量非常大时，采用分批 update 并批量提交，尽早释放资源
15. 因为是数据仓库，可以适当运用bitmap index
16. Append + parallel(这一点效果不大，因为机器本身配置不高)
17. 前端ETL导出要尽可能采用增量方式
18. 采用预编译的更新或删除来处理动态SQL
19. 把SQL loader 改为exe/imp，减少前端导出的时间

20.用时间做partition, 再用省做subpartition,再用localbitmap index

孤独飞

如果某一session比较慢，一般会直接看此session的等待事件，找出问题的根本原因，然后解决。
如果系统在某段时间比较慢，会收集那段时间的AWR报告，分析Top 5events，逻辑读、物理读和时间模型，找出影响系统慢的主要问题，然后进行分析优化。
做优化N年，发现主要是两个问题：
1）SQL本身的执行计划出了问题：SQL过于复杂，缺少索引，或者是统计信息不准确，总之是SQL没有达到最优执行计划。
注：最优执行计划的确定，如果sql简单，直接人工分析确定，若是复杂，可以借助oracle 的sqladvisor
2）数据存取逻辑设计不合理，如楼主举的例子。

thain

分区、索引、并发、统计信息的收集
分析执行计划(分析前注意统计信息的收集)，改写SQL
避免使用游标,用truncate代替delete，中间表、临时表一定要nolog
可以的话开启paralleldml以支持并发写入

suredandan

olap可以尝试使用:物化视图,bit map等..

yangb71881

1.创建合理的索引是至关重要的...
2.保证统计信息的准确性...
3.围绕的想得的数据，对sql语句进行拆分...
4.对系统相关性能的调整及对相关sql语句的优化...

xgghxkhuang

物化视图

anlinew

RAC结合parttion，再加业务隔离

wanglipo

1，分析业务逻辑，尝试是否能从业务逻辑的角度进行优化
2，找出执行慢的SQL，查看执行计划，
3，针对执行计划进行SQL重写、添加索引等优化操作
4，尝试是否可以从数据库性能上进行一些优化

jieyancai

对于大数据量常用的招数：
1.分区表操作：分区交换，并行操作，本地分区索引
2.表压缩操作：插入后再做压缩，提升查询性能，节约空间。

vage

介绍了一个因为MySQL占用网络带宽导致Oracle性能问题的精彩案例。

gszoracle

数据仓库1：分布IO，2：用大块，3：根据业务排序数据提高聚合度，4：建合适的索引，适时采用位图索引，5：及时采集统计数据，6：开并发7：还有•••••

活动时间：
2012年6月25日—7月2日

活动奖励：
活动结束，将评选出一位分享最认真的会员赠与《oracle管理之道》一本。欢迎大家多多参与！

图书信息：


作者：张天慧
简介：本书以深入浅出的方法，引导读者快速进入Oracle知识领域，接着进行了理论与实际参考示例的阐述，既可以让初学者参考学习，又可以帮助具备一定Oracle数据库基础的DBA学习高级的知识，希望无论是数据库管理员还是程序开发人员都能从中获益。

kelsoncong

沙发自己来做，先说一下我最近做的一次调优，算是丢块砖头：

话说还在乙方混的时候，接到上级指示，给一个报表系统做性能调整，这个报表系统会每天从业务系统里面接受数据，聚合整理以后给报表提供数据。业务系统会在每天的8点把原始数据推送过来，我们的系统一般在晚上10点左右开工，要求在早上8点以前把数据整理好，保证用户早上8点以后就能查阅报表。
系统刚上线的时候，由于数据量小，运行的还比较正常，一般2-3个小时就能运行结束了，但是系统使用了几个月，数据慢慢变多了以后，性能问题就出现了，数据抽取过程的时间越来越长，客户也是非常的好说话，他们看报表的时间也从早上的8点推迟到中午，然后推迟到下午，知道最后推迟到下班的时候看。直到到了下班的时候都看不到，并且和第二天的业务系统数据推送冲突了，造成了数据冲突，因此要求我们公司来处理性能问题。
接到任务后，立刻开展工作，先说下最后的成果，经过一系列的调整，整个数据抽取过程从原先的二十多个小时，缩减到一个小时以内，效果还是蛮显著的，下面会分别讲讲整个过程中，我所使用的三板斧：

zhuxianglei

坐等三板斧

ward

关注一下，顶

kongo

怎么还没有三板斧？

yangb71881

等待三板斧到来

kelsoncong

首先看下现行的系统，整个数据抽取是用存储过程实现的，业务逻辑也非常的简单，基本就是把原始数据按照业务发生时间做周-》月-》季度-》年的分层聚合，然后和多维数据集连接，获取结果后扔到最后的报表层所需要的表里面去，整个逻辑还是很简单的。唯一需要注意的是，业务表中提交过来的数据是有可能变化的，因此需要把现有的记录清除掉，然后重新抽取。
所以大多数的语句类似于
delete from ta where week='xxx';
insert into ta select * from tranTa where week='xxx';
我看了下数据库结构，所有的表都采用了传统的堆表。 有经验的兄弟看到这边大概就知道猜到我第一步要做什么了。
对，就是分区表，这样的一个系统，每次应用的时候几乎对某个时间周期内的数据做聚合，因此用索引效果是不明显的，采用时间分区表，有效的限制了FTS的范围，对这个环境而言可能是最最合适的了，

考虑清楚以后，立刻动手，把所有的相关表改成以周为单位的时间分区表，语句也可以修改成
Alter table ta truncate partition wk_xxx;
insert into ta select * from tranTa where week='xxx';

用truncate代替delete，也能有效的节省很多时间，且所有的数据在周分区里面，为后面的数据抽取打下了很好的基础，所有的全表扫描变成了分区扫描，性能提升不少。

未完待续....

tangyong327

顶起楼主的三板斧。

presidentccj

顶起

kelsoncong

继续：

这个过程中还有一个小插曲，由于之前的项目不是一个开发做的，有的开发对游标的使用情由独钟，居然在这种类似于数据仓库系统的环境下使用游标做类似于ETL工具里面的router操作。
整个脚本类似于
declare cursor as select * from 原始大表 where week=xxx
循环
判断目标表中是否存在同id记录
存在就 用原始表update目标表，
不存在就 insert
循环结束

这个操作是一个经典的模拟router的操作，实现的脚本也没问题了，但是router的使用是有着非常特定的场景的，放在现在这样的一个类似于数据仓库的环境里面，就显得非常的不动脑子，很教条了，

全部被我粗暴的换成了之前描述的
alter table ta truncate partition wk_xxx;
insert into ta select * from 原始大表 where week=xxx；

这样一来，时间又缩短了不少。

未完待续。。。。