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

Oracle在解析SQL语句的时候，如果在共享池中发现匹配的SQL语句，就可以避免掉解析的大部分开销。在共享池中找到匹配的SQL语句所对应的解析被称为软解析(softparse)。如果没有找到匹配的SQL语句，则必须进行硬解析（hardparse）。硬解析不仅耗费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语句。withforce_mathcesas(selectl.force_matching_signaturemathces,max(l.sql_id||l.child_number)max_sql_child,dense_rank()over(orderbycount(*)desc)rankingfromv$sqllwherel.force_matching_signature0andl.parsing_schema_name'SYS'groupbyl.force_matching_signaturehavingcount(*)10)selectv.sql_id,v.sql_text,v.parsing_schema_name,fm.mathces,fm.rankingfromforce_mathcesfm,v$sqlvwherefm.max_sql_child=(v.sql_id||v.child_number)andfm.ranking=5orderbyfm.ranking;3.通过执行动态SQL语句获取绑定变量的好处通过执行动态SQL语句，比较字面量和绑定参数对SQL解析的影响。declarev_enameemp.ename%type;v_salemp.sal%type;v_sqlclob;begindbms_output.put_line('*********使用字面量************');forvrt_empin(select*fromemp)loopv_sql:='selecte.ename,e.salfromempewheree.empno='||vrt_emp.empno;executeimmediatev_sqlintov_ename,v_sql;dbms_output.put_line(v_ename||':'||v_sql);endloop;dbms_output.put_line('');dbms_output.put_line('*********使用绑定变量************');forvrt_empin(select*fromemp)loopv_sql:='selecte.ename,e.salfromempewheree.empno=:empno';executeimmediatev_sqlintov_ename,v_sqlusingvrt_emp.empno;dbms_output.put_line(v_ename||':'||v_sql);endloop;end;查询v$sql视图，比较执行结果：SQLselectv.sql_text,v.sql_id,v.force_matching_signature2fromv$sqlv3wherev.sql_textlike'selecte.ename,e.salfromempewheree.empno%';SQL_TEXTSQL_IDFORCE_MATCHING_SIGNATURE-----------------------------------------------------------------------------------------------selecte.ename,e.salfromempewheree.empno=7782766syjydcn5fh1.27397653964533E19selecte.ename,e.salfromempewheree.empno=74996ymy4hcb386vt1.27397653964533E19selecte.ename,e.salfromempewheree.empno=79343t96y707p8by71.27397653964533E19selecte.ename,e.salfromempewheree.empno=7902f9pyzxf7tnuzw1.27397653964533E19selecte.ename,e.salfromempewheree.empno=7654fvk1fzmrvjc4j1.27397653964533E19selecte.ename,e.salfromempewheree.empno=7788gsmatg9f4jd2z1.27397653964533E19selecte.ename,e.salfromempewheree.empno=75664q9pzzpvvdpuu1.27397653964533E19selecte.ename,e.salfromempewheree.empno=73693xhqmvm5vdqy01.27397653964533E19selecte.ename,e.salfromempewheree.empno=7698bjjjw0gzaprzv1.27397653964533E19selecte.ename,e.salfromempewheree.empno=78768nd8v8mrzxw4w1.27397653964533E19selecte.ename,e.salfromempewheree.empno=75215tnyy066zfk1b1.27397653964533E19selecte.ename,e.salfromempewheree.empno=78444kd7jb013g2zz1.27397653964533E19selecte.ename,e.salfromempewheree.empno=7900grx9sh4fwrcwx1.27397653964533E19selecte.ename,e.salfromempewheree.empno=:empno20wmyr4cvrr6k3.49355109645567E18selecte.ename,e.salfromempewheree.empno=78396u2ajyu05gw9s1.27397653964533E19在v$sql视图中，发现使用字面量的SQL语句有14条，而使用绑定变量的SQL语句只有一条。其中使用字面量的SQL语句除以了字面量值不同之外，其他部分都是相同。而FORCE_MATCHING_SIGNATURE的值是在假设该SQL语句使用绑定变量或者CURSOR_SHARING得到的，因此通过FORCE_MATCHING_SIGNATURE字段识别没有绑定变量的SQL语句。