Using SQL Trace and TKPROF

Understanding Recursive Calls

Sometimes, in order 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, then 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, then TKPROF produces statistics for the recursive SQL statements and marks them clearly as recursive SQL statements in the output file. You can suppress the listing of Oracle internal recursive calls (for example, space management) in the output file by setting the SYS command-line parameter to NO. 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, consider the statistics for that statement as well as those for recursive calls caused by that statement.

—-
Note:
Recursive SQL statistics are not included for SQL-level operations. However, recursive SQL statistics are included for operations done below the SQL level, such as triggers. For more information, see “Avoiding the Trigger Trap”.

Recursive SQL call 에 유의하야하고, recursive SQL로 인한 resource 사용은 SQL문장에 대한 통계에서 제외됨에 유의해야한단 겁니다. recursive SQL문은 위의 예에 잘 나와있는 데이터 딕셔너리 캐시 miss 발생시 발생하는 SYS에 의해 실행되는 SQL문장을 뜻합니다. 그러니까 주로 DML문장이나 프로시저 호출시 프로시저 내용 얻어오기 등에서 사용되겠죠.

Avoiding the Trigger Trap

The resources reported for a statement include those for all of the SQL issued while the statement was being processed. Therefore, they include any resources used within a trigger, along with the resources used by any other recursive SQL (such as that used in space allocation). With the SQL trace facility enabled, TKPROF reports these resources twice. Avoid trying to tune the DML statement if the resource is actually being consumed at a lower level of recursion.

If a DML statement appears to be consuming far more resources than you would expect, then check the tables involved in the statement for triggers and constraints that could be greatly increasing the resource usage.

하지만 트리거와 같은 SQL 수준의 실행에서 발생한 resource consumption은 원래의 SQL문장에 대한 statistics에 포함된다. 따라서 두번세지 않도록 주의.

만약 SQL문장에서 프로시저를 호출할경우, 프로시저의 정보가 data cache에 없어서 miss가 나면 이는 recursive SQL을 유발하며 이 때의 자원 소비는 원래의 SQL문장에 포함되지 않죠. 그러나 프로시저 자체의 실행에서 발생한 자원 소비는 포함됨에 유의.

더불어 SQL*PLUS의 set autot on 문법(또는 set autotrace on)으로 볼때의 자원소비나 실행계획은 바인드 변수를 제대로 해석하지 못하는 문제가 있어서 정확한 것이 아님에 유의. Tom Kyte의 말마따나 다른 것은 믿지 말고 오직 tkprof, tkprof, tkprof!