[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索引可能根本不需要.