How to Trace Recursive SQL Calls in Oracle Using 10046 and Orasrp

Overview

DBAs often use the top‑activity session view to locate high‑impact SQL statements. When a top SQL is identified, it may be necessary to determine which application module or client originated the statement, especially if the SQL is recursive and its source is not obvious.

Problem Description

An abnormal top SQL with ID c7452agj0s0t6 was consuming about 9% of database time. The SQL text queries the V$ACTIVE_SESSION_HISTORY view, but the originating client machine could not be identified from the standard SQL detail page.

SQL‑ID Level Trace Analysis

Enable a 10046 trace for the specific SQL ID:

alter system set events 'sql_trace [sql:c7452agj0s0t6] wait=true,bind=true,plan_stat=all_executions,level=12';

Find the default trace file directory:

select value from v$diag_info where name='Default Trace File';

Search the most recent trace file (e.g., PRDDB_oraxxx.trc) for the SQL ID. The trace shows a client identifier testapp and a dependency depth of 2, indicating a recursive call that was not submitted directly by a user.

*** SESSION ID:(329.41269) 2016-10-14 09:34:13.921
*** MODULE NAME:(python@testapp (TNS V1-V3)) 2016-10-14 09:34:13.921
... 
PARSING IN CURSOR #139987897061528 len=11079 dep=2 uid=87 ... sqlid='c7452agj0s0t6'

Disable the SQL‑ID trace:

alter system set events 'sql_trace [sql:c7452agj0s0t6] off';

Session‑Level Trace Analysis

Because the offending SQL runs in a short‑lived session, create a logon trigger for the APPUSER schema to start a 10046 trace automatically on the next login:

create or replace trigger sys.set_trace
after logon on database
when (user in ('APPUSER'))
declare
begin
  execute immediate 'alter session set statistics_level=all';
  execute immediate 'alter session set max_dump_file_size=unlimited';
  execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end;
/

After the trigger fires, locate the newly generated trace file (e.g., PRDDB1_ora_10452.trc) and analyze it with the open‑source tool Orasrp (Oracle Session Resource Profiler). The tool URL is:

http://oracledba.ru/orasrp/

Orasrp produces a session call graph that reveals the recursive call chain: the top‑level SQL (hash 2036392974) invoked the problematic SQL (hash 3792438054) at depth 2.

Root Cause Identification

The top‑level SQL was generated by the PL/SQL function dbms_sqltune.report_sql_monitor, which had been called by a newly deployed monitoring job that fetched SQL Monitor reports too frequently.

select dbms_sqltune.report_sql_monitor(type=>:1, sql_id=>:2, sql_exec_id=>:3, report_level=>'ALL') monitor_report from dual;

Reducing the job’s execution frequency eliminated the excessive recursive calls.

Cleanup

Drop the logon trigger to avoid generating unnecessary trace files:

drop trigger sys.set_trace;

Conclusion

Combining SQL‑ID‑specific and session‑level 10046 tracing with the Orasrp analyzer allows DBAs to uncover the origin of recursive SQL statements, such as those generated by dbms_sqltune.report_sql_monitor. The methodology is applicable to diagnosing PL/SQL performance issues, long parse times, and other obscure database problems.