执行计划改变导致的生产事故

最新推荐文章于 2024-04-23 08:30:19 发布

东方-phantom

最新推荐文章于 2024-04-23 08:30:19 发布

阅读量161

点赞数

分类专栏： ORA- 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/hkyw000/article/details/109641796

版权

ORA- 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

最近某业务SQL执行时间过长，导致后续对账跑不出来。然后就做了那个时间段的awr报告。根据sql order by elapsed time 找到最高的SQL .
SQL:

select JRN_NO, JRN_SEQ, AC_DT, SYS_DT, SYS_TM, CAP_TYP, CCY, AC_TYP, PRD_NO, TX_TYP, BUS_TYP, ORG_JRN_NO, ORG_TX_DT, CNL_TX_CD, ORG_CD, AC_ORG_CD, AE_SEQ, SEP_CD, JRN_STS, RVS_TX_FLG, RVS_FLG_TYP, RVS_JRN, RVS_JRN_SEQ, TX_CD, DR_AMT, CR_AMT, BAL, OD_AMT, NOT_TX_AMT, UAVA_BAL, LAST_AVA_BAL, HOLD_AMT, USR_NO, TX_DESC, ORD_TYP, ORD_NO, TX_AMT, BUS_CNL, SYS_CNL, HLD_NO, HLD_DC_FLG, ORD_SEQ, TM_SMP, AMT_KIND, RMK, LAST_AC_BAL, NOT_TX_AVA_AMT, CNL_FLG, CNL_JRN, DC_FLG, NOT_TX_FLG, BAL_OD_FLG, NOD_ID, REQ_ID, UPD_BAL_FLG, STL_BAT_NO, STL_BAT_SEQ, STL_STS, STL_DT, STL_TM from CSDACM.T_ACM_CDDT where sep_cd = :1 and ac_dt <= :2 and ac_dt >=:3 and ord_seq = (select max (ord_seq) from CSDACM.T_ACM_CDDT where sep_cd = :4 and ac_dt <= :5 and ac_dt >=:6 ) and jrn_no = (select max(jrn_no) from CSDACM.T_ACM_CDDT where sep_cd = :7 and ord_seq != '0' and ac_dt <= :8 and ac_dt >=:9 ) and jrn_seq = (select max(jrn_seq) from CSDAC M.T_ACM_CDDT where sep_cd = :10 and ac_dt <= :11 and ac_dt >=:12 and ord_seq = (select max(ord_seq) from CSDACM.T_ACM_CDDT where sep_cd = :13 and ac_dt <= :14 and ac_dt >=:15 ) and jrn_no = (select max(jrn_no) from CSDACM.T_ACM_CDDT where sep_cd = :16 and ord_seq != '0' and ac_dt <= :17 and ac_dt >=:18 ) )

根据AWR里的sql_id 找到历史的执行计划：

select distinct SQL_ID,
                PLAN_HASH_VALUE,
                to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP
  from dba_hist_sql_plan
 where SQL_ID = '3ap66x3b3tc6h'
 order by TIMESTAMP;

SQL_ID        PLAN_HASH_VALUE TIMESTAMP
------------- --------------- -----------------
3ap66x3b3tc6h      2349313146 20200729 18:48:10
3ap66x3b3tc6h       828630111 20201111 22:58:24

根据plan_hash_value 找到对应的执行计划：

select * from table (dbms_xplan.display_awr(db_id => '1422150319', sql_id =>'3ap66x3b3tc6h' , plan_hash_value =>'828630111'));

Plan hash value: 2349313146 （较快的）

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |       |       |    47 (100)|          |
|   1 |  FILTER                              |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID        | T_ACM_CDDT     |     1 |   299 |     5   (0)| 00:00:01 |
|   3 |    INDEX RANGE SCAN                  | T_ACM_CDJN_PK  |     1 |       |     4   (0)| 00:00:01 |
|   4 |     SORT AGGREGATE                   |                |     1 |    44 |            |          |
|   5 |      FILTER                          |                |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID    | T_ACM_CDDT     |    39 |  1716 |    42   (0)| 00:00:01 |
|   7 |        INDEX RANGE SCAN              | T_ACM_CDJN_NI1 |    88 |       |     4   (0)| 00:00:01 |
|   8 |     SORT AGGREGATE                   |                |     1 |    47 |            |          |
|   9 |      FILTER                          |                |       |       |            |          |
|  10 |       TABLE ACCESS BY INDEX ROWID    | T_ACM_CDDT     |     1 |    47 |     5   (0)| 00:00:01 |
|  11 |        INDEX RANGE SCAN              | T_ACM_CDJN_PK  |     1 |       |     4   (0)| 00:00:01 |
|  12 |         SORT AGGREGATE               |                |     1 |    44 |            |          |
|  13 |          FILTER                      |                |       |       |            |          |
|  14 |           TABLE ACCESS BY INDEX ROWID| T_ACM_CDDT     |    39 |  1716 |    42   (0)| 00:00:01 |
|  15 |            INDEX RANGE SCAN          | T_ACM_CDJN_NI1 |    88 |       |     4   (0)| 00:00:01 |
|  16 |        SORT AGGREGATE                |                |     1 |    25 |            |          |
|  17 |         FILTER                       |                |       |       |            |          |
|  18 |          TABLE ACCESS BY INDEX ROWID | T_ACM_CDDT     |    88 |  2200 |    42   (0)| 00:00:01 |
|  19 |           INDEX RANGE SCAN           | T_ACM_CDJN_NI1 |    88 |       |     4   (0)| 00:00:01 |
|  20 |    SORT AGGREGATE                    |                |     1 |    25 |            |          |
|  21 |     FILTER                           |                |       |       |            |          |
|  22 |      TABLE ACCESS BY INDEX ROWID     | T_ACM_CDDT     |    88 |  2200 |    42   (0)| 00:00:01 |
|  23 |       INDEX RANGE SCAN               | T_ACM_CDJN_NI1 |    88 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------


