ABAP程序效率优化系列之④——开发优化之数据库

ABAP程序效率优化系列文章历史

写在前面

1、读取数据库优化的这两篇(上一篇和这一篇)，对于HANA来说意义不大，一直在HANA数据库上进行开发的朋友请忽略

2、本文中的所有例子都是ORACLE数据库上的

3、大家在测试时，如果遇到与我不同的测试结果，也属于正常现象

4、我的目的是让大家通过ST05看到SQL的执行轨迹，了解它大概是怎么回事。然后通过HINTS的方式，在执行轨迹不符合我们的预期时，进行适当的干预

5、性能优化在运维项目上的作用尤其大，建设项目的数据量小，复杂度相对低，难以体会到性能的瓶颈。

初识ST05

首先，我们在一个REPORT程序中编写一段代码：

然后打开ST05

接下来按以下步骤操作：

1、Activate Trace

2、执行REPORT程序

3、Deactivate Trace

4、Display Trace，然后点执行，可以得到如下的界面

这个界面展示了SQL的执行轨迹：

第二列，是每一条SQL的执行时间；

对象名列，是对应的表或视图；

最后一列，是相应的SQL语句。

初识执行计划

选中上图中表AFPO OPEN操作的行，点击工具栏上的”Explain”按钮(F9)，可以得到SQL的执行计划。

其中1、2、3为SQL执行时的执行顺序。

步骤1中的AFPO~2，代表的是AFPO的索引2，该索引包括客户端和PROJN两个字段。

点击1下面的Access Predicates，可以看到

即，第一步访问AFPO表时，用的就是AFPO~2这个索引。

步骤2是根据步骤1得到的ROWID去AFPO获取数据，步骤3不用管它。

多个查询条件的执行计划

换一段代码

(在MSEG表中对BUDAT_MKPF新建了索引，索引名是Z05)

在我目前的项目上，这个SQL在开发机上执行的很快，到了生产机却执行的异常慢。通过ST05监测其执行轨迹，发现结果如下：

在开发机上，第一步是先根据budat_mkpfAccess，再根据werks和bwartfilter

在生产机上，第一步是先根据werks和bwartAccess，再根据budat_mkpffilter

要解决这个问题并不麻烦，只需要通过HINTS人工指定索引即可(上一期也提到了)，即：

%_HINTS ORACLE ‘INDEX(MSEG “MSEG~Z05”)’。

(第一个MSEG可以不加引号)

至于生产机为什么和开发机是不一样的SQL执行计划，这个我也不清楚。

据BASIS解释说，是ORACLE的统计分析的结果，导致了这样的SQL执行计划。

ORACLE认为它是最优的，但在执行时不一定总是最优的。

这时候就需要我们的人工干预，以快速解决这样的性能问题。

(一般情况下，ORACLE的自动优化都是没太大问题的，但我在这里想说的是，我们要掌握一个快速解决处理的办法，以备不时之需)

表关联的执行计划

代码如下

执行计划如下

可以看到，多个表关联时，在图片最上面的SQL STATEMENT中出现了T_00、T_01，它们是参与JOIN的表的别名。

因此，在表关联时，指定索引的方式与只查询一个表时略有不同，格式如下：

%_HINTS ORACLE ‘INDEX(T_00“MSEG~OIB”)’

进一步分析

在此例中，大家也可以看到：

第一步，执行的是MSEG~Z11的索引(自建的索引)，这个索引包含的内容是工厂和移动类型；

第二步，是按客户端过滤数据；

第三步，执行的是MKPF~0这个索引(~0是一个表的主键索引)，它包含的内容是MKPF的主键字段，因为MSEG和MKPF进行了关联；

第四步，是按MKPF的BUDAT过滤数据。

(这是我在我的开发系统的测试结果，大家也可以自己测一下这个代码，应该有很多人跟我是一样的测试结果)

我们可以想象，如果数据很多的话，我们是根据工厂和261做第一步更快，还是根据一个月的日期做第一步更快？肯定是后者啊！

那怎么办呢？按照上面的方式指定索引吗？加上代码

%_HINTS ORACLE 'INDEX("T_01" "MKPF~BUD")'

然后试试，结果如下：

第一步、第二步没有变化，第三步变了。

这并没有达到预期的希望先ACCESS表MKPF的效果。

那我们把MKPF和MSEG关联的顺序颠倒一下可以吗？你试过之后发现依然不行。

怎么办呢？ORACLE为什么不按我希望的顺序执行呢？

于是我开始翻资料，查找“ORACLE指定查询顺序”的关键字，没找到原因，却最终得到了以下的处理办法：

我把MKPF写在MSEG之前，并且指定HINTS ordered，使SQL执行计划按表出现的顺序执行。

接下来是它的执行计划分析：

OK！！这太棒了！！

这就是我们想要的结果了！！

案例与实际应用

下面这两个案例，都是我在运维项目上遇到的实际案例。

案例1

SQL语句如下

SELECT

afpo~projn

aufk~auart

afko~aufpl

prps~post1

FROMafpo

INNERJOINaufkONaufk~aufnr = afpo~aufnr

INNERJOINafkoONafko~aufnr = aufk~aufnr

INNERJOINprpsONprps~pspnr = afpo~projn

WHEREafpo~prpsIN(....).

IN的内容较小时，执行没有问题，但是超过100条时，ST05的执行轨迹如下：

第一步是PRPS的ACCESS

第四步是AUFK的ACCESSFULL(全表扫描)，非常耗时

第五步才是AFPO的Z03索引

(Z03是我这里PROJN的索引，系统中有标准的AFPO~2索引，我也不清楚为什么之前的顾问建了个多余的索引)

(大家如果写同样的程序，可能不会遇到跟我同样的问题)

这个SQL的执行速度超慢，大约5-10分钟。

我一个表一个表的手动查询，都用不了2分钟的啊。

分析一下，这个很明显就是ORACLE自动确定的执行顺序出了问题(至于怎么)，所以我就在后面加了ordered的hints，之后不到2秒钟，结果就出来了。

案例2

代码如下

其中GT_ALV中只有一条记录，根据其记录在PRPS可以找到18条数据，进而在AFVC中找到4条数据，最后根据AFVC的4条数据查询COSP的内容。

然而，它在生产系统的执行轨迹是这样的：

它按照顺序PRPS-AFVC-COSP的顺序执行，但是COSP时却意外的ACCESS FULL，看看吓人的CPU-Costs和IO-Costs吧，有没有要解决掉的冲动？

动手吧，因为它同样很简单，指定COSP的索引COSP~1即可(INDEX(T_02 "COSP~1"))，COSP~1的第一个字段就是OBJNR。

结果当然也是很快的，一瞬间，就查询出来了。

写在最后

利用index和ordered这两个hints，可能还是无法完全解决我们遇到的问题。除此之外，hints家族中还有leading(指定首先访问的表)、use_nl(nested loop的join方式)、use_hash(hash join的方式)等。

对于oracle，我懂的也不多，这几个hints怎么用，大家还是自己上网了解一下吧，网上的Oracle大神数不胜数，随便支点招就够我们abaper用的了。

IOS/ANDROID用户打赏——赞赏码