[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中並沒有體現出來！在最佳化儲存過程中給注意一下！