同一个 sql_id 有多个执行计划

问题描述

批量的场景下，执行该条 SQL (含有临时表)，相同的 SQL_ID，有大量不同的 PLAN_ID，计划不固定，且存在不命中情况，导致执行时间长，导致压测耗时偏大。

1. 执行 SQL 语句如下。

    UPDATE
    TAX_DRAFT_DATA D
    SET
    D.C60 = (
        SELECT
        SUM(tn.PERIOD_NET_CR_DR)
        FROM
        TAX_INDEX_INFORMATION tn
        WHERE
        tn.period_name = ? || ? || trim(to_char(?, ?))
        and tn.index_code = decode(
            D.row_code,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?
        )
        and (
            exists (
            select
                ?
            from
                tax_vat_information tt
            where
                tt.declare_level = ?
                and tn.taxpayer_id = tt.taxpayer_id
                and tt.taxpayer_id = ?
            )
            or (
            exists (
                select
                ?
                from
                tax_vat_information tt
                where
                tt.declare_level != ?
                and tt.taxpayer_id = ?
            )
            and tn.taxpayer_id in (
                select
                bvt.taxpayer_id
                from
                Tax_Register_Basic_vat_temp bvt
            )
            )
        )
    )
    WHERE
    D.YEAR = ?
    AND D.TAX_CATEGORY_TYPE = ?
    AND D.VERSION_DETAIL_ID = ?
    AND D.ROW_CODE IN (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?
    )
   

2. 查询当前租户在所有 Server 上的每个计划缓存整体的状态。

   select sql_id,
         svr_ip,
       hit_count,
         executions,
       avg_exe_usec,
       slowest_exe_usec,
       plan_id,
         type,
       last_active_time
   from gv$plan_cache_plan_stat
   where tenant_id = 1002
   and last_active_time > 'xxxx-xx-xx xx:xx:xx.xxxxxx'
   and sql_id='xxxxxxxxxx'
   order by executions desc,hit_count desc;
   

   输出结果如下。

   +----------------------------------+---------------+-----------+------------+--------------+------------------+----------+------+----------------------------+
   | sql_id                           | svr_ip        | hit_count | executions | avg_exe_usec | slowest_exe_usec | plan_id  | type | last_active_time           |
   +----------------------------------+---------------+-----------+------------+--------------+------------------+----------+------+----------------------------+
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         4 |          5 |      1527385 |          1628968 | 45500115 |    1 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         4 |          5 |      1533061 |          1673650 | 45486946 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         3 |          4 |      1532520 |          1640874 | 45502733 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         3 |          4 |        86125 |           128772 | 45688834 |    1 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         3 |          4 |      1530349 |          1644110 | 45498490 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         2 |          3 |       124344 |           175108 | 45557942 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         2 |          3 |       172029 |           210541 | 45711193 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   ..............................................................................................................................................................
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |       143295 |           143295 | 45688878 |    1 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |       135159 |           135159 | 45697437 |    1 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |       152189 |           152189 | 45688901 |    1 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |      1604819 |          1604819 | 45738075 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |      1611552 |          1611552 | 45688955 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |      1625996 |          1625996 | 45688960 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |       116965 |           116965 | 45738195 |    1 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |       157273 |           157273 | 45689048 |    1 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |       184898 |           184898 | 45689050 |    3 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   | xxxxxxxxxx                       | xx.xx.xx.xx   |         0 |          1 |      1677552 |          1677552 | 45689194 |    1 | xxxx-xx-xx xx:xx:xx.xxxxxx |
   +----------------------------------+---------------+-----------+------------+--------------+------------------+----------+------+----------------------------+
   187 rows in set (0.08 sec)
   

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。

问题原因

由于执行的 SQL 语句在批量场景下相同的 SQL_ID 存在大量的不同的 PLAN_ID (有多个不同的执行计划被缓存)，且计划不固定，并且存在不命中的情况。从查询的计划缓存中可以看出，同一个 SQL 语句的不同 PLAN_ID 的执行情况，其中有些 PLAN_ID 的执行次数和命中次数较多，而有些 PLAN_ID 的执行次数较少或者没有命中。这可能导致了执行时间长的问题。

解决方法

临时表改成实体表。