[20221012]修改统计信息优化sql语句.txt

[20221012]修改统计信息优化sql语句.txt --//生产系统一类sql语句出现一点点性能问题,选择错误的索引,通过修改统计信息优化sql语句来优化sql语句. 1.环境: SYS@192.168.100.235:1521/orcl> @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.分析: --//语句类似如下,存在两种风格的update语句.一类包含barcode,一类没有而是包含barcode. UPDATE LIS_RESULT_TEMP    SET in_flag = 1, in_time = :in_time  WHERE     barcode = :barcode  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~        AND inst_id = :inst_id        AND in_flag = 0        AND write_time >= :write_time        AND Item_Channel IN ( :Item_Channel1                            , :Item_Channel2 ...                            , :Item_Channel104                            , :Item_Channel105                            , :Item_Channel106) UPDATE LIS_RESULT_TEMP    SET in_flag = 1, in_time = :in_time  WHERE     test_no = :test_no        AND inst_id = :inst_id        AND test_date = :test_date        AND in_flag = 0        AND write_time >= :write_time        AND Item_Channel IN ( :Item_Channel1                            , :Item_Channel2 ...                            , :Item_Channel49) --//查询建立索引如下: SYS@192.168.100.235:1521/orcl> @ ind2 lis.LIS_RESULT_TEMP Display indexes where table or index name matches lis.LIS_RESULT_TEMP... TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC -------------------- ------------------------------ ------------------------------ ---- ------------------------------ ---- LIS                  LIS_RESULT_TEMP                IX_LIS_RESULT_TEMP_BARCODE        1 BARCODE                                                     IX_LIS_RESULT_TEMP_T_3            1 TEST_NO                                                                                       2 TEST_DATE                                                                                       3 INST_ID                                                     IX_LIS_RESULT_TEMP_WRITE_TIME     1 WRITE_TIME                                                     I_LIS_RESULT_TEMP_IN_FLAG         1 IN_FLAG                                                     PK_LIS_RESULT_TEMP                1 ID INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT -------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ --------- LIS                  LIS_RESULT_TEMP                IX_LIS_RESULT_TEMP_BARCODE     NORMAL     NO   VALID    NO   N     3      12618         61224    1575217     143148 2022-10-11 22:00:25 1      VISIBLE                      LIS_RESULT_TEMP                IX_LIS_RESULT_TEMP_T_3         NORMAL     NO   VALID    NO   N     3      10539         19208    1820170     137855 2022-10-11 22:00:31 1      INVISIBLE                      LIS_RESULT_TEMP                IX_LIS_RESULT_TEMP_WRITE_TIME  NORMAL     NO   VALID    NO   N     3       7361         72944    1820118     132620 2022-10-11 22:00:35 1      INVISIBLE                      LIS_RESULT_TEMP                I_LIS_RESULT_TEMP_IN_FLAG      NORMAL     NO   VALID    NO   N     3       3245             2    1820159      35682 2022-10-11 22:00:39 1      VISIBLE                      LIS_RESULT_TEMP                PK_LIS_RESULT_TEMP             NORMAL     YES  VALID    NO   N     3       4327       1820170    1820170     245835 2022-10-11 22:00:38 1      VISIBLE --//注:IN_FLAG 索引是我事后建立的. --//我发现即使一些包含字段BARCODE的等值语句,有一些选择barcode索引,有一些选择write_time. --//而没有包含字段BARCODE的语句,会选择IX_LIS_RESULT_TEMP_T_3索引,有一些选择write_time. --//除了选择BARCODE效果很好外,其它情况的逻辑读都很高,达到18XX.特别到了下午晚上的情况更加严重,因为test_date,WRITE_TIME数 --//据随着当前业务的累加,记录会越来越多. --//LIS_RESULT_TEMP 从字面可以看出是报错结果的"临时"表.修改后in_flag从0=>1.看看IN_FLAG的数据分布. SYS@192.168.100.235:1521/orcl> @ tabhist lis.LIS_RESULT_TEMP IN_flag COLUMN_NAME                    DATA_TYPE            HISTOGRAM       SAMPLE_SIZE ENDPOINT_NUMBER                 ENDPOINT_VALUE  FREQUENCY HEIGHT_BAL ENDPOINT_ACTUAL_VALUE ------------------------------ -------------------- --------------- ----------- --------------- ------------------------------ ---------- ---------- ---------------------------------------- IN_FLAG                        NUMBER               FREQUENCY           1820118              15                              0         15            0                                                     FREQUENCY           1820118         1820118                              1    1820103            1 --//很明显建立IN_FLAG可能效果不错,另外在业务高峰时,IN_FLAG的数量可以达到4XX. --//于是我临时设置IX_LIS_RESULT_TEMP_T_3,IX_LIS_RESULT_TEMP_WRITE_TIME 索引INVISIBLE(不可用). --//刷新共享池观察,发现字段BARCODE的等值语句,有一些选择barcode索引,有一些选择IN_FLAG索引.不知道是否没有刷新共享池的原因. --//好像多数选择IN_FLAG索引.我希望发现字段BARCODE的等值语句,最好还是选择barcode的索引. --//一般情况下我会选择sql profile来稳定执行计划,但是里面的项目数量Item_Channel会变,使用sql profile明显不现实. --//我突然想到应该通过修改barcode的Density来控制执行计划: SYS@192.168.100.235:1521/orcl> @ descz lis.LIS_RESULT_TEMP "column_name in ('IN_FLAG','BARCODE')" eXtended describe of lis.LIS_RESULT_TEMP DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner      Table_Name           SAMPLE_SIZE LAST_ANALYZED       Col# Column Name          Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value  High_value ---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------- ------------ LIS        LIS_RESULT_TEMP          1575217 2022-10-11 22:00:11    3 BARCODE                         NVARCHAR2(28)               61224   .00001633346     244953                           1 0150860102 _HIV Ag/AbL4                                     1820118 2022-10-11 22:00:11   24 IN_FLAG                         NUMBER(1,0)                     2   .00000027471         52 FREQUENCY                 2 0          1 2 rows selected. --//这样只要查询包含字段BARCODE的等值查询,优先选择PAT_BARCODE索引,整个执行计划就可以很好的控制. --//注意看Density列的值.我把barcode的Density设置低一些就ok了. SYS@192.168.100.235:1521/orcl> exec dbms_stats.SET_COLUMN_STATS('LIS','LIS_RESULT_TEMP','BARCODE',DENSITY=>.0000001,NO_INVALIDATE=>false); PL/SQL procedure successfully completed. SYS@192.168.100.235:1521/orcl> @ descz lis.LIS_RESULT_TEMP "column_name in ('IN_FLAG','BARCODE')" eXtended describe of lis.LIS_RESULT_TEMP DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner      Table_Name           SAMPLE_SIZE LAST_ANALYZED       Col# Column Name          Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value  High_value ---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------- ------------ LIS        LIS_RESULT_TEMP          1575217 2022-10-12 09:15:45    3 BARCODE                         NVARCHAR2(28)               61224   .00000010000     244953                           1 0150860102 _HIV Ag/AbL4                                     1820118 2022-10-11 22:00:11   24 IN_FLAG                         NUMBER(1,0)                     2   .00000027471         52 FREQUENCY                 2 0          1 2 rows selected. --//刷新共享池观察,可以发现基本实现我的优化目的.查询共享池发现最大的逻辑读13X. 3.lock表,避免以后的分析修改该表的统计信息: SYS@192.168.100.235:1521/orcl> exec dbms_stats.LOCK_TABLE_STATS('LIS','LIS_RESULT_TEMP'); PL/SQL procedure successfully completed. SYS@192.168.100.235:1521/orcl> @ cs lis alter session set current_schema=lis Session altered. SYS@192.168.100.235:1521/orcl> @ gts LIS_RESULT_TEMP Gather Table Statistics for table LIS_RESULT_TEMP... BEGIN dbms_stats.gather_table_stats(null, upper('LIS_RESULT_TEMP'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 40751 ORA-06512: at "SYS.DBMS_STATS", line 40035 ORA-06512: at "SYS.DBMS_STATS", line 9393 ORA-06512: at "SYS.DBMS_STATS", line 10317 ORA-06512: at "SYS.DBMS_STATS", line 39324 ORA-06512: at "SYS.DBMS_STATS", line 40183 ORA-06512: at "SYS.DBMS_STATS", line 40732 ORA-06512: at line 1 SYS@192.168.100.235:1521/orcl> @ cs sys alter session set current_schema=sys Session altered. --//已经实现lock表功能. 4.观察一段时间看看: --//IX_LIS_RESULT_TEMP_T_3,IX_LIS_RESULT_TEMP_WRITE_TIME索引可能根本不需要.