SQL性能优化：EBS资产报表优化案例一则

2022-07-10 08:23:17

资产模块以有个客户化报表，是通过FA_相关表和子分类账XLA 联查得出资产模块信息和会计信息，未优化前跑单个资产账套单个月的数据需要一个小时，SQL优化改写后一秒左右就能跑完。本文用比较简单朴素的手段来一步步分析如何进行优化

核心指导思想

尽可能的缩小查询的数据范围
用小表驱动大表

定位性能SQL

这是一个大的pkg包，可能涉及代码几百上千行，怎么快速定位是哪一段代码是罪魁祸首，导致报表速度下降呢，需要用一个Oracle 自带的分析利器 DBMS_HPROF ，简介及使用方法参见这篇文章
ORACLE性能优化之-DBMS_HPROF

优化前SQL

经过上一步几个简单的命令，我们已经定位性能sql，这是一个UNION SQL,结构很相似，此处仅摘取一部分为例说明

--该sql是一个报表sql，还有几个参数条件，跟此次优化无关，已经过滤掉
SELECT a.asset_id,
       to_char(h.transaction_date_entered, 'yyyy-mm-dd hh24:mi:ss'),
       to_char(xe.transaction_date, 'yyyy-mm-dd'),
       b.book_type_code,
       a.asset_number,
       a.description,
       c.segment1,
       c.segment2,
       b.original_cost amount,
       '增加' trans_type
  FROM fa_transaction_headers   h,
       fa_additions_vl          a,
       fa_books                 b,
       fa_categories            c,
       xla_transaction_entities xte,
       xla_events               xe,
       fa_asset_invoices        i
 WHERE a.asset_id = h.asset_id
       AND b.asset_id = a.asset_id
       AND c.category_id = a.asset_category_id
       AND h.book_type_code = b.book_type_code
       AND i.asset_id = a.asset_id
       AND i.po_vendor_id = i.po_vendor_id
       AND b.date_ineffective IS NULL
       AND h.transaction_type_code = 'ADDITION'
       AND h.transaction_header_id = xte.source_id_int_1
       AND xte.entity_id = xe.entity_id

处理思路

数据分析（不结合业务）

假设我们对EBS表结构和业务不熟悉，单纯从技术角度进行分析。
这个SQL并不复杂，只有7个表，通过统计数据规模，其中FA_开头的相关表数据规模是几万条左右，属于小表。以XLA_开头的表是几百万至千万级别，属于大表。
在查看相关表索引（这里我们重点关注大表的索引和结构），发现XLA_相关表是分区表，是通过字段application_id这个字段进行分区的，但是上面的这个SQL中没有用到分区限制，通过上面sql查询后，发现这个sql取的application_id完全一样，都是140，通过向有过一两年经验EBS技术顾问了解（此处假设我们完全不懂EBS业务和表结构，稍微有点经验的EBS技术顾问都知道这个字段是业务模块字段ID），其实这个sql是资产模块同子分类账联查的sql，xla_开头的表都有application_id这个业务模块字段，资产模块对应的application_id是140，没有其他值。
那么我们就可以利用分区表特性，加入分区限制，给大表加入以下条件

AND xte.application_id = xe.application_id --利用表分区表特性缩小范围
AND xte.application_id = 140 --利用表分区表缩小范围

再次查询后发现效率提升了不少，数据量稍微大点（几千条规模）的仍需要几分钟，数据量小点的（几百条）需要一分钟，这点数据量对于内存超过100G、24个逻辑核心的数据库还要这么长时间，应该还可以有优化空间。

结合业务进行分析

在运行这个报表的时候通过session追踪，我们定位了这个有问题的sqlid，然后通过sqlhc这个Oracle 免费工具，一个很简单的命令
sql> sqlhc T 【sqlid】
来获得一份全面的分析报告(其实用SQL Tuning Advisor 也能取得，只不过sqlhc更简单，一条命令搞定)，获得一份优化建议：

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.98%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'fpkqh87w1y36b_tuning_task', task_owner => 'SYS', replace =>
            TRUE);

