[20120414]存储过程递归调用与V$SQL.txt

[20120414]存储过程递归调用与V$SQL.txt

如果存储过程存在递归调用看看能否在v$SQL中有记录呢？前一阵子跟踪程序，优化sql语句的过程中，
发现在存储过程中再调用存储过程，v$sql中体现不出来。

写一个例子如下:
CREATE or replace  PROCEDURE testfindme( flag  VARCHAR2) as
BEGIN
    IF flag = '1' THEN
        dbms_output.put_line ('flag='||flag);
    END IF;
    IF flag = '2' THEN
        dbms_output.put_line ('flag='||flag);
        testfindme('1');
    END IF;
END;
/


SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


1.检查是否存在：
SELECT sql_text, sql_id, executions, plsql_exec_time, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable
  FROM v$sql
 WHERE lower(sql_text) LIKE '%testfindme%' AND lower(sql_text) NOT LIKE '%v$sql%';

no rows selected


2.测试，如果我执行exec testfindme('2'),可以确定存储过程会调用testfineme('1').

SQL> set SERVEROUT on
SQL> column sql_text format a30
SQL> column is_obsolete format a10
SQL> column is_bind_sensitive format a10
SQL> column is_bind_aware format a10
SQL> column is_shareable format a10
SQL> exec testfindme('2');
flag=2
flag=1

SQL> SELECT sql_text, sql_id, executions, plsql_exec_time, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable
  FROM v$sql
 WHERE lower(sql_text) LIKE '%testfindme%' AND lower(sql_text) NOT LIKE '%v$sql%';
SQL_TEXT                       SQL_ID        EXECUTIONS PLSQL_EXEC_TIME IS_OBSOLET IS_BIND_SE IS_BIND_AW IS_SHAREAB
------------------------------ ------------- ---------- --------------- ---------- ---------- ---------- ----------
BEGIN testfindme('2'); END;    ahw3y5yzbjkg6          1             177 N          N          N          Y

--可以发现并没有调用testfindme('1')的情况。再执行一次：

SQL> exec testfindme('2');
flag=2
flag=1

PL/SQL procedure successfully completed.

SQL> SELECT sql_text, sql_id, executions, plsql_exec_time, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable
  FROM v$sql
     WHERE lower(sql_text) LIKE '%testfindme%' AND lower(sql_text) NOT LIKE '%v$sql%';
SQL_TEXT                       SQL_ID        EXECUTIONS PLSQL_EXEC_TIME IS_OBSOLET IS_BIND_SE IS_BIND_AW IS_SHAREAB
------------------------------ ------------- ---------- --------------- ---------- ---------- ---------- ----------
BEGIN testfindme('2'); END;    ahw3y5yzbjkg6          2             301 N          N          N          Y

--奇怪并没有记录testfindme('1') 的语句。可以发现存储过程存在递归调用在V$SQL中并没有体现出来！

查询基表：(sys用户执行)
SQL> column KGLNAOBJ format a30
SQL> SELECT addr, indx, inst_id, kglhdpar, kglnaobj FROM SYS.x$kglob WHERE lower(KGLNAOBJ) like 'begin%testfindme%';
SQL> SELECT addr, indx, inst_id, kglhdpar, kglnaobj FROM SYS.x$kglob WHERE lower(KGLNAOBJ) like 'begin%testfindme%';
ADDR                   INDX    INST_ID KGLHDPAR         KGLNAOBJ
---------------- ---------- ---------- ---------------- ------------------------------
0000002A97467208       1402          1 00000000994F0D10 BEGIN testfindme('2'); END;
0000002A971CB900       1403          1 00000000994F0D10 BEGIN testfindme('2'); END;

3.我执行testfindme('1') 看看结果如何？
SQL> exec testfindme('1');
flag=1
PL/SQL procedure successfully completed.

SQL> SELECT sql_text, sql_id, executions, plsql_exec_time, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable
  FROM v$sql
   WHERE lower(sql_text) LIKE '%testfindme%' AND lower(sql_text) NOT LIKE '%v$sql%';
SQL_TEXT                       SQL_ID        EXECUTIONS PLSQL_EXEC_TIME IS_OBSOLET IS_BIND_SE IS_BIND_AW IS_SHAREAB
------------------------------ ------------- ---------- --------------- ---------- ---------- ---------- ----------
BEGIN testfindme('1'); END;    5d8ga1jv253gj          1             111 N          N          N          Y
BEGIN testfindme('2'); END;    ahw3y5yzbjkg6          2             301 N          N          N          Y

3.再建立一个新的存储过程
CREATE or replace  PROCEDURE testfindmea( flag  VARCHAR2) as
BEGIN
    dbms_output.put_line ('flag='||flag);
    testfindme('2');
END;
/

SQL> exec testfindmea('3');
flag=3
flag=2
flag=1

PL/SQL procedure successfully completed.


SQL> SELECT sql_text, sql_id, executions, plsql_exec_time, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable
  FROM v$sql
   WHERE lower(sql_text) LIKE '%testfindme%' AND sql_text NOT LIKE '%v$sql%';
SQL_TEXT                       SQL_ID        EXECUTIONS PLSQL_EXEC_TIME IS_OBSOLET IS_BIND_SE IS_BIND_AW IS_SHAREAB
------------------------------ ------------- ---------- --------------- ---------- ---------- ---------- ----------
BEGIN testfindme('1'); END;    5d8ga1jv253gj          1             111 N          N          N          Y
BEGIN testfindme('2'); END;    ahw3y5yzbjkg6          2             301 N          N          N          Y
SELECT addr, indx, inst_id, kg 5drw5vqu56szv          2               0 N          N          N          Y
lhdpar, kglnaobj FROM SYS.x$kg
lob WHERE lower(KGLNAOBJ) like
 'begin%testfindme%'
BEGIN testfindmea('3'); END;   95975a87vmb3b          1             184 N          N          N          Y

--从EXECUTIONS看，虽然testfindmea('3')调用了testfindme('2')和testfindme('1'),但是在V$SQL中并没有体现出来。

可以发现存储过程存中调用其它存储过程在V$SQL中并没有体现出来！在优化存储过程中给注意一下！