oracle 未绑定变量,查看Oracle数据库中未绑定变量的SQL

今天药忘吃喽~ 2023-01-19 07:08 11阅读 0赞

Oracle在解析SQL语句的时候，如果在共享池中发现匹配的SQL语句，就可以避免掉解析的大部分开销。在共享池中找到匹配的SQL语句所对应的解析被称为软解析(soft parse)。如果没有找到匹配的SQL语句，则必须进行硬解析(hard parse)。

硬解析不仅耗费CPU时间，在有大量会话想要同时缓存SQL语句到共享池时还会造成争用。通过使用绑定变量，可以最小化解析的代价。

1.CURSOR\_SHARING参数

该参数转换SQL语句中的字面值到绑定变量。转换值提高了游标共享，且可能会影响SQL语句的执行计划。优化器是基于绑定变量的存在生成执行计划，而不是实际字面量值。

CURSOR\_SHARING决定什么类型的SQL语句可以共享相同的游标。CURSOR\_SHARING参数有三个值：

FORCE：只要有可能，字面量就会被替换为绑定变量。

SIMILAR：只有当替换不会影响到执行计划时，才会将字面量替换为绑定变量

EXACT：这是默认值。不将字面量替换为绑定变量。

注意：不推荐修改CURSOR\_SHARING参数的默认值。如果实在无法修改现有应用的代码，可以通过设置CURSOR\_SHARING参数来指示Oracle透明地将字面量替换为绑定变量。

2.识别没有使用绑定变量的SQL语句

利用v$sql视图的FORCE\_MATCHING\_SIGNATURE字段，可以识别可能从绑定变量或CURSOR\_SHARING获益的SQL语句。如果SQL已使用绑定变量或者CURSOR\_SHARING,则FORCE\_MATCHING\_SIGNATURE在对其进行标识时将给出同样的签名。换句话说，如果两个SQL语句除了字面量的值之外都是相同的，它们将拥有相同的FORCE\_MATCHING\_SIGNATURE,这意味着如果为他们提供了绑定变量或者CURSOR\_SHARING,它们就成了完全相同的语句。

使用FORCE\_MATCHING\_SIGNATURE识别没有使用绑定变来的SQL语句。

with force\_mathces as

(select l.force\_matching\_signature mathces,

max(l.sql\_id || l.child\_number) max\_sql\_child,

dense\_rank() over(order by count(\*) desc) ranking

from v$sql l

where l.force\_matching\_signature <> 0

and l.parsing\_schema\_name <> 'SYS'

group by l.force\_matching\_signature

having count(\*) > 10)

select v.sql\_id, v.sql\_text, v.parsing\_schema\_name, fm.mathces, fm.ranking

from force\_mathces fm, v$sql v

where fm.max\_sql\_child = (v.sql\_id || v.child\_number)

and fm.ranking <= 5

order by fm.ranking;

3.通过执行动态SQL语句获取绑定变量的好处

通过执行动态SQL语句，比较字面量和绑定参数对SQL解析的影响。

declare

v\_ename emp.ename%type;

v\_sal   emp.sal%type;

v\_sql   clob;

begin

dbms\_output.put\_line('\*\*\*\*\*\*\*\*\*使用字面量\*\*\*\*\*\*\*\*\*\*\*\*');

for vrt\_emp in (select \* from emp) loop

v\_sql := 'select e.ename,e.sal from emp e where e.empno =' ||

vrt\_emp.empno;

execute immediate v\_sql

into v\_ename, v\_sql;

dbms\_output.put\_line(v\_ename || ':' || v\_sql);

end loop;

dbms\_output.put\_line('');

dbms\_output.put\_line('\*\*\*\*\*\*\*\*\*使用绑定变量\*\*\*\*\*\*\*\*\*\*\*\*');

for vrt\_emp in (select \* from emp) loop

v\_sql := 'select e.ename,e.sal from emp e where e.empno =:empno';

execute immediate v\_sql

into v\_ename, v\_sql

using vrt\_emp.empno;

dbms\_output.put\_line(v\_ename || ':' || v\_sql);

end loop;

end;

查询v$sql视图，比较执行结果：

SQL> select v.sql\_text, v.sql\_id, v.force\_matching\_signature

2    from v$sql v

3   where v.sql\_text like 'select e.ename,e.sal from emp e where e.empno %';

SQL\_TEXT                                                      SQL\_ID        FORCE\_MATCHING\_SIGNATURE

\---------------------------------------------------------   -------------- ------------------------

select e.ename,e.sal from emp e where e.empno =7782         766syjydcn5fh      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7499         6ymy4hcb386vt      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7934         3t96y707p8by7      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7902         f9pyzxf7tnuzw      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7654         fvk1fzmrvjc4j      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7788         gsmatg9f4jd2z      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7566         4q9pzzpvvdpuu      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7369         3xhqmvm5vdqy0      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7698         bjjjw0gzaprzv      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7876         8nd8v8mrzxw4w      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7521         5tnyy066zfk1b      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7844         4kd7jb013g2zz      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =7900         grx9sh4fwrcwx      1.27397653964533E19

select e.ename,e.sal from emp e where e.empno =:empno       20wmyr4cvrr6k      3.49355109645567E18

select e.ename,e.sal from emp e where e.empno =7839         6u2ajyu05gw9s      1.27397653964533E19

在v$sql视图中，发现使用字面量的SQL语句有14条，而使用绑定变量的SQL语句只有一条。其中使用字面量的SQL语句除以了字面量值不同之外，其他部分都是相同。而FORCE\_MATCHING\_SIGNATURE的值是在假设该SQL语句使用绑定变量或者CURSOR\_SHARING得到的，因此通过FORCE\_MATCHING\_SIGNATURE字段识别没有绑定变量的SQL语句。