[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 就很好,而不是使用绑定变量.
--//其中细节给大家慢慢体会.