you stand a GOOD CHANCE of
losing stat records
by turning sql trace off!!!!you stand a GOOD CHANCE of
losing stat records
by turning sql trace off!!!!you stand a GOOD CHANCE of
losing stat records
by turning sql trace off!!!!you stand a GOOD CHANCE of
losing stat records
by turning sql trace off!!!!you stand a GOOD CHANCE of
losing stat records
by turning sql trace off!!!!have you investigated dbms_monitor? It is designed to do this selective tracing you ask - it can enable tracing by sqlnet connection string, by user, by individual physical session or even logical (application server) session ids.
If you use a tnsconnect string for example that connects using a service name (your ETL processes would use this connect string) you could have tracing enabled (or not) for them automagically - no code.
(a) you should get trace data for most all things unless the subroutine causes a cursor to be closed. plsql caches open cursors using session_cached_cursors/open_cursors. If you exceed the plsql cursor cache, it'll close a cursor on you for real (normally, plsql does NOT close them - the stat records are NOT written out until they are REALLY closed - and if tracing is not on when they are really closed, you'll lose that)
consider:
declare
l_sql long := 'create or replace procedure p2 as begin ';
begin
l_sql := l_sql || 'execute immediate ''alter session set sql_trace=false'';';
for i in 1 .. 500
loop
l_sql := l_sql || ' for x in (select * from dual d' || i || ') loop null; end loop;';
end loop;
l_sql := l_sql || 'execute immediate ''alter session set sql_trace=true''; end;';
execute immediate l_sql;
end;
/
create or replace procedure p1( p_call_p2 in boolean default true )
as
begin
for x in (select * from all_users)
loop
null;
end loop;
if ( p_call_p2 ) then
p2;
end if;
end;
/
connect /
alter session set sql_trace=true;
exec p1;
@tk "sys=no"
alter session set sql_trace=true;
exec p1(false);
@tk "sys=no"
my @tk script is:
column trace new_val TRACE
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
disconnect
!tkprof &TRACE ./tk.prf &1
connect /
edit tk.prf
it disconnects (closes all cursors) and then tkprofs.
The first run has a tkprof of:
********************************************************************************
SELECT *
FROM
ALL_USERS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 149 0 46
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 149 0 46
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 563 (recursive depth: 1)
********************************************************************************
notice - no stat records, no row source operation. That is because P2 opened so many cursors that P1's cursor was really closed and tracing was off
however, the second run tkprof is:
********************************************************************************
SELECT *
FROM
ALL_USERS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 149 0 46
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 149 0 46
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 563 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
46 HASH JOIN (cr=149 pr=0 pw=0 time=7575 us)
46 HASH JOIN (cr=78 pr=0 pw=0 time=3577 us)
46 TABLE ACCESS FULL USER$ (cr=7 pr=0 pw=0 time=601 us)
67 TABLE ACCESS FULL TS$ (cr=71 pr=0 pw=0 time=2707 us)
67 TABLE ACCESS FULL TS$ (cr=71 pr=0 pw=0 time=750 us)
********************************************************************************
NOTE: this is true for 10gR2 and before only, in 11g - the tracing is different - the stat records get written out for each execution of the cursor - the stat records would be there in 11gR1 and above for the above example....
(b) have tracing on the entire time, just exit.
(c) see (b), same answer.