【知识拓展】Oracle 数据库扫描规则之关注未使用绑定变量的SQL

非功能之路 2021-06-10

2127

在之前的文章中我们曾经讨论过扫描规则之分区表、索引跳扫及SQL Profile等相关知识，今天我们来一起看一下未使用绑定变量相关规则的起源、检查方法及在测试过程中发现该类SQL后应提供何种修改建议。

什么是绑定变量？

绑定变量是将变量的数据通过一定的数据访问技术来和相应的实体捆绑在一起，也可看做是替代变量或占位符。使用了绑定变量的SQL，相当于预先生成一个解决方案，在执行语句的时候，提供使用的实际值, 把占位符替换为具体的值，通过改变变量的值来得到不同的结果。

示例如下：

不使用绑定变量：select * from table where uname='zhang';
使用绑定变量：select * from table where uname =:name ;

为什么扫描规则中要加入检查“未使用绑定变量的SQL”的规则呢？

了解了绑定变量的定义，我们现在来看下未使用绑定变量会带来的性能问题，主要有两点：

1、重复生成相同的执行计划

一条sql语句分为动态部分和静态部分，一般情况下，动态部分对执行计划的影响较小，对存在动态部分的相同sql语句生成的执行计划往往都是相同的，例如对于下面两条sql语句，虽然“uname”的实际值不同，但会生成相同的执行计划：

select* from table where uname='zhang'

select* from table where uname='wang'

SQL语句的执行过程一般为语法检查、分析、执行和返回结果。当一条SQL通过语法检查后，会在共享池里寻找是否有与其相同的语句：如果有则用已有的执行计划执行SQL语句，即软解析；如果没有找到，则对SQL语句作语法分析，生成执行计划，再执行SQL语句，即硬解析。

对于示例中的未使用绑定变量的语句，虽然他们的执行计划相同，但由于共享池里面的语句不同，导致发生了硬解析。很明显，硬解析比软解析多了语法分析步骤，而进行语法分析需要耗费较多的CPU等资源，同时也会增加SQL总体执行时间。

2、增加维护共享池SQL区

SQL语句经语法分析后，语句本身与分析的信息（即执行计划）都被装入到共享池SQL区中，如前所述，Oracle只对每个SQL语句翻译一次，在以后再次执行该语句时，只要该语句还在共享SQL区中，就可以避免对该语句重新进行语法分析，直接使用对应的执行计划对数据进行存取。

未使用绑定变量的语句被认为是不同的语句，如果一段程序未采用绑定变量的方式而又被大量调用，会导致共享池中的SQL语句增多，随着SQL数量过多，加速SQL语句老化而被被清理出共享池。共享池内部频繁维护，消耗大量的CPU和内存资源，增加IO，同时共享池内命中率下降，导致频繁发生硬解析，增加SQL执行时间。

此外，从系统安全角度看，不采用绑定变量的方式也可能会造成SQL注入漏洞。例如，在进行用户验证时，若不使用绑定变量，而是允许直接输入用户名和密码到数据库中查询。

例如：SELECT id FROM user WHEREusername='**' AND password='**';

若攻击者从客户端传的username为任意值（如test），password变量为“1′ or ‘1′=’1”，此时替换变量后的SQL变为：SELECT id FROM user WHERE username='test' AND password='1' or'1'='1'，则可以得到user表中的所有数据。

因此，对于在线事务处理系统，在测试过程中扫描出存在未使用绑定变量的SQL，作为重点关注项进行分析和整改，可以极大的改善数据库的性能，是一项非常必要的数据库扫描规则。

如何获取到未使用绑定变量的SQL？

Oracle10g以后v$SQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列，通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值, FORCE_MATCHING_SIGNATURE列可以帮助我们找出那些潜在可以共享的语句。例如，对于如下3条未使用绑定变量的类似SQL语句，虽然语句不同（即EXACT_MATCHING_SIGNATURE不同），但经过FORCE模式转换后的FORCE_MATCHING_SIGNATURE值相同，如图所示：