[20221207]為什麼FORCE_MATCHING_SIGNATURE不一致.txt

[20221207]為什麼FORCE_MATCHING_SIGNATURE不一致.txt

--//生產系統最佳化轉移到備庫,沒有想到開發或者同事把許多查詢移到到備庫完成,當時慢並不能做為移動的理由.實際上移動到備庫也快
--//不到那裡.繼續最佳化工作:

1.環境:
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
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.分析:
--//在2022-11-25 09:48:45 執行的.
SYS@192.168.100.237:1521/orcldg> @ ashtop sql_id 1=1 &100day
    Total                                                                         Distinct Distinct
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
    30225      .0   38% |               2022-11-24 11:11:46 2022-11-25 09:48:45          1    14391
    13999      .0   18% | 1h98kpy1s770r 2022-11-24 13:37:50 2022-11-24 14:58:35          4     4846
    13181      .0   17% | 623b841u978k2 2022-11-24 11:11:46 2022-11-25 09:48:44       8630    11853
     9755      .0   12% | bu48z014njcg4 2022-11-24 11:12:26 2022-11-25 09:47:59       9733     5056
     1159      .0    1% | f9pnxajd6cykm 2022-11-25 09:29:29 2022-11-25 09:48:48          1     1159
      738      .0    1% | 686sth2hr8783 2022-11-24 11:11:45 2022-11-24 11:24:01          2      737
      735      .0    1% | 0h17fpc351rp0 2022-11-24 11:13:23 2022-11-25 09:48:48        602      729
      553      .0    1% | 2tfpup517t7c5 2022-11-25 09:20:05 2022-11-25 09:29:18          2      553
      361      .0    0% | a91d0rd8qvu21 2022-11-24 11:12:18 2022-11-25 09:46:28        174      361
      347      .0    0% | 0wu7fhhrf27c8 2022-11-24 11:11:46 2022-11-24 11:18:34         97      347
      341      .0    0% | 71k88tu1yx8j6 2022-11-25 09:37:53 2022-11-25 09:41:13          2      200
      222      .0    0% | 0sa1kg1c4d53w 2022-11-25 09:37:30 2022-11-25 09:41:12          1      222
      200      .0    0% | 4yw3h0fnhbpbb 2022-11-24 15:11:45 2022-11-24 15:14:30          2      166
      192      .0    0% | 8ss7js42xzp05 2022-11-24 11:16:41 2022-11-25 09:42:03        192      192
      178      .0    0% | 2s7mc407pazjb 2022-11-24 11:12:15 2022-11-25 09:47:44        159      177
      178      .0    0% | f454ryjfx6syf 2022-11-24 11:11:47 2022-11-25 09:47:10        178      178
      166      .0    0% | 9pp9vzjw7k7r8 2022-11-24 15:11:45 2022-11-24 15:14:30          1      166
      158      .0    0% | 17pamqymtc0cu 2022-11-25 09:14:52 2022-11-25 09:17:29          1      158
      127      .0    0% | c3jafyjuwt13b 2022-11-24 11:16:36 2022-11-25 09:46:58        127      127
      125      .0    0% | f6d0fpgm1w2sw 2022-11-24 11:31:41 2022-11-25 09:47:04        125      125
      124      .0    0% | d946h5sr5gt69 2022-11-24 11:26:44 2022-11-25 09:47:06        124      124
      121      .0    0% | 0tha0zcyf9maq 2022-11-24 11:21:39 2022-11-25 09:47:02        121      121
      120      .0    0% | 2sq2bmkwuz6at 2022-11-24 11:31:42 2022-11-25 09:47:05        120      120
      120      .0    0% | 6sbq34x7ckff7 2022-11-24 11:16:38 2022-11-25 09:47:01        120      120
      119      .0    0% | fpamfm2pkznu1 2022-11-24 11:31:44 2022-11-25 09:42:07        119      119
      117      .0    0% | 0zrwxj39q7u3w 2022-11-24 11:11:45 2022-11-25 09:42:08        117      117
      117      .0    0% | 3tjcpzm0fkkf7 2022-11-24 11:22:19 2022-11-25 09:42:09        114      117
      113      .0    0% | 5ub6g7qwaf35x 2022-11-24 11:16:37 2022-11-25 09:41:59        113      113
      113      .0    0% | az4ju0qgum193 2022-11-24 11:16:40 2022-11-25 09:42:02        113      113
      113      .0    0% | czkw1ncpthxy4 2022-11-24 11:21:37 2022-11-25 09:47:00        113      113
30 rows selected.

