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

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

5recursive calls

0db block gets

2081consistent gets

0physical reads

0redo size

695bytes sent via SQL*Net to client

400bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

9rows processed

关于执行计划，参考我的Blog：

Oracle 执行计划（Explain Plan） 说明

http://blog.csdn.net/xujinyang/article/details/6832655

官网对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 arealso 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 statementthat is executed at the request of the user.

A recursive call occurswhen 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。

Oracle Shared pool详解

http://blog.csdn.net/xujinyang/article/details/6823351

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

二.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

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

8recursive calls

0db block gets

8826consistent gets

0physical reads

0redo size

2541097bytes sent via SQL*Net to client

37250bytes received via SQL*Net from client

3352SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

50256rows processed

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

50256 rows selected.

Statistics

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

0recursive calls

0db block gets

8824consistent gets

0physical reads

0redo size

2541097bytes sent via SQL*Net to client

37250bytes received via SQL*Net from client

3352SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

50256rows 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

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

247555014453.994195589

查看data dictionary cache的大小：

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

SUM(SHARABLE_MEM)

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

17399859

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