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

[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下测试,没有问题,剩下的等待时间的检验.