2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 98.65%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index XLA.IDX$$_A3EE0001 on XLA.XLA_TRANSACTION_ENTITIES("SOURCE_ID_
    INT_1");

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.  If you choose to create the
    recommended index, consider dropping the index "FA"."FA_BOOKS_N2" because
    it is a prefix of the recommended index.
    create index FA.IDX$$_A3EE0002 on FA.FA_BOOKS("TRANSACTION_HEADER_ID_OUT","
    ASSET_ID");

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index FA.IDX$$_A3EE0003 on FA.FA_TRANSACTION_HEADERS("BOOK_TYPE_CODE
    ","TRANSACTION_TYPE_CODE","TRANSACTION_SUBTYPE");

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index FA.IDX$$_A3EE0004 on FA.FA_ADJUSTMENTS("TRANSACTION_HEADER_ID"
    );

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

第一部分是执行计划改写，部分情况可以获得很大的性能提升，可以刷入到系统试试。
第二部分是索引维护，我们关注到一个建议是在XLA_TRANSACTION_ENTITIES 这个大表上建立一个字段SOURCE_ID_INT_1的索引，这个字段一般是来源各个业务模块的事务的主键，但是这个这个表是子分类账的一个公用表，其他模块也会用到，不敢贸然加索引，怕影响其他模块的更新效率。考虑能否利用表上的现有索引，
这个表有两个索引，一个常规组合索引：

(LEDGER_ID, ENTITY_CODE, NVL(SOURCE_ID_INT_1,(-99)), NVL(SOURCE_ID_INT_2,(-99)), NVL(SOURCE_ID_INT_3,(-99)), NVL(SOURCE_ID_INT_4,(-99)), NVL(SOURCE_ID_CHAR_1,' '), NVL(SOURCE_ID_CHAR_2,' '), NVL(SOURCE_ID_CHAR_3,' '), NVL(SOURCE_ID_CHAR_4,' '))

一个唯一组合索引：

ENTITY_ID, APPLICATION_ID

唯一索引我们已经用到了，那么考虑是否可以用到组合索引呢，这个sql我们用到了大表XLA_TRANSACTION_ENTITIES 上的SOURCE_ID_INT_1这个字段，但是LEDGER_ID, ENTITY_CODE 这两个字段没用到，那么这个索引就是没利用到，是否可以在不影响原sql结果的前提下，通过sql改写来实现利用这个sql前缀呢，通过业务分析我们了解 LEDGER_ID 是总账账套ID, FA
模块向子分类账追溯时分为两个部分资产事务和资产折旧，分别对应 ENTITY_CODE的两个值是【TRANSACTIONS】和【DEPRECIATION】，我们这部分业务不涉及到资产折旧，所以可以把这个大表的ENTITY_CODE 字段值固定为TRANSACTIONS。
同时，可以通过资产账簿的配置表fa_book_controls 来实现资产账簿同总账账簿的关联，那么我们就能利用这个大表的现有索引来加快对大表的范围筛选，如下sql：

--加入资产账簿配置表 
fa_book_controls  fbc
--加入如下关联条件
AND xte.ledger_id = fbc.set_of_books_id --利用xla_transaction_entities现有索引
AND fbc.book_type_code = h.book_type_code --利用xla_transaction_entities现有索引
AND xte.entity_code = 'TRANSACTIONS' --利用xla_transaction_entities现有索引

优化后SQL

优化后的sql如下：

SELECT a.asset_id,
       to_char(h.transaction_date_entered, 'yyyy-mm-dd hh24:mi:ss'),
       to_char(xe.transaction_date, 'yyyy-mm-dd'),
       b.book_type_code,
       a.asset_number,
       a.description,
       c.segment1,
       c.segment2,
       b.original_cost amount,
       '增加' trans_type
  FROM fa_transaction_headers   h,
       fa_additions_vl          a,
       fa_books                 b,
       fa_categories            c,
       xla_transaction_entities xte,
       xla_events               xe,
       fa_asset_invoices        i,
       fa_book_controls         fbc --增加资产账簿以便同子分类账关联
 WHERE a.asset_id = h.asset_id
       AND b.asset_id = a.asset_id
       AND c.category_id = a.asset_category_id
       AND h.book_type_code = b.book_type_code
       AND i.asset_id = a.asset_id
       AND i.po_vendor_id = nvl(p_vendor_id, i.po_vendor_id)
       AND (a.description LIKE '%' || p_asset_desc || '%' OR p_asset_desc IS NULL)
       AND b.date_ineffective IS NULL
       AND h.transaction_type_code = 'ADDITION'
       AND b.book_type_code = p_book_type_code
       AND h.transaction_header_id = nvl(xte.source_id_int_1, (-99))
       AND xte.entity_id = xe.entity_id
       AND xe.transaction_date >= to_date(p_date_from, 'yyyy-mm-dd hh24:mi:ss')
       AND xe.transaction_date <= to_date(p_date_to, 'yyyy-mm-dd hh24:mi:ss') + 1
       /* 下面是新增的关联条件及原因 */
       AND xte.application_id = xe.application_id --利用表分区表特性缩小范围
       AND xte.application_id = 140 --利用表分区表缩小范围
       AND xte.ledger_id = fbc.set_of_books_id --利用xla_transaction_entities现有索引
       AND fbc.book_type_code = h.book_type_code --利用xla_transaction_entities现有索引
       AND xte.entity_code = 'TRANSACTIONS' --利用xla_transaction_entities现有索引

此报表sql经过改写后运行单个资产账簿、单个月的数据能秒出。

分析

上面的一些改写就是遵循很朴素的两个思想

尽量缩小数据的查询范围，如利用分区限制及业务常数固定
利用小表驱动大表，小表同大表关联的字段上最理想的情况是都有索引，次之是小表可以无索引，大表有索引。

这两个原理不光是针对Oracle数据库，其他的关系型数据库也是通用的。

码农公寓