[20240306]奇怪的高逻辑读.txt

[20240306]奇怪的高逻辑读.txt --//生产系统遇到一条sql语句出现高逻辑读的情况，做一个记录并且分析。 1.环境： SYS@127.0.0.1:9014/ywdb> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@127.0.0.1:9014/ywdb> @ sql_id 1f52yqf7s5bs3 --SQL_ID = 1f52yqf7s5bs3 Select sum ( zjje ) From zy_fymx Where yzxh =:1 And zyh =:2 ; 2.分析： SYS@127.0.0.1:9014/ywdb> @ sqlhh 1f52yqf7s5bs3 100 time unit : millisecond BEGIN_INTERVAL_TIME    INST_ID SQL_ID        PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC  AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC ------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- --------------- 2024-03-05 16:00:23          1 1f52yqf7s5bs3      3005060615          5           73483            9441           1.0       1202877          41571           51731         1.2           15635               0               0 2024-03-05 16:00:23          2 1f52yqf7s5bs3      3005060615          2          111427            8610           0.5        250879          57754           98004         1.7            7547               0               0 2 rows selected. --//注：参数2=100,表示查询100天的历史记录(实际上设置仅仅保留40天)，视图dba_hist_sqlstat。 --//仅仅在awr记录几次逻辑读很高的情况，出现在昨天下午16－17点。 SYS@127.0.0.1:9014/ywdb> @ dpc 1f52yqf7s5bs3 '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  1f52yqf7s5bs3, child number 0 ------------------------------------- Select sum ( zjje ) From zy_fymx Where yzxh =:1 And zyh =:2 Plan hash value: 3005060615 ------------------------------------------------------------------------------------------------ | Id  | Operation                    | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT             |                |        |       |     5 (100)|          | |   1 |  SORT AGGREGATE              |                |      1 |    17 |            |          | |*  2 |   TABLE ACCESS BY INDEX ROWID| ZY_FYMX        |      1 |    17 |     5   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | I_ZY_FYMX_YZXH |      4 |       |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / ZY_FYMX@SEL$1    3 - SEL$1 / ZY_FYMX@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - (NUMBER): 2010583299    2 - (NUMBER): 20062047 Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("ZYH"=:2)    3 - access("YZXH"=:1) Note -----    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level --//执行计划正常,没有出现隐式转换. SYS@127.0.0.1:9014/ywdb> @ desczz pppppp_hhh.zy_fymx yzxh,zyh eXtended describe of pppppp_hhh.zy_fymx DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner      Table_Name           SAMPLE_SIZE LAST_ANALYZED       Col# Column Name          Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value High_value ---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- --------- ----------- pppppp_hhh ZY_FYMX                 27324487 2023-12-11 10:58:41    2 ZYH                  NOT NULL   NUMBER(18,0)                58240   .00001717033          0                           1 670168    40025105                                    27014089 2023-12-11 10:58:41   23 YZXH                            NUMBER(18,0)              7678464   .00000013023     310398                           1 0         2009800527 2 rows selected.         --//注：YZXH表示医嘱序号，zyh表示住院号，选择使用YZXH字段索引理论讲效率较高(Density更小). SYS@127.0.0.1:9014/ywdb> @ bind_cap 1f52yqf7s5bs3 '' SQL_ID        CHILD_NUMBER WAS NAME                                       POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING ------------- ------------ --- ---------------------------------------- ---------- ---------- ------------------- --------------- ------------- 1f52yqf7s5bs3            0 YES :1                                                1         22 2024-03-06 09:18:52 NUMBER          2010665119                            YES :2                                                2         22 2024-03-06 09:18:52 NUMBER          20062485 --//我尝试带入以上参数执行逻辑读并没有这么高,最多5,6个逻辑读。为什么昨天出现异常情况，理论讲这条语句不会出现awr报表中， --//平时执行逻辑读以及次数都不是很高,查看历史绑定变量的值： SYS@127.0.0.1:9014/ywdb> @ bind_cap_awr 1f52yqf7s5bs3 '' @bind_cap_awr sql_id [column] 1 row selected.    SNAP_ID INSTANCE_NUMBER SQL_ID        WAS LAST_CAPTURED       NAME   POSITION MAX_LENGTH DATATYPE_STRING VALUE_STRING ---------- --------------- ------------- --- ------------------- ---- ---------- ---------- --------------- ------------      32574               2 1f52yqf7s5bs3 YES 2024-03-05 16:14:02 :1            1         22 NUMBER          0                          2                                       :2            2         22 NUMBER          978498                          1                   2024-03-05 16:46:08 :1            1         22 NUMBER          2010529840                          1                                       :2            2         22 NUMBER          20061723 --//噢，参数1有带入0的情况。导致出现查询使用yzxh索引返回记录太多，再通过过滤zyh=:2效率很低。 SYS@127.0.0.1:9014/ywdb> @ cntgx pppppp_hhh.zy_fymx yzxh yzxh=0 select count(*) , yzxh  from pppppp_hhh.zy_fymx where yzxh=0 group by yzxh order by 1 desc;   COUNT(*)       YZXH ---------- ----------    3103564          0 SYS@127.0.0.1:9014/ywdb> @ cnt pppppp_hhh.zy_fymx select count(*) from pppppp_hhh.zy_fymx; COUNT(*) ----------   29728335 --//3103564/29728335 = .1044 占10%的记录。没有办法很好的解决这个问题。 3.总结： --//1.通过sql profile来使用zyh索引，但是大部分查询yzxh>0的，而且大于0的选择性很好。这样导致大部分的情况下执行效率偏低。 --//2.也可以通过sql profile来使用zyh索引以及yzxh索引,走位图与,这样情况与方法1类似,也许逻辑读小一些.对于yzxh=0的情况效率 --//比方法1要低. --//3.建立复合索引YZXH+zyh，磁盘空间有点浪费,感觉有点多余。 --//4.当使用参数0时，不要使用绑定变量，这样要修改程序代码，并且在这个字段YZXH上要建立直方图。 --//总之，在我看来方法4最好，这样可以兼顾各种情况。向这种类似的修改提交开发基本选择不愿意改动代码。所以我为什么认为国内 --//的许多IT项目都是豆腐渣的原因.