统计未用绑定变量的sql语句

最新推荐文章于 2020-07-06 12:22:22 发布

conglueh193642

最新推荐文章于 2020-07-06 12:22:22 发布

阅读量179

点赞数

文章标签：数据库

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语句。

---实践案例
select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE, count(1) as counts
from v$sql
where FORCE_MATCHING_SIGNATURE>0 and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2 desc;

create table tb_force(id integer,name varchar2(10));
insert into tb_force(id,name) values ('1','111');
insert into tb_force(id,name) values ('2','222');
insert into tb_force(id,name) values ('3','333');
commit;

SQL> show parameter cursor_sharing;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT

SQL> select /*mystatement*/ * from tb_force where id=1;

                                     ID NAME
--------------------------------------- ----------
                                      1 111
SQL> select /*mystatement*/ * from tb_force where id=2;

                                     ID NAME
--------------------------------------- ----------
                                      2 222
SQL> select /*mystatement*/ * from tb_force where id=3;

                                     ID NAME
--------------------------------------- ----------
                                      3 333

SQL> select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like '%mystatement%';

SQL_TEXT                                                                         FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
-------------------------------------------------------------------------------- ------------------------ ------------------------
select /*mystatement*/ * from tb_force where id=2                                    1.30958144015941E19      6.87046715690462E18
select /*mystatement*/ * from tb_force where id=1                                    1.30958144015941E19      5.16419277214989E17
select /*mystatement*/ * from tb_force where id=3                                    1.30958144015941E19      1.36147732398326E19
--id取不同值时，FORCE_MATCHING_SIGNATURE的值相同，而EXACT_MATCHING_SIGNATURE值不同，说明在cursor_sharing=force时，执行计划可共用；cursor_sharing=exact时，执行计划不可共用；实际

上我们使用绑定变量后，同样也可以达到执行计划共用的效果。

sys@ORCL> alter system set cursor_sharing=force;

System altered.

sys@ORCL> show parameter cursor_sharing;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE

sys@ORCL> alter system flush shared_pool;

System altered.

SQL> select /*mystatement*/ * from tb_force where id=1;

                                     ID NAME
--------------------------------------- ----------
                                      1 111

SQL> select /*mystatement*/ * from tb_force where id=2;

                                     ID NAME
--------------------------------------- ----------
                                      2 222

SQL> select /*mystatement*/ * from tb_force where id=3;

                                     ID NAME
--------------------------------------- ----------
                                      3 333

--通过上面的实验，我们发现查找系统中有哪些语句未使用绑定变量，可根据FORCE_MATCHING_SIGNATURE实现。

--下面我们就通过FORCE_MATCHING_SIGNATURE来查找系统中未使用绑定变量的语句...
select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE, count(1) as counts
from v$sql
where FORCE_MATCHING_SIGNATURE>0 and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2 desc;

--通过上面查询出来的FORCE_MATCHING_SIGNATURE值，来查找sql语句
select t.* from v$sql t where FORCE_MATCHING_SIGNATURE=16456394970215993993;

--延伸：
cursor_sharing有三个可选参数：EXACT、SIMILAR、FORCE。

--语句完全一样，则利用share pool中的语句，反之则重新生成(默认)。
EXACT：Only allows statements with identical text to share the same cursor.

--语句相似，但系统自行判断是否利用share pool中的语句(不推荐，bug很多)。
SIMILAR：Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or

the degree to which the plan is optimized.

--语句相似，系统强制利用share pool中的语句(不推荐，bug较多)。
FORCE：Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

参考连接：http://blog.csdn.net/orcldb/article/details/9041139

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29119536/viewspace-1243171/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29119536/viewspace-1243171/

conglueh193642

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
统计未用绑定变量的sql语句

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

扫一扫