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