[20240313]奇怪的高逻辑读后续.txt

--//生产系统遇到一条sql语句出现高逻辑读的情况，前面我已经分析过在于带入的变量有等于的情况0，回表记录太多，导致逻辑读很高
--//的情况。突然想到我可以通过sql patch的方式加入提示bind_aware,测试看看。

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> @ 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>select count(*) , yzxh  from portal_his.zy_fymx where yzxh=0 group by yzxh order by 1 desc;

  COUNT(*)       YZXH
---------- ----------
   3103564          0

SYS@127.0.0.1:9014/ywdb> @ cnt portal_his.zy_fymx
select count(*) from portal_his.zy_fymx;
COUNT(*)
----------
  29728335

--//3103564/29728335 = .1044 占10%的记录,没有办法很好的解决这个问题。
--//我当时想到最好的方法是判断当参数1等于0时直接使用常量代替.很明显要开发改代码往往是不情愿做这件事情.

3.问题再现：
--//生成对应sql语句的执行脚本：
SYS@127.0.0.1:9014/ywdb> @ b9d 1f52yqf7s5bs3 0
conv_sys=1 convert SYS_B_N,conv_sys=0 not convert SYS_B_N,conv_sys=2 convert constant
@ b9d <sql_id> <conv_sys>
@ b9d 1f52yqf7s5bs3 0

variable N1 NUMBER
variable N2 NUMBER
begin
:N1 := 2010529840;
:N2 := 20061723;
null;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=PORTAL_HIS;
alter session set statistics_level=all;

Select sum ( zjje ) From zy_fymx Where yzxh =:N1 And zyh =:N2 ;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' '' ''
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;

--// 生成snap_id=32574,INSTANCE_NUMBER=2的赋值语句.
SYS@127.0.0.1:9014/ywdb> @ cr_b9d 1f52yqf7s5bs3 32574 2
@ cr_b9d  <sql_id> <snap_id> <inst_id>
@ cr_b9d  1f52yqf7s5bs3  32574  2
spool filename = 1f52yqf7s5bs3.sql9d_s32574_i2

variable N1 NUMBER
variable N2 NUMBER
begin
:N1 := 0;
:N2 := 978498;
null;
end;
/

SYS@127.0.0.1:9014/ywdb> @ 1f52yqf7s5bs3.sql9d_0
PL/SQL procedure successfully completed.
Plan hash value: 3005060615
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |       |     5 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |                |      1 |      1 |    17 |            |          |      1 |00:00:00.01 |       3 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| ZY_FYMX        |      1 |      1 |    17 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN          | I_ZY_FYMX_YZXH |      1 |      4 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------------------------
--//执行计划ok。

SYS@127.0.0.1:9014/ywdb> @ 1f52yqf7s5bs3.sql9d_s32574_i2
PL/SQL procedure successfully completed.

--//注解前面的赋值语句，改用新值测试：
$ cat 1f52yqf7s5bs3.sql9d_0
-- variable N1 NUMBER
-- variable N2 NUMBER
-- begin
-- :N1 := 2010529840;
-- :N2 := 20061723;
-- null;
-- end;
-- /
set termout off
set sqlblanklines on
alter session set current_schema=PORTAL_HIS;
alter session set statistics_level=all;

Select sum ( zjje ) From zy_fymx Where yzxh =:N1 And zyh =:N2 ;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' '' ''
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;

SYS@127.0.0.1:9014/ywdb> @ 1f52yqf7s5bs3.sql9d_0
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bkmw0c8k9cakf, child number 0
-------------------------------------
Select sum ( zjje ) From zy_fymx Where yzxh =:N1 And zyh =:N2
Plan hash value: 3005060615
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |       |     5 (100)|          |      1 |00:06:59.45 |    1517K|    277K|
|   1 |  SORT AGGREGATE              |                |      1 |      1 |    17 |            |          |      1 |00:06:59.45 |    1517K|    277K|
|*  2 |   TABLE ACCESS BY INDEX ROWID| ZY_FYMX        |      1 |      1 |    17 |     5   (0)| 00:00:01 |     60 |00:06:59.45 |    1517K|    277K|
|*  3 |    INDEX RANGE SCAN          | I_ZY_FYMX_YZXH |      1 |      4 |       |     3   (0)| 00:00:01 |   3135K|00:00:28.34 |   13376 |  10103 |
--------------------------------------------------------------------------------------------------------------------------------------------------
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
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "ZY_FYMX"@"SEL$1" ("ZY_FYMX"."YZXH"))
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 0
   2 - (NUMBER): 978498
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ZYH"=:N2)
   3 - access("YZXH"=:N1)
49 rows selected.

--//扫描索引就使用28秒,整个查询差不多7分钟完成,对于现在的硬件环境是相当慢的查询!!

4.优化：

--//首先在字段YZXH建立直方图：
BEGIN
   DBMS_STATS.gather_table_stats
   (
      'PORTAL_HIS'
     ,'ZY_FYMX'
     ,estimate_percent   => sys.DBMS_STATS.auto_sample_size
     ,method_opt         => 'FOR TABLE FOR ALL COLUMNS SIZE repeat for columns YZXH size 254'
     ,cascade            => TRUE
     ,no_invalidate      => FALSE
   )
end;
/

