Subscribe

Recursive calls and statistics

There are many cases when executing some statement (either SQL or PL/SQL) may invoke execution of another SQL or PL/SQL statement.
However it’s not at all obvious how execution statistics for cursors are aggregated.
Let’s consider two cases

SQL statement calls another SQL statement in a function

PL/SQL block calls SQL statement

In both cases there will be two cursors: parent and child (in SQL trace at recursive depth 0 and 1 respectively).This concept has no relation to child cursors and cursor sharing though (each sql_id has one ore more child cursors numbered from zero – v$sql.child_number).
Recursive SQL is the SQL associated with a database call and a recursive depth value greater then zero.
Recursive depth can be any non negative integer number and for any child cursor on n + 1 level there should be parent cursor on nth level.
Let’s analyze execution statistics for them.

It’s a bit surprising that number of buffer gets for “nop” function is greater than for “scanning” function and also number of disk reads is considerably greater then zero.
Let’s repeat the same experiment without second query (select /*+ qwerty0 */ f0 from dual).

This output shows that overhead introduced by f0 in first output now added to SQL_ID fycxy6700td4z.
In order to figure out what Oracle was doing we can refer to SQL trace (for second scenario).
It shows that besides user statements (Parsing user id is not SYS) like

The crucial point here is that statistics for child statement is not added for parent unlike the approach for v$sql.
So you can see that elapsed time for fycxy6700td4z is near zero and disk reads are zero.

Interesting question is how to find out parent statement for given child recursive statement.
As described in Optimizing Oracle Performance by Cary Millsap

The rule for determining the recursive relationships among database calls is simple:
A database call with dep=n + 1 is the recursive child of the first subsequent dep=n database call listed in the SQL trace data stream.

As you can see statistics for parent statement in v$sql are aggregated which includes statistics for recursive user statement (SQL_ID fnzgacfvhu7rz) and other recursive statements under SYS schema. As well as statistics are not aggregated in TKPROF output.

So there are a lot of cases when one statement recursively calls another user statements (function, trigger, SQL statement in PL/SQL block etc) or SYS statements (retrieving dictionary information, dynamic sampling etc) and it’s important to understand how to treat execution statistics.