动态SQL的dbms_sql.bind_variable无法触发绑定变量窥探

samt007

newkid 发表于 2017-3-4 00:26
我回帖后才看到你又贴了一段，和我的建议稍有不同。
如果你要坚持原来的解析做法，就要把它解析成一段动态 ...

嗯，明白版主的建议。
主要是我开发的这个功能有点特殊，它是一个“依附”在所有查询功能的一个共用的导出xlsx的功能。
而所有的查询功能，只可以获取其最后一次查询的SQL脚本，也是SQL静态脚本，例如我的例子：
SELECT XXX FROM XXX_TAB WHERE TABLE_COL1='8888' AND TABLE_COL2=999
然后导出的程序先自动执行这个SQL脚本，在数据库端自动产生xlsx文件，再给用户下载的。
换句话说，无法用变量来替换。。。
我之前写的自动转换变量变量这个功能还是可以直接用的，只是动态SQL的执行方法方面需要改写一下，不用DBMS_SQL就OK，改用EXECUTE IMMEDIATE。

samt007

写共用的功能是稍微麻烦点。不过倒是一个相对一劳永逸的办法！这样子就不需要为特定的某个功能再特定开发一个快速Excel导出了。。。
用户用这个功能好几年，反馈的结果也是非常好。

newkid

用EXECUTE IMMEDIATE就有USING的问题，除非你要再一层动态，用动态PLSQL去执行动态SQL。我楼上那个方法已经类似EXECUTE IMMDATE效果了。
当然，你如果要把消费游标的代码也写成动态的，那么效率会更好，只是代码更复杂，更难读了，参考我多年前写的，和你8楼写的异曲同工：
http://www.itpub.net/forum.php?mod=viewthread&tid=1738533

samt007

newkid 发表于 2017-3-4 00:38
用EXECUTE IMMEDIATE就有USING的问题，除非你要再一层动态，用动态PLSQL去执行动态SQL。我楼上那个方法已经 ...

我在8楼写的代码，其实就是参考您之前回复的一个帖子的逻辑来写的~用游标作为一个参数，很巧妙解决USING参数不确定的问题！
现在不用EXECUTE IMMEDIATE而用DBMS_SQL的主要原因是觉得改动比较大，有一定风险，当时觉得没太必要（性能我也对比过，用EXECUTE IMMEDIATE是提高一点，但是没有质的提升！）。
现在倒是有让我改用EXECUTE IMMEDIATE的理由了~
感谢版主帮忙了~

samt007

根据这个问题，在Meterlink上搜索了一下，确实是一个bug，不过没修复的办法：
Does ACS work for the SQL from PL/SQL?

Yes. ACS will work on the SQL started within PL/SQL, however there are a number of cases where it does not. For example:

Bug 14276867 ACS IS NOT WORKING WITH PL/SQL CODE LEADING TO POOR PERFORMANCE OF THE QUERIES
Summary: For queries executed from pl/sql functions ACS monitoring is not on (v$sql.is_bind_sensitive is N) so the cursors can never become bind sensitive so ACS will never work.
Status: Not a Bug

Bug 8357294 adaptive cursor sharing doesn't work for static sql cursors from pl/sql
Summary: For SQL that is embedded in pl/sql and executed via "execute immediate", ACS does not work because PL/SQL caches it's cursors differently to standard cursors.
Status:   Unresolved

Bug 9197434 BIND PEEKING NOT HAPPENING WHEN USING DBMS_SQL
Summary Bind peeking does not work with dbms_sql. This is because the relevant driver does not handle the bind code in the right way for this to work and would need an enhancement to do so.
Closed as a duplicate of : Base bug:
  Bug 4179405 bind peeking does not work with dbms_sql
  Closed as a duplicate of : Base bug:
    Bug 13896999 ADAPTIVE CURSOR SHARING IS NOT WORKING AS EXPECTED WITH DYNAMIC PL/SQL
    Closed as a duplicate of : Base bug:
      Bug 13386678 BIND PEEKING DOES NOT WORK IN DBMS_SQL
      Closed, not feasible to fix

newkid

第一个他说不是bug, 不知道具体是怎么发生的。
第二个，如果EXECUTE IMMEDIATE执行的是动态PL/SQL，里面的SQL则是静态的，是不是就可以避开了?
第三个就是你顶楼的贴了，如果在PARSE时候已经硬解析，那确实是没有办法。

newkid

第二个我理解错了，是游标缓存导致的，想要缓存就没法用上新功能，真是纠结。
http://oracle-randolf.blogspot.c ... cursor-sharing.html

samt007

真纠结！我发现之前我改写的 EXECUTE IMMEDIATE 还是无法解决这个绑定变量窥探的问题。。。
原因是，因为我必须要先获取游标的栏位信息，例如栏位名称以及数据类型等等，所以，必须得用DBMS_SQL来解析SQL。就导致了解析的时候，没产生绑定变量窥探了。
唉~

samt007

newkid 发表于 2017-3-6 23:59
第二个我理解错了，是游标缓存导致的，想要缓存就没法用上新功能，真是纠结。
http://oracle-randolf.blog ...

如果改用下面这个方式，但是又碰到USING的问题。

1. DECLARE
   
2.   TYPE C_TYPE IS REF CURSOR;
   
3.   l_cur C_TYPE;
   
4.   R_EMP XYG_PROGRAM_ERROR_LOG%ROWTYPE;
   
5.   L_OBJ_NAME VARCHAR2(50);
   
6. BEGIN
   
7.   L_OBJ_NAME:='XYG_MTL_SYSTEM_ITEMS_TL_04';
   
8.   OPEN l_cur FOR 'SELECT * FROM XYG_PROGRAM_ERROR_LOG WHERE OBJECT_NAME=:1 ' USING L_OBJ_NAME;
   
9.   
   
10.   LOOP
    
11.       FETCH l_cur INTO R_EMP;
    
12.       EXIT WHEN l_cur%NOTFOUND;
    
13.       --处理啊处理。。。
    
14.       DBMS_OUTPUT.PUT_LINE ('ID为' || TO_CHAR (R_EMP.ERROR_LOG_ID) || ' OBJECT_NAME为：' || R_EMP.OBJECT_NAME);
    
15.   END LOOP;
    
16. 
    
17.   CLOSE l_cur;
    
18. END;
    

复制代码

jindingxin

路过而已·