Plan hash value: 828630111 （较慢的）

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |       |       |    10 (100)|          |
|   1 |  FILTER                             |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID       | T_ACM_CDDT     |     1 |   310 |     5   (0)| 00:00:01 |
|   3 |    INDEX RANGE SCAN                 | T_ACM_CDJN_PK  |     1 |       |     4   (0)| 00:00:01 |
|   4 |     SORT AGGREGATE                  |                |     1 |    46 |            |          |
|   5 |      FILTER                         |                |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID   | T_ACM_CDDT     |     1 |    46 |     5   (0)| 00:00:01 |
|   7 |        INDEX RANGE SCAN             | T_ACM_CDJN_NI5 |     1 |       |     4   (0)| 00:00:01 |
|   8 |     SORT AGGREGATE                  |                |     1 |    49 |            |          |
|   9 |      FILTER                         |                |       |       |            |          |
|  10 |       TABLE ACCESS BY INDEX ROWID   | T_ACM_CDDT     |     1 |    49 |     5   (0)| 00:00:01 |
|  11 |        INDEX RANGE SCAN             | T_ACM_CDJN_NI5 |     1 |       |     4   (0)| 00:00:01 |
|  12 |        SORT AGGREGATE               |                |     1 |    46 |            |          |
|  13 |         FILTER                      |                |       |       |            |          |
|  14 |          TABLE ACCESS BY INDEX ROWID| T_ACM_CDDT     |     1 |    46 |     5   (0)| 00:00:01 |
|  15 |           INDEX RANGE SCAN          | T_ACM_CDJN_NI5 |     1 |       |     4   (0)| 00:00:01 |
|  16 |        SORT AGGREGATE               |                |     1 |    27 |            |          |
|  17 |         FILTER                      |                |       |       |            |          |
|  18 |          TABLE ACCESS BY INDEX ROWID| T_ACM_CDDT     |     1 |    27 |     5   (0)| 00:00:01 |
|  19 |           INDEX RANGE SCAN          | T_ACM_CDJN_NI5 |     1 |       |     4   (0)| 00:00:01 |
|  20 |    SORT AGGREGATE                   |                |     1 |    27 |            |          |
|  21 |     FILTER                          |                |       |       |            |          |
|  22 |      TABLE ACCESS BY INDEX ROWID    | T_ACM_CDDT     |     1 |    27 |     5   (0)| 00:00:01 |
|  23 |       INDEX RANGE SCAN              | T_ACM_CDJN_NI5 |     1 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

发现里面用到的索引发生了变化：

SQL> select COLUMN_NAME from dba_ind_columns where INDEX_NAME='T_ACM_CDJN_NI5';

COLUMN_NAME
--------------------------------------------------------------------------------
AC_DT, USR_NO

SQL> select COLUMN_NAME from dba_ind_columns where INDEX_NAME='T_ACM_CDJN_NI1';

COLUMN_NAME
--------------------------------------------------------------------------------
AC_DT, SEP_CD

而写的语句里是有AC_DT, SEP_CD这两列的，系统没有选择用N1这个索引，反而用了N5 这个索引。

当时想的强制加索引，加到内循环中，依然无效。
然后收集统计信息，依然无效。

二、 baseline载入正确的执行计划：

查看内存是否有：

select sql_text,
 sql_id,
 hash_value,
 child_number,
 plan_hash_value,
 to_char(LAST_ACTIVE_TIME, 'hh24:mi:ss') time
 from v$sql a
where sql_id='3ap66x3b3tc6h' and plan_hash_value='2349313146';

从库缓存中载入：

DECLARE
 l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '3ap66x3b3tc6h',plan_hash_value=> '2349313146');
END;
/

查看载入后的信息：

SELECT * FROM dba_sql_plan_baselines where origin='MANUAL-LOAD' order by created desc;

固定执行计划，将该基线转为fixed

DECLARE
 i NATURAL;
 BEGIN
 i := dbms_spm.alter_sql_plan_baseline(
 'SQL_e546293a670659ae',
 'SQL_PLAN_fajj979mhcqdf1bc84d76',
 attribute_name => 'FIXED',
 attribute_value => 'YES');
 dbms_output.put_line(i);
 END;
/

查看是否存在了：

SELECT * FROM dba_sql_plan_baselines where origin='MANUAL-LOAD' order by created desc;

查看基线中的执行计划和后期删除方法：

select * from table(dbms_xplan.display_sql_plan_baseline('SQL_e546293a670659ae', plan_name => 'SQL_PLAN_fajj979mhcqdf1bc84d76',format => 'ADVANCED'));

删除base line的方法：


SELECT * FROM dba_sql_plan_baselines;
 
SET SERVEROUTPUT ON
DECLARE
v_text PLS_INTEGER;
BEGIN
v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SQL_e546293a670659ae',plan_name => NULL);
DBMS_OUTPUT.put_line(v_text);
END;