--//Distinct  Execs Seen 基本可以看成執行次數.可以發現好幾條執行次數很少,當我查詢時發現這些語句都無法查詢到.理論講dg執行
--//語句很少,查詢不到,基本是從共享池消失,可以導致確定這些語句沒有使用繫結變數,而且執行很慢.比如1h98kpy1s770r僅僅執行4次,
--//耗費13999秒.平均每次13999/4 = 3499秒.注意後面的Distinct Tstamps=4846,也就是查詢沒有完成,另外又開啟一個程式再次執行的
--//情況,或者想象操作員等不及,退出了程式,但是語句還在後臺執行出現的情況.我估計操作員再次修改時間範圍,結果查詢要麼快了要
--//麼更慢,實際上的情況與我前幾天看到的一樣,執行選擇優先選擇了日期索引,而日期的查詢範圍相對很大比如幾天,而且連線選擇巢狀
--//執行,慢是很自然的.

3.在toad介面下查詢,按照buffer_gets/executions降序排序:

--//我發現幾條執行很少的sql語句,抽取出兩條sql語句,格式化儲存為文字,使用diff比較.發現如下:
R:\>diff a1.txt a2.txt
60c60
<        AND a.pat_name LIKE '陳XX婷%'
---
>        AND a.pat_name LIKE '黃YY開%'

--//語句大部分都一樣,僅僅查詢的病人名字不同.
--//我開始想這樣簡單了我按照前面的最佳化思路最佳化一條語句,然後使用我寫的spsw.sql指令碼交換執行計劃就ok了.
--//完成後我發現不行,我只要換一個病人名字查詢就出現很慢的情況,也就是這樣的情況我無法透過sql profile穩定執行計劃.

--//檢視對應語句的EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE:
SYS@192.168.100.237:1521/orcldg> SELECT sql_id
     , length(sql_fulltext), EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE
  FROM v$sqlarea
 WHERE sql_id in ('g7qf8ub9jx093','b73a4k20wbjtj');
SQL_ID        LENGTH(SQL_FULLTEXT) EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- -------------------- ------------------------ ------------------------
g7qf8ub9jx093                 1026     16687802159211697548     16687802159211697548
b73a4k20wbjtj                 1026     16455271695327448147     16455271695327448147
2 rows selected.
--//發現EXACT_MATCHING_SIGNATURE=FORCE_MATCHING_SIGNATURE,上下的FORCE_MATCHING_SIGNATURE並不相等.
--//參考連結 [20180301]FORCE_MATCHING_SIGNATURE的計算.txt =>http://blog.itpub.net/267265/viewspace-743928/
--//當sql語句存在繫結變數與常量混合時,計算EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE的方法一樣.

--//摘要當時的測試結果:
--//注意幾個細節,計算時裡面提示的空格取消了,變數與等號之間存在空格.小寫變成了大寫.我當時的結論:
--//http://blog.itpub.net/267265/viewspace-743928/
有如下的結論：對SQL語句，去掉重複的空格(不包括字元常量)，將大小寫轉換成相同，比如均為大寫(不包括字元常量)後，如果
SQL相同，那麼SQL語句的exact_matching_signature就是相同的。對SQL語句，去掉重複的空格(不包括字元常量)，將大小寫轉換成相同，
比如均為大寫(不包括字元常量)，然後去掉SQL中的常量，如果SQL相同，那麼SQL語句的force_matching_signature就是相同的。但是例
外的情況是：如果SQL中有繫結變數，force_matching_signature就會與exact_matching_signature一樣的生成標準。

4.剩下問題,這樣如何最佳化呢?
--//很明顯採用原來sql profile方式不行.問題又如何解決呢?

--//1.定期手工分析對應表,這樣oracle就不會該日期範圍內資料很少,優先選擇該日期索引. 缺點這些表已經很大,分析即使自動取樣也
--//  非常浪費時間.

--//2.建立新索引將pat_name,查詢日期作為複合索引,不過這裡不行,因為pat_name與查詢日期欄位對應不同的表.也就是這個方法不可行.

--//3.在system級別上設定cursor_sharing=force,這樣類似語句的FORCE_MATCHING_SIGNATURE完全一致,使用sql profile穩定計劃變得
--//  很簡單了.我最終採用使用這樣的方法.

--//4.修改程式程式碼,使用繫結變數,其實該應用程式繫結變數做的算很好的,這麼一條類似語句就導致執行慢,真的有點像一顆老鼠屎搞壞
--//  一鍋湯.

5.總結:
--//做到這裡,我感覺我突然明白oracle在當sql語句存在繫結變數與常量混合時,選擇這樣的計算
--//EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE,
--//也就是合理使用繫結變數,比如某表status=0很少,status=1很多.
--//如果查詢包括 status欄位,直接使用status=0 或者 status =1 就很好,而不是使用繫結變數.
--//其中細節給大家慢慢體會.