几个比较大型的数据库优化项目,发现一些共性问题

最近完成了几个比较大的数据库优化项目，发现了一些常见的问题，写出来供大家参考。

这些项目都是远程完成的，远程收集信息（使用ora工具），远程分析，最后在腾讯会议上汇报（也相当于实训课）。

其中一个项目，我不敢接受。这也是迄今为止唯一被拒绝的项目。为什么？

现场工程师给我发了一些收集到的性能信息。虽然系统运行在一体机（较早的版本）上，但SQL的执行效率还是很不理想。还制作了数百 GB 的大型表。分区，但大多数不使用分区字段上的条件，仍然需要全表扫描；

客户的期望是通过一次操作就能快速看到明显的优化效果，这实在是不可能的。我可以对这个系统提出一些优化建议，但是由于设计原因，需要做很多。短期内无法看到效果。涉及到开发者的配合和大的改动，这个时间是无法控制的。

给 DBA 的一些建议：

在很多情况下，只有系统挂起会引起dba的注意，而大部分系统挂起是由性能问题引起的。 DBA可能会重启数据库来暂时解决问题，但如果性能问题不能从根本上解决，这种情况还是会经常出现，而且频率会越来越高。

不要乱改参数

很多百度的DBA都来过一些“专家”的建议，希望通过修改参数来达到优化的目的，其实可能适得其反。以下是一些“专家”建议修改的参数：

编辑

mup

ds

_adj

......

我的参数设置原则是：

将大部分参数保留为默认值。对于bug，影响的特殊SQL很少，可以在SQL级别单独处理（提示或）；只有影响比较大的参数才在级别上进行全局调整。

（很多开发者也期望有神奇的参数来达到优化效果，可惜没有）

2.关于统计收集

由于某些原因，某些数据库的自动统计信息收集已关闭。

会自动执行一项任务来收集统计信息。建议开启这个自动任务，否则可能会时不时的因为SQL执行计划的恶化而导致性能问题。

统计信息是优化器为SQL生成正确执行计划的最重要依据。如果统计信息不准确，那么生成的执行计划很可能是次优的。

一些高级DBA使用自己的脚本进行采集，不推荐； DBA可以根据业务特点，在默认采集任务的基础上进行微调，如调整时间窗口，与业务错开；分区表的增量收集；并行大表集合；并发收集；调整默认陈旧率（默认10%）；调整采样率；锁定归档历史表统计；大分区表复制/设置统计等方法。

关于手动收集统计数据：

执行计划变差时手动统计的几个重要知识点（包括开发者在程序代码中调用命令统计）：

使用.，不要使用table来收集统计数据，因为：

该命令在很久以前没有命令的时候使用过，还可以收集统计信息（有了之后主要用来做和列出行），命令写起来比较简单，但是有比较大的缺陷；

p>

可能因为mysql数据库使用这个命令来收集表的统计信息，也会误导一些人在数据库上使用这个命令。

.table和table有什么区别，大家可以自己对比一下，印象深刻。

有些人觉得它很有用。统计后，执行计划并没有立即改变。这是因为没有使用 =>false 参数。这个参数，只有有经验的dba才知道。

最后给 DBA 一点额外建议：

给予开发者合理的权限来收集性能信息，例如生成awr、查看数据字典等

给数据库开发者的几点建议：

数据库性能问题，90%以上（我个人经验值）都是开发设计问题，这里有几个常见的：

基本上大部分业务端不做负载控制（并发控制）

sql执行时间长的时候，很多很久没有发布，新的业务请求还在增加，系统不堪重负，所以挂了。

中间件需要充当门。不要以为数据库可以响应所有请求。数据库的负载是有限的，超过限制负载就会出现问题。达到一定阈值后，不再添加新请求。 （有时候虽然应用端没有新的请求，但还是无法避免数据库本身的定时任务）

2.不合理使用绑定变量

这导致大量的硬SQL解析，超过95%的开发者不知道绑定变量的写法是什么；这个百度，很简单，只知道你不知道的关系。

如果一开始没有这个概念，如果在系统启动前或者已经启动之前测试过，然后发现问题，就更难改系统了。

对于mysql/pg等其他数据库，如果不使用绑定变量，问题可能没那么严重，但是对于其他数据库，会带来很多问题，比如并发不增加，ora-4031，系统越来越慢（缓存被池占用），不容易被发现。

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

如instt(,'xxx')>0之类的'%xxxxx%'，这个一般需要全表扫描，随着记录数的增加会越来越慢；虽然可以改写，但是使用索引快速全扫描在改进上也有限；最好调整一下业务需求oracle数据库优化方法，可以改成‘xxxxx%’之类的最好（去掉一个百分号，最好去掉第一个，最后一个也可以，但是需要做一些特殊处理）。

4.sql编写和业务逻辑实现：

不推荐对大型结果集进行分页查询，不推荐使用分页查询逻辑的数据导出。分页应该尽量减少结果集； 应该一次性导出；

大型结果集使用标量子查询，执行时间会很长，并行性无济于事。有的需要改成外部关联，有的可能需要借助物化视图（比如一些报表）来刷新；

......

5.plsql结合上下文写优化：

如果不看plsql代码的上下文，单条sql是没有问题的。以下是几个例子：

5.1 两步可以合并为一步，比较简单

原文拼写：+两步：

来自 rp

在哪里 rp。 = :b1 和 rp。 = :b2;

变成(,,,)

(:b3, :b2, :b1);

改进写法，直接一步（表中其他字段设置为null）：

设置 =:b3

, =null,=null,....

其中 =:b2 和 = :b1;

5.2 两个步骤可以合二为一，比较简单

原始拼写，先，然后：

进入（aaa, bbb, ... 备忘录）

* 来自 ;

na 设置 na.aaa = 29010 其中 na.bbb = '111';

na 设置 na.aaa = 29011 其中 na.bbb = '222';

na 设置 na.aaa = 29012 其中 na.bbb = '333';

改进写法，只需一次，不需要：

进入（aaa, bbb, ... 备忘录）

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

5.3 存储过程调用函数实现的逻辑其实可以用简单的SQL来实现：

--（在SQL不使用绑定变量的情况下，可以加一个=1的条件）：

(clob) 是

;

v_sql(3000);

开始

v_sql := 'count(id)

来自 tf

id in (' || || ')';

v_sql

进入;

如果 > 0 那么

1;

其他

0;

如果结束；

结束;

/

--存储过程，调用上述函数计算两个值：

（出，出）

是

:= 0;

开始

count(pid) into from tp;

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

如果 (x.fids) = 1 那么

:= + 1;

如果结束；

结束循环；

:= ;

结束；

/

SQL怎么写oracle数据库优化方法，你可以试试，不难。

注意：以上SQL都是从客户的业务代码中脱敏和简化的。

以上观点仅为个人观点，如有不当之处欢迎批评指正。

（全文）