SYS@127.0.0.1:9014/ywdb> @ desczz portal_his.ZY_FYMX YZXH,zyh
eXtended describe of portal_his.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
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- --------- -----------
PORTAL_HIS ZY_FYMX                 30033494 2024-03-13 10:32:10    2 ZYH                  NOT NULL   NUMBER(18,0)                64076   .00001560647          0                           1 670168    40025105
                                       5448 2024-03-13 10:32:10   23 YZXH                            NUMBER(18,0)              8503296   .00000079333     343839 HEIGHT BALANCED         254 0         2010819598

SYS@127.0.0.1:9014/ywdb> @ sqlpatch bkmw0c8k9cakf bind_aware

input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_bkmw0c8k9cakf');
display sql path message , run @spext bkmw0c8k9cakf
PL/SQL procedure successfully completed.

SYS@127.0.0.1:9014/ywdb> @ spext bkmw0c8k9cakf
HINT       NAME                   DESCRIPTION                              LAST_MODIFIED
---------- ---------------------- ---------------------------------------- ------------------------------
bind_aware sqlpatch_bkmw0c8k9cakf                                          2024-03-13 10:39:03.000000

--//第1次测试，带入非0值,注意取消1f52yqf7s5bs3.sql9d_0的注解部分：
SYS@127.0.0.1:9014/ywdb> @ 1f52yqf7s5bs3.sql9d_0
Plan hash value: 3005060615
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |       |     5 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |                |      1 |      1 |    17 |            |          |      1 |00:00:00.01 |       3 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| ZY_FYMX        |      1 |      1 |    17 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN          | I_ZY_FYMX_YZXH |      1 |      3 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------------------------

--//第二次测试，带入0值,注意加入1f52yqf7s5bs3.sql9d_0的注解部分：：
SYS@127.0.0.1:9014/ywdb> @ 1f52yqf7s5bs3.sql9d_s32574_i2
PL/SQL procedure successfully completed.

SYS@127.0.0.1:9014/ywdb> @ 1f52yqf7s5bs3.sql9d_0
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bkmw0c8k9cakf, child number 1
-------------------------------------
Select sum ( zjje ) From zy_fymx Where yzxh =:N1 And zyh =:N2
Plan hash value: 3487623146
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |      1 |        |       |    90 (100)|          |      1 |00:00:00.01 |     127 |
|   1 |  SORT AGGREGATE              |                    |      1 |      1 |    17 |            |          |      1 |00:00:00.01 |     127 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| ZY_FYMX            |      1 |     51 |   867 |    90   (0)| 00:00:02 |     60 |00:00:00.01 |     127 |
|*  3 |    INDEX RANGE SCAN          | I_ZY_FYMX_ZYH_JSCS |      1 |    469 |       |     6   (0)| 00:00:01 |   1246 |00:00:00.01 |       9 |
---------------------------------------------------------------------------------------------------------------------------------------------
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
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "ZY_FYMX"@"SEL$1" ("ZY_FYMX"."ZYH" "ZY_FYMX"."JSCS"))
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 0
   2 - (NUMBER): 978498
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("YZXH"=:N1)
   3 - access("ZYH"=:N2)
Note
-----
   - SQL patch "sqlpatch_bkmw0c8k9cakf" used for this statement
53 rows selected.
--//生成新的子光标，选择了另外的索引，也就是这个方法可行!!

5.正式使用：

--//删除前面测试的情况，因为原始语句带入的绑定变量是:1,:2,而改写的脚本使用的绑定变量是:N1,:N2，sql_id 不同。

SYS@127.0.0.1:9014/ywdb> exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_bkmw0c8k9cakf');
PL/SQL procedure successfully completed.

SYS@127.0.0.1:9014/ywdb> @ sqlpatch 1f52yqf7s5bs3 bind_aware
input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_1f52yqf7s5bs3');
display sql path message , run @spext 1f52yqf7s5bs3
PL/SQL procedure successfully completed.
--//注：我写sqlpatch脚本有一点点小问题。原始脚本从v$sql取。因为语句当前不在共享池，从DBA_HIST_SQLTEXT取。
-- select sql_fulltext into v_sql from v$sql where sql_id='&1' and rownum=1;
   select sql_text into v_sql from DBA_HIST_SQLTEXT where sql_id='&1' and rownum=1;
--//有机会完善该语句,暂时先放一放.

SYS@127.0.0.1:9014/ywdb> @ spext 1f52yqf7s5bs3
HINT       NAME                   DESCRIPTION                              LAST_MODIFIED
---------- ---------------------- ---------------------------------------- ------------------------------
bind_aware sqlpatch_1f52yqf7s5bs3                                          2024-03-13 10:47:14.000000

SYS@127.0.0.1:9014/ywdb> @ colour 1f52yqf7s5bs3
add and remove awr
exec dbms_workload_repository.add_colored_sql('1f52yqf7s5bs3');
exec dbms_workload_repository.remove_colored_sql('1f52yqf7s5bs3')

query sys.wrm$_colored_sql
column owner format 99999999
select * from sys.wrm$_colored_sql where sql_id='1f52yqf7s5bs3';
select * from sys.wrm$_colored_sql ;
column owner format a20

SYS@127.0.0.1:9014/ywdb> exec dbms_workload_repository.add_colored_sql('1f52yqf7s5bs3');
PL/SQL procedure successfully completed.

--//我自己尝试在toad下测试,没有问题,剩下的等待时间的检验.