故障描述
告警： [Ucenter 平台 ] 告警 2024-08-20 07:10:36 来自 Linux_172.27.12.26(172.27.12.26) 的（ Linux_172.27.12.26 ）的 CPU 利用率的文件阈值当前为 92.61 ，应用类型（ Linux ）。
问题详细诊断过程
登录数据库检查等待事件，发现此节点有上百个进程在执行 Sql ： a13tu7zzjj1hd
问题排查
1） 检查 SQL 历史执行效率 SQL> select to_char(s.begin_interval_time,'MM-DD HH24:MI') snap_time, 2 ss.executions_delta execs, 3 round(ss.buffer_gets_delta/decode(ss.executions_delta,0,1,ss.executions_delta),2) lio_per_exec, 4 round(ss.disk_reads_delta/decode(ss.executions_delta,0,1,ss.executions_delta),2) pio_per_exec, 5 round((ss.cpu_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta),2) cpu_per_exec, 6 round((ss.elapsed_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta),2) ela_per_exec 7 from dba_hist_snapshot s, 8 dba_hist_sqlstat ss 9 where ss.dbid=s.dbid 10 and ss.instance_number=s.instance_number 11 and ss.snap_id=s.snap_id 12 and ss.sql_id='&sql_id' 13 and ss.executions_delta>0 14 and s.begin_interval_time>=sysdate-7 15 order by s.snap_id; Enter value for sql_id: a13tu7zzjj1hd old 12: and ss.sql_id='&sql_id' new 12: and ss.sql_id='a13tu7zzjj1hd' SNAP_TIME EXECS LIO_PER_EXEC PIO_PER_EXEC CPU_PER_EXEC ELA_PER_EXEC ----------- ---------- ------------ ------------ ------------ ------------
08-19 23:00 788 11071.19 .12 .05 .05
08-19 23:00 946 10031.74 .13 .05 .05
08-19 23:00 326 22519.56 .13 .14 .14
08-19 23:00 1113 33095.33 .01 .25 .25
08-19 23:00 906 76344.36 .32 .92 .92
08-19 23:00 176 61082.49 .15 .85 .85
08-19 23:00 51 10582.67 0 .06 .06
08-19 23:30 352 24321.62 .22 .15 .15
08-19 23:30 1270 39594.72 .04 .27 .27
08-19 23:30 58 10332.17 0 .05 .05
08-19 23:30 846 13360.69 0 .07 .07
08-19 23:30 1538 75742.3 .03 .92 .92
08-19 23:30 53 1862106.36 .36 4.55 4.56
08-19 23:30 83 68332.78 .01 .9 .9
08-19 23:30 1101 15031.29 0 .07 .07
08-19 23:30 2326 43234.51 .01 .29 .29
08-19 23:30 666 12013.81 .08 .06 .06
08-19 23:30 661 9152.31 .01 .04 .04
08-19 23:30 514 20456.49 .05 .13 .13
08-19 23:30 748 33633.98 0 .25 .25
08-19 23:30 783 77258.25 .77 .92 .92
08-19 23:30 26 68445.65 0 .89 .89
08-19 23:30 801 33166.86 0 .26 .26
08-20 00:00 1673 41696.38 .03 .28 .28
08-20 00:00 16 25585.38 0 .18 .19
08-20 00:00 2 53273 0 .35 .35
08-20 00:00 14 9646.71 .14 .05 .06
08-20 00:00 61 64172.85 0 .92 .92
08-20 00:00 4291 85873.42 0 22.9 22.95
08-20 00:00 153 63306.87 0 .84 .84
08-20 00:00 58 56359.4 0 .34 .34
08-20 00:00 1032 17180.91 .02 .08 .08
08-20 00:00 571 10898.51 .01 .05 .05
08-20 00:00 635 21908.15 .03 .14 .14
08-20 00:00 1100 34140.33 0 .25 .26
08-20 00:00 1153 75917.68 0 .91 .92
08-20 00:00 43 22332.53 0 .11 .11
08-20 00:30 1038 13698.58 .01 .07 .07
08-20 00:30 935 10476.53 0 .05 .05
08-20 00:30 479 24294.22 0 .14 .14
08-20 00:30 694 33899.08 0 .25 .25
08-20 00:30 4521 202473.44 0 27.16 27.19
08-20 00:30 55 61933.16 0 .87 .88
08-20 00:30 2028 40594.36 .02 .27 .27
08-20 00:30 14 5025 0 .03 .03
08-20 00:30 1115 76819.38 .11 .9 .91
08-20 01:00 1141 15522.01 .01 .08 .08
08-20 01:00 893 11099.29 0 .05 .05
08-20 01:00 555 20374.08 .02 .13 .13
08-20 01:00 487 33485.48 0 .25 .25
08-20 01:00 4494 181796.63 0 33.11 33.15
08-20 01:00 126 59683.5 .05 .83 .84
08-20 01:00 2028 41380.96 0 .28 .28
08-20 01:00 11 5025 0 .03 .03
08-20 01:00 1232 71415.57 .03 .88 .88
08-20 01:30 1019 12524.78 0 .06 .06
08-20 01:30 725 11842.13 0 .06 .06
08-20 01:30 272 21538.9 0 .13 .13
08-20 01:30 523 34382.64 0 .25 .25
08-20 01:30 4440 166052.62 0 40.49 40.52
08-20 01:30 85 60853.39 0 .83 .84
08-20 01:30 2714 42796.64 0 .28 .28
08-20 01:30 4 5025 0 .04 .04
08-20 01:30 1114 73383.76 0 .88 .88
08-20 02:00 8 5025 0 .05 .05
08-20 02:00 4100 15875.66 0 46.61 46.66
08-20 02:00 898 13852.31 .01 .07 .07
08-20 02:00 492 11063.2 0 .05 .05
08-20 02:00 2533 41954.09 .01 .28 .28
08-20 02:00 736 34410.13 0 .25 .25
08-20 02:00 1271 71195.11 .04 .88 .88
08-20 02:00 167 62138.63 0 .86 .86
08-20 02:00 595 20478.95 0 .13 .13
08-20 02:30 968 12826.34 0 .06 .06
08-20 02:30 609 10580.72 0 .05 .05
08-20 02:30 819 22095.05 0 .14 .14
08-20 02:30 687 34113.15 .04 .25 .25
08-20 02:30 4283 89869.64 0 51.25 51.28
08-20 02:30 46 61836.72 0 .85 .86
08-20 02:30 2467 42640.16 0 .28 .28
08-20 02:30 12 5025 0 .03 .03
08-20 02:30 1506 69819.23 0 .87 .87
08-20 03:00 780 17488.51 0 .08 .08
08-20 03:00 849 11840.15 0 .06 .06
08-20 03:00 651 21158.76 0 .13 .13
08-20 03:00 809 33472.88 0 .25 .25
08-20 03:00 4128 22509.55 0 57.4 57.45
08-20 03:00 61 59288.43 0 .83 .83
08-20 03:00 2371 43140.45 0 .29 .29
08-20 03:00 11 5025 0 .04 .04
08-20 03:00 1196 70585.81 0 .88 .88
08-20 03:30 792 14849.31 0 .07 .07
08-20 03:30 671 11521.96 0 .06 .06
08-20 03:30 452 20239.34 0 .13 .13
08-20 03:30 819 34128.8 0 .25 .25
08-20 03:30 3941 16490.25 0 62.09 62.14
08-20 03:30 97 63647.33 0 .86 .86
08-20 03:30 1952 42364.17 0 .28 .29
08-20 03:30 10 5025 0 .05 .05
08-20 03:30 1275 71301.26 0 .88 .88
08-20 04:00 8 5025 0 .06 .06
08-20 04:00 4130 20476.4 0 65.14 65.19
08-20 04:00 737 12396.42 0 .06 .06
08-20 04:00 983 12653 0 .06 .06
08-20 04:00 2023 42905.64 0 .28 .28
08-20 04:00 841 34423.47 0 .25 .25
08-20 04:00 1230 72488.16 0 .88 .88
08-20 04:00 76 59485.17 0 .81 .81
08-20 04:00 370 21843.55 0 .14 .14
08-20 04:30 944 10986.48 0 .05 .06
08-20 04:30 745 12170.17 0 .06 .06
08-20 04:30 611 21649.73 0 .13 .13
08-20 04:30 812 34313.33 0 .26 .26
08-20 04:30 4148 35444.15 0 68.83 68.87
08-20 04:30 74 60311.91 0 .82 .82
08-20 04:30 2171 42274.05 0 .28 .28
08-20 04:30 9 5025 0 .1 .1
08-20 04:30 995 76738.81 0 .89 .89
08-20 05:00 8 5025 0 .06 .06
08-20 05:00 3978 0 0 69.8 69.85
08-20 05:00 1315 14082.47 0 .07 .07
08-20 05:00 864 13958.9 0 .07 .07
08-20 05:00 2038 43900.49 0 .28 .29
08-20 05:00 634 34375.39 0 .25 .25
08-20 05:00 1004 69500.18 0 .86 .86
08-20 05:00 5 72806.4 0 .88 .89
08-20 05:00 272 27227.12 0 .16 .16
08-20 05:30 963 12102.69 0 .06 .06
08-20 05:30 760 20393.88 0 .13 .13
08-20 05:30 648 11209.55 0 .05 .05
08-20 05:30 696 34122.45 0 .24 .24
08-20 05:30 4095 3289.09 0 72.38 72.43
08-20 05:30 60 60066.6 0 .84 .84
08-20 05:30 1968 44199.64 0 .28 .28
08-20 05:30 5 5025 0 .03 .03
08-20 05:30 1343 68082.15 0 .86 .86
08-20 06:00 9 5025 0 .03 .03
08-20 06:00 4017 0 0 83.98 84.05
08-20 06:00 1005 13811.87 0 .07 .07
08-20 06:00 589 20073.78 0 .12 .13
08-20 06:00 2025 42322.45 .01 .28 .28
08-20 06:00 698 34159.51 0 .25 .25
08-20 06:00 1073 72364.07 .01 .89 .89
08-20 06:00 113 57673.83 0 .83 .83
08-20 06:00 1075 16780.89 0 .08 .08
08-20 06:30 707 27000.05 .06 .15 .16
08-20 06:30 921 12050.3 0 .06 .06
08-20 06:30 507 33682.2 0 .25 .25
08-20 06:30 1169 71813.02 .01 .89 .89
08-20 06:30 1234 10464.1 .02 .05 .05
08-20 06:30 2023 42610.35 0 .29 .29
08-20 06:30 11 5025 0 .03 .03
08-20 06:30 3878 0 0 92.41 92.49
08-20 06:30 47 63018.94 0 .87 .87
08-20 07:00 6 5025 0 .05 .05
08-20 07:00 3358 834279.83 0 101.54 101.68
08-20 07:00 973 7627.32 .21 .04 .04
08-20 07:00 835 10903.97 .14 .05 .05
08-20 07:00 2039 40100.17 .01 .28 .28
08-20 07:00 637 33147.3 .02 .25 .25
08-20 07:00 1112 79970.36 .47 .93 .94
08-20 07:00 105 62033.1 8.53 .88 .89
08-20 07:00 487 21970.79 2.1 .13 .13
可以发现在 08-19 23:30 明显有一个执行计划的 sql 时间变长了，而且随着 CPU 争用的增加，执行效率越来越低。
2） 取 awrsqlrtp 查看此 Sql 的执行计划
Plan hash value 的执行计划为错误执行计划，平均执行一次大概为 700,116,749ms/7236=96 秒。
获取 sql 的绑定变量
SQL> set line 300 pagesize 999
SQL> col VALUE_STRING for a20
SQL> select sql_id,name,position,DATATYPE_STRING,last_captured,value_string from v$sql_bind_capture where sql_id='&sql_id';
Enter value for sql_id: a13tu7zzjj1hd
SQL_ID
-------------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSITION DATATYPE_STRING LAST_CAPT VALUE_STRING
---------- ------------------------------------------------------------ --------- --------------------
a13tu7zzjj1hd
:1
1 VARCHAR2(128) 20-AUG-24 2024-08-13 00:00:00
a13tu7zzjj1hd
:2
2 VARCHAR2(128) 20-AUG-24 2024-08-20 23:59:59
a13tu7zzjj1hd
:3
3 VARCHAR2(128) 20-AUG-24 62e2303b529fe35987b7
6562
a13tu7zzjj1hd
:4
4 VARCHAR2(128) 20-AUG-24 610cf685bd06d3454cfa
5cba
a13tu7zzjj1hd
:5
5 VARCHAR2(128) 20-AUG-24 62e2303b529fe35987b7
6562
a13tu7zzjj1hd
:1
1 VARCHAR2(128) 20-AUG-24 2024-08-13 00:00:00
a13tu7zzjj1hd
:2
2 VARCHAR2(128) 20-AUG-24 2024-08-20 23:59:59
a13tu7zzjj1hd
:3
3 VARCHAR2(128) 20-AUG-24 6161940824f65b2ec778
69b6
a13tu7zzjj1hd
:4
4 VARCHAR2(128) 20-AUG-24 610cf685bd06d3454cfa
59a6
a13tu7zzjj1hd
:5
5 VARCHAR2(128) 20-AUG-24 6161940824f65b2ec778
69b6
10 rows selected.
取得异常 sql 的 Outline Data ，可以用来手工执行与异常 sql 相同的执行计划。
set line 300 pagesize 999
select * from table(dbms_xplan.display_cursor('&sql_id',0,'advanced'));
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_undo_cost_change' '10.2.0.4')
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('_optimizer_extend_jppd_view_types' 'false')
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_replace_virtual_columns' 'false')
OPT_PARAM('_first_k_rows_dynamic_proration' 'false')
OPT_PARAM('_bloom_pruning_enabled' 'false')
OPT_PARAM('_optimizer_multi_level_push_pred' 'false')
OPT_PARAM('_nlj_batching_enabled' 0)
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
OPT_PARAM('_fix_control' '9344709:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$7C475101")
ELIMINATE_JOIN(@"SEL$B5E9CB87" "HIPI3_"@"SEL$3")
OUTLINE(@"SEL$B5E9CB87")
OUTER_JOIN_TO_INNER(@"SEL$94AFEEFD" "HIHPLMEDOR2_"@"SEL$2")
OUTLINE(@"SEL$94AFEEFD")
MERGE(@"SEL$C9E671F9" >"SEL$224C738B")
OUTLINE(@"SEL$224C738B")
ANSI_REARCH(@"SEL$5")
OUTLINE(@"SEL$C9E671F9")
MERGE(@"SEL$42D95501" >"SEL$BD5F319E")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$BD5F319E")
ANSI_REARCH(@"SEL$F15B0E4A")
OUTLINE(@"SEL$42D95501")
MERGE(@"SEL$66B15841" >"SEL$E15FCA1A")
OUTLINE(@"SEL$F15B0E4A")
ANSI_REARCH(@"SEL$4")
OUTLINE(@"SEL$E15FCA1A")
ANSI_REARCH(@"SEL$41465E65")
OUTLINE(@"SEL$66B15841")
MERGE(@"SEL$8812AA4E" >"SEL$10B37F33")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$41465E65")
ANSI_REARCH(@"SEL$3")
OUTLINE(@"SEL$10B37F33")
ANSI_REARCH(@"SEL$948754D7")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$7C475101" "HIHPLMEDOR2_"@"SEL$2" ("HI_HPL_MED_ORD"."DT_MEDORDSIGN"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$7C475101" "HIHPLMEDOR2_"@"SEL$2")
INDEX_RS_ASC(@"SEL$7C475101" "HIVISMEDIP4_"@"SEL$4" ("HI_VIS_MED_IP"."ID_VISMED"))
INDEX_RS_ASC(@"SEL$7C475101" "HIHPLMEDOR0_"@"SEL$1" ("HI_HPL_MED_ORD_CONSL_PHY"."ID_MEDORD"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$7C475101" "HIHPLMEDOR0_"@"SEL$1")
INDEX_RS_ASC(@"SEL$7C475101" "HIHPLMEDOR1_"@"SEL$1" ("HI_HPL_MED_ORD_CONSL"."ID_MEDORD"))
LEADING(@"SEL$7C475101" "HIHPLMEDOR2_"@"SEL$2" "HIVISMEDIP4_"@"SEL$4" "HIHPLMEDOR0_"@"SEL$1" "HIHPLMEDOR1_"@"SEL$1")
USE_NL(@"SEL$7C475101" "HIVISMEDIP4_"@"SEL$4")
USE_NL(@"SEL$7C475101" "HIHPLMEDOR0_"@"SEL$1")
USE_NL(@"SEL$7C475101" "HIHPLMEDOR1_"@"SEL$1")
END_OUTLINE_DATA
*/
手工执行完整的 sql
查看带有 A-TIME 的执行计划的用法如下。
set line 300 pagesize 999
set serveroutput off
alter session set STATISTICS_LEVEL = ALL;
select /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_undo_cost_change' '10.2.0.4')
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('_optimizer_extend_jppd_view_types' 'false')
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_replace_virtual_columns' 'false')
OPT_PARAM('_first_k_rows_dynamic_proration' 'false')
OPT_PARAM('_bloom_pruning_enabled' 'false')
OPT_PARAM('_optimizer_multi_level_push_pred' 'false')
OPT_PARAM('_nlj_batching_enabled' 0)
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
OPT_PARAM('_fix_control' '9344709:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$7C475101")
ELIMINATE_JOIN(@"SEL$B5E9CB87" "HIPI3_"@"SEL$3")
OUTLINE(@"SEL$B5E9CB87")
OUTER_JOIN_TO_INNER(@"SEL$94AFEEFD" "HIHPLMEDOR2_"@"SEL$2")
OUTLINE(@"SEL$94AFEEFD")
MERGE(@"SEL$C9E671F9" >"SEL$224C738B")
OUTLINE(@"SEL$224C738B")
ANSI_REARCH(@"SEL$5")
OUTLINE(@"SEL$C9E671F9")
MERGE(@"SEL$42D95501" >"SEL$BD5F319E")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$BD5F319E")
ANSI_REARCH(@"SEL$F15B0E4A")
OUTLINE(@"SEL$42D95501")
MERGE(@"SEL$66B15841" >"SEL$E15FCA1A")
OUTLINE(@"SEL$F15B0E4A")
ANSI_REARCH(@"SEL$4")
OUTLINE(@"SEL$E15FCA1A")
ANSI_REARCH(@"SEL$41465E65")
OUTLINE(@"SEL$66B15841")
MERGE(@"SEL$8812AA4E" >"SEL$10B37F33")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$41465E65")
ANSI_REARCH(@"SEL$3")
OUTLINE(@"SEL$10B37F33")
ANSI_REARCH(@"SEL$948754D7")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$948754D7")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$7C475101" "HIHPLMEDOR2_"@"SEL$2" ("HI_HPL_MED_ORD"."DT_MEDORDSIGN"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$7C475101" "HIHPLMEDOR2_"@"SEL$2")
INDEX_RS_ASC(@"SEL$7C475101" "HIVISMEDIP4_"@"SEL$4" ("HI_VIS_MED_IP"."ID_VISMED"))
INDEX_RS_ASC(@"SEL$7C475101" "HIHPLMEDOR0_"@"SEL$1" ("HI_HPL_MED_ORD_CONSL_PHY"."ID_MEDORD"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$7C475101" "HIHPLMEDOR0_"@"SEL$1")
INDEX_RS_ASC(@"SEL$7C475101" "HIHPLMEDOR1_"@"SEL$1" ("HI_HPL_MED_ORD_CONSL"."ID_MEDORD"))
LEADING(@"SEL$7C475101" "HIHPLMEDOR2_"@"SEL$2" "HIVISMEDIP4_"@"SEL$4" "HIHPLMEDOR0_"@"SEL$1" "HIHPLMEDOR1_"@"SEL$1")
USE_NL(@"SEL$7C475101" "HIVISMEDIP4_"@"SEL$4")
USE_NL(@"SEL$7C475101" "HIHPLMEDOR0_"@"SEL$1")
USE_NL(@"SEL$7C475101" "HIHPLMEDOR1_"@"SEL$1")
END_OUTLINE_DATA
*/
count(*) as col_0_0_
from HI_HPL_MED_ORD_CONSL_PHY hihplmedor0_
left outer join hi_hpl_med_ord_consl hihplmedor1_
on (hihplmedor0_.id_medord = hihplmedor1_.id_medord)
left outer join HI_HPL_MED_ORD hihplmedor2_
on (hihplmedor0_.id_medord = hihplmedor2_.id_medord)
left outer join hi_pi hipi3_
on (hihplmedor2_.id_pi = hipi3_.id_pi)
left outer join hi_vis_med_ip hivismedip4_
on (hivismedip4_.id_vismed = hihplmedor2_.id_vismed)
where 1 = 1
and hihplmedor2_.dt_medordsign >= to_date('2024-08-13 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and hihplmedor2_.dt_medordsign <= to_date('2024-08-20 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
and (hihplmedor1_.sd_medordconsl_cd <> '3' and
(hihplmedor0_.id_dep_consl = '62e2303b529fe35987b76562' and
(hihplmedor0_.id_emp_consl_app is null) or
hihplmedor0_.id_emp_consl_app = '610cf685bd06d3454cfa5cba') or
hihplmedor1_.sd_medordconsl_cd = '3' and
hihplmedor1_.id_dep_medord = '62e2303b529fe35987b76562')
and (hihplmedor2_.sd_vistp_cd = '114' and hivismedip4_.sd_ip_medtp_cd < '2' or hihplmedor2_.sd_vistp_cd <> '114')
order by hihplmedor2_.dt_medordsign desc nulls last;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
Plan hash value: 1641324214
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:06.49 | 2443K| 1097 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:06.49 | 2443K| 1097 |
|* 2 | FILTER | | 1 | | 3 |00:00:06.49 | 2443K| 1097 |
| 3 | NESTED LOOPS OUTER | | 1 | 469 | 3694 |00:00:06.49 | 2443K| 1097 |
| 4 | NESTED LOOPS | | 1 | 249K| 3694 |00:00:06.47 | 2439K| 1097 |
|* 5 | FILTER | | 1 | | 563K|00:00:05.97 | 2410K| 1094 |
| 6 | NESTED LOOPS OUTER | | 1 | 334K| 786K|00:00:05.86 | 2410K| 1094 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| HI_HPL_MED_ORD | 1 | 603K| 786K|00:00:03.39 | 705K| 1094 |
|* 8 | INDEX RANGE SCAN | IDX_HI_HPL_MED_ORD_DT_SIGN | 1 | 603K| 786K|00:00:00.82 | 3844 | 1094 |
| 9 | TABLE ACCESS BY INDEX ROWID | HI_VIS_MED_IP | 786K| 1 | 354K|00:00:02.18 | 1705K| 0 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0038438 | 786K| 1 | 354K|00:00:01.38 | 1339K| 0 |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED | HI_HPL_MED_ORD_CONSL_PHY | 563K| 1 | 3694 |00:00:00.42 | 28926 | 3 |
|* 12 | INDEX RANGE SCAN | IDX_HI_HPL_MED_ORD_CONSL_PHY_M | 563K| 1 | 3694 |00:00:00.29 | 25290 | 3 |
| 13 | TABLE ACCESS BY INDEX ROWID | HI_HPL_MED_ORD_CONSL | 3694 | 1 | 3694 |00:00:00.02 | 4086 | 0 |
|* 14 | INDEX UNIQUE SCAN | SYS_C0037052 | 3694 | 1 | 3694 |00:00:00.01 | 392 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((((("HIHPLMEDOR0_"."ID_DEP_CONSL"='62e2303b529fe35987b76562' AND "HIHPLMEDOR0_"."ID_EMP_CONSL_APP" IS NULL) OR
"HIHPLMEDOR0_"."ID_EMP_CONSL_APP"='610cf685bd06d3454cfa5cba') AND "HIHPLMEDOR1_"."SD_MEDORDCONSL_CD"<>'3') OR
("HIHPLMEDOR1_"."SD_MEDORDCONSL_CD"='3' AND "HIHPLMEDOR1_"."ID_DEP_MEDORD"='62e2303b529fe35987b76562')))
5 - filter((("HIHPLMEDOR2_"."SD_VISTP_CD"='114' AND "HIVISMEDIP4_"."SD_IP_MEDTP_CD"<'2') OR "HIHPLMEDOR2_"."SD_VISTP_CD"<>'114'))
8 - access("HIHPLMEDOR2_"."DT_MEDORDSIGN">=TO_DATE(' 2024-08-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"HIHPLMEDOR2_"."DT_MEDORDSIGN"<=TO_DATE(' 2024-08-20 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
10 - access("HIVISMEDIP4_"."ID_VISMED"="HIHPLMEDOR2_"."ID_VISMED")
12 - access("HIHPLMEDOR0_"."ID_MEDORD"="HIHPLMEDOR2_"."ID_MEDORD")
14 - access("HIHPLMEDOR0_"."ID_MEDORD"="HIHPLMEDOR1_"."ID_MEDORD")
55 rows selected.
大部分时间都花在了
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| HI_HPL_MED_ORD | 1 | 603K| 786K|00:00:03.39 | 705K| 1094 |
|* 8 | INDEX RANGE SCAN | IDX_HI_HPL_MED_ORD_DT_SIGN | 1 | 603K| 786K|00:00:00.82 | 3844 | 1094 |
时间索引与回表查询上面。
Plan hash value: 1280427351
----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | FILTER | | |
|* 3 | FILTER | | |
| 4 | NESTED LOOPS OUTER | | 1 |
| 5 | NESTED LOOPS | | 2 |
|* 6 | FILTER | | |
| 7 | NESTED LOOPS OUTER | | 2 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| HI_HPL_MED_ORD | 3 |
|* 9 | INDEX RANGE SCAN | IDX_HI_HPL_MED_ORD_DT_SIGN | 3 |
| 10 | TABLE ACCESS BY INDEX ROWID | HI_VIS_MED_IP | 1 |
|* 11 | INDEX UNIQUE SCAN | SYS_C0038438 | 1 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | HI_HPL_MED_ORD_CONSL_PHY | 1 |
|* 13 | INDEX RANGE SCAN | IDX_HI_HPL_MED_ORD_CONSL_PHY_M | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | HI_HPL_MED_ORD_CONSL | 1 |
|* 15 | INDEX UNIQUE SCAN | SYS_C0037052 | 1 |
对比这个执行计划可以现 e-rows 时间索引出现了明显的评估错误。
原因分析：由于语句中有绑定变量，优化器错误使用了历史变量生成的执行计划，导致对 e-rows 时间索引范围返回值评估错误。
优化方法
一、检查其它几个正常的执行计划，发现均未使用索引， IDX_HI_HPL_MED_ORD_DT_SIGN 。 如果业务方便修改 Sql 语句，可以在 sql 语句中指定 hint /*+ NO_INDEX(hihplmedor2_ IDX_HI_HPL_MED_ORD_DT_SIGN) */ ，提示优化器不使用此索引。 二、使用更灵活的 SQL Plan Management 来稳定执行计划。
步骤如下：
1 ） 装载指定sql_id 的所有 plan_hash_value
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'a13tu7zzjj1hd');
END;
/
2 ）查看手工与自动捕获的基线
SQL> set line 300 pagesize 999
SQL> select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,FIXED,AUTOPURGE,SQL_TEXT,EXECUTIONS,ELAPSED_TIME from dba_sql_plan_baselines where sql_text like '%HI_HPL_MED_ORD_CONSL_PHY%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX AUT
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------------------------- --- --- --- ---
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXECUTIONS ELAPSED_TIME
---------- ------------
SQL_475be56bda1d258b SQL_PLAN_4fqz5dgd1u9cb8ec2cd38 MANUAL-LOAD-FROM-CURSOR-CACHE YES YES NO YES
select count(*) as col_0_0_ from HI_HPL_MED_ORD_CONSL_PHY hihplmedor0_ left outer join hi_hpl_med_ord_consl hihplmedor1_ on (hihplmedor0_.id_medord=hihplmedor1_.id_medord) left outer join HI_HPL_MED_ORD hihplmedor2_ on (hihplmedor0_.id_medord=hihplmedor2_.id_medord) left outer join hi_pi hipi3_ on (
hihplmedor2_.id_pi=hipi3_.id_pi) left outer join hi_vis_med_ip hivismedip4_ on (hivismedip4_.id_vismed=hihplmedor2_.id_vismed) where 1=1 and hihplmedor2_.dt_medordsign>=to_date(:1 , 'yyyy-MM-dd hh24:mi:ss') and hihplmedor2_.dt_medordsign<=to_date(:2 , 'yyyy-MM-dd hh24:mi:ss') and (hihplmedor1_.sd_me
dordconsl_cd<>'3' and (hihplmedor0_.id_dep_consl=:3 and (hihplmedor0_.id_emp_consl_app is null) or hihplmedor0_.id_emp_consl_app=:4 ) or hihplmedor1_.sd_medordconsl_cd='3' and hihplmedor1_.id_dep_medord=:5 ) and (hihplmedor2_.sd_vistp_cd='114' and hivismedip4_.sd_ip_medtp_cd<'2' or hihplmedor2_.sd_
vistp_cd<>'114') order by hihplmedor2_.dt_medordsign desc nulls last
12286 1.0764E+10
SQL_475be56bda1d258b SQL_PLAN_4fqz5dgd1u9cb4118080e AUTO-CAPTURE YES NO NO YES
select count(*) as col_0_0_ from HI_HPL_MED_ORD_CONSL_PHY hihplmedor0_ left outer join hi_hpl_med_ord_consl hihplmedor1_ on (hihplmedor0_.id_medord=hihplmedor1_.id_medord) left outer join HI_HPL_MED_ORD hihplmedor2_ on (hihplmedor0_.id_medord=hihplmedor2_.id_medord) left outer join hi_pi hipi3_ on (
hihplmedor2_.id_pi=hipi3_.id_pi) left outer join hi_vis_med_ip hivismedip4_ on (hivismedip4_.id_vismed=hihplmedor2_.id_vismed) where 1=1 and hihplmedor2_.dt_medordsign>=to_date(:1 , 'yyyy-MM-dd hh24:mi:ss') and hihplmedor2_.dt_medordsign<=to_date(:2 , 'yyyy-MM-dd hh24:mi:ss') and (hihplmedor1_.sd_me
dordconsl_cd<>'3' and (hihplmedor0_.id_dep_consl=:3 and (hihplmedor0_.id_emp_consl_app is null) or hihplmedor0_.id_emp_consl_app=:4 ) or hihplmedor1_.sd_medordconsl_cd='3' and hihplmedor1_.id_dep_medord=:5 ) and (hihplmedor2_.sd_vistp_cd='114' and hivismedip4_.sd_ip_medtp_cd<'2' or hihplmedor2_.sd_
vistp_cd<>'114') order by hihplmedor2_.dt_medordsign desc nulls last
0 0
SQL_475be56bda1d258b SQL_PLAN_4fqz5dgd1u9cbab870b41 AUTO-CAPTURE YES NO NO YES
select count(*) as col_0_0_ from HI_HPL_MED_ORD_CONSL_PHY hihplmedor0_ left outer join hi_hpl_med_ord_consl hihplmedor1_ on (hihplmedor0_.id_medord=hihplmedor1_.id_medord) left outer join HI_HPL_MED_ORD hihplmedor2_ on (hihplmedor0_.id_medord=hihplmedor2_.id_medord) left outer join hi_pi hipi3_ on (
hihplmedor2_.id_pi=hipi3_.id_pi) left outer join hi_vis_med_ip hivismedip4_ on (hivismedip4_.id_vismed=hihplmedor2_.id_vismed) where 1=1 and hihplmedor2_.dt_medordsign>=to_date(:1 , 'yyyy-MM-dd hh24:mi:ss') and hihplmedor2_.dt_medordsign<=to_date(:2 , 'yyyy-MM-dd hh24:mi:ss') and (hihplmedor1_.sd_me
dordconsl_cd<>'3' and (hihplmedor0_.id_dep_consl=:3 and (hihplmedor0_.id_emp_consl_app is null) or hihplmedor0_.id_emp_consl_app=:4 ) or hihplmedor1_.sd_medordconsl_cd='3' and hihplmedor1_.id_dep_medord=:5 ) and (hihplmedor2_.sd_vistp_cd='114' and hivismedip4_.sd_ip_medtp_cd<'2' or hihplmedor2_.sd_
vistp_cd<>'114') order by hihplmedor2_.dt_medordsign desc nulls last
0 0
3） 查看几个 SQL_PLAN_BASELINE 的执行计划，找出较优的
SET LONG 10000
SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_4fqz5dgd1u9cb4118080e'));
4 ）演化执行计划较优的 SQL_PLAN_BASELINE
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
REPORT CLOB;
BEGIN
REPORT := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_475be56bda1d258b',plan_name => 'SQL_PLAN_ 4fqz5dgd1u9cb4118080e' ,time_limit => 10,verify => 'yes',commit => 'yes');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
5 ）修改较优的执行计划为 'fixed'
SET SERVEROUTPUT ON
DECLARE
v_text PLS_INTEGER;
BEGIN
v_text := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_475be56bda1d258b',
plan_name => 'SQL_PLAN_4fqz5dgd1u9cb4118080e',
attribute_name => 'fixed',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || v_text );
END;
/
SPM 完成后，相同的 sql 系统将从 plan history 中选择被标记为 ENABLED,ACCEPTED,FIXED 的执行计划使用。
