Oracle Recursive Calls 说明

一.  Recursive Calls 说明

在执行计划的统计信息里有如下信息：

 

SYS@anqing2(rac2)> set autot trace statistics

SYS@anqing2(rac2)> select * from ta,tb where ta.id=tb.id and ta.id <10;   

9 rows selected.

 

Statistics

----------------------------------------------------------

          5  recursive calls

          0  db block gets

       2081  consistent gets

          0  physical reads

          0  redo size

        695  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          9  rows processed

 

官网对recursive calls 的解释如下：

       Recursive Calls：  Number of recursive calls generated at both the user and system level.    

 

       Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。

 

MOS 上的说明

       Sometimes to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called 'recursive calls' or 'recursive SQL statements'.  For example, if you insert a row into a table that does not have enough space to hold that row, Oracle makes recursive calls to allocate the space dynamically.       

       Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk. If recursive calls occur while the SQL trace facility is enabled, TKPROF produces statistics for the recursive SQL statements and clearly marks them as recursive SQL statements in the output file.

       Note that the statistics for a recursive SQL statement are included in the listing for that statement, not in the listing for the SQL statement that caused the recursive call. So when you are calculating the total resources required to process a SQL statement, you should consider the statistics for that statement as well as those for recursive calls caused by that statement.

 

 

IBM 的一篇文档上说明如下：

       http://publib.boulder.ibm.com/tividd/td/ITMD/SC23-4724-00/en_US/HTML/oraclepac510rg59.htm

 

Monitors the following information:

       1.Recursive Calls -- The number of recursive calls since the instance was created

       2.User Calls -- The number of user calls since the instance was created

       3.Recursive Call Rate (Interval) -- The number of new recursive calls per second

       4.Recursive To User Call Ratio -- The number of recursive calls compared to the number of user calls

 

       A user call is an SQL statement that is executed at the request of the user.

 

       A recursive call occurs when one SQL statement requires the execution of a further separate SQL statement. A continued increase in the reported figure indicates poor or decreasing system performance. Some recursive activity is unavoidable.

 

Recursive calls can be generated by the following activities:

       （1）An object requiring an additional extent for storage (dynamic extension)

       （2）Misses on the dictionary cache

       （3）Firing of database triggers  

       （4）DDL statements

       （5）Execution of SQL statements within stored procedures, packages, functions, and anonymous PL/SQL blocks

       （6）Enforcement of referential integrity constraints

 

       If Oracle is making an inordinate number of recursive calls, try to determine which of the previously listed activities is causing most of the recursive calls. Run the application through TKPROF with EXPLAIN PLAN to see what the application is doing.

       Also, monitor the number of extents in the database to see if there is noticeable dynamic extension. If the recursive calls are caused by dynamic extension, you can reduce the number of calls by allocating larger extents to the relevant objects. A dictionary cache that is too small can also cause recursive calls.

 

总结一下：

       当执行一条SQL语句时，产生的对其他SQL语句的调用，这些额外的语句称之为''recursive calls''或''recursive SQL statements''.

 

在IBM 的那片文档里讲了触发Recursive Call的6种情况： 　

       如：

       （1）我们做一条insert 时，没有足够的空间来保存row记录，Oracle 通过Recursive Call 来动态的分配空间。

       （2）执行DDL语句时，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便成功的执行该DDL语句。

       （3）当Shared Pool过小，data dictionary cache 也会相应的过小，没有足够的空间存储ORACLE的系统数据字典信息时，会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。

       （4）存储过程、触发器内如果有SQL调用的话，也会产生recursive SQL。

 

 

       在这些情况中，主要是对数据字典的查询，通常发生在第一次执行时，第二次执行一般可显著降低。递归需要消耗大量的资源,如果操作复杂,很容易出现问题!

 

 

二.  Recursive Calls 的测试

       在上面的说明提到数据字典查询，如果Data dictionary cache 过小，没有足够的空间来存放数据字典信息时，就会发生Recursive Calls，此时ORACLE会从硬盘读取数据字典信息，来完成相关的查询工作。

       在这种情况下，可以将recursive calls理解为从磁盘读取数据字典的次数。

 

SYS@anqing2(rac2)> set autot trace stat

SYS@anqing2(rac2)> select * from dba_objects;

50256 rows selected.

 

Statistics

----------------------------------------------------------

          8  recursive calls

          0  db block gets

       8826  consistent gets

          0  physical reads

          0  redo size

    2541097  bytes sent via SQL*Net to client

      37250  bytes received via SQL*Net from client

       3352  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      50256  rows processed

 

SYS@anqing2(rac2)> select * from dba_objects;

50256 rows selected.

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       8824  consistent gets

          0  physical reads

          0  redo size

    2541097  bytes sent via SQL*Net to client

      37250  bytes received via SQL*Net from client

       3352  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      50256  rows processed

 

       在第一次查询dba_objects时，产生了8次recursive Call，第二次查询的时候，因为数据字典的信息信息已经放在cache里，所以第二次的recursive call 为0. 如果第二次也没有完全cache，那么也是会产生recursive call，但次数比第一次少。

 

 

查看 data dictionary cache 的命中率：

SYS@anqing2(rac2)> select sum(gets),sum(getmisses),(1-(sum(getmisses)/(sum(gets)+sum(getmisses)))) hitratio from v$rowcache;

 

 SUM(GETS)  SUM(GETMISSES)    HITRATIO

----------      --------------   ----------

   2475550     14453    .994195589

 

 

查看data dictionary cache 的大小：

SYS@anqing2(rac2)> select sum(sharable_mem) from v$sqlarea;