[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项目都是豆腐渣的原因.