Oracle AUTOTRACE alternative

Question

I need collect statistics for my long SQL-scripts. Script files generated by Java application and execute by third-party fast DB-driver.

In this way I can't use AUTOTRACE, because it`s not SQLPlus. But for perfomance reasons I need to know stat information about every statement in script.

Can you advice approaches or best practices?

I look in the direction of STATSPACK and secondarily tkproof. 1. STATSPACK looks like machine that breaks a butterfly on a wheel. Isn`t it? 2. tkproof needs kind of privileges and I worry that the Java application user does not have such privileges.

Is there something else?

Answer 1

You can get some statistics from v$sql:

SELECT q.*
FROM v$session s
, v$sql q
WHERE s.sql_address  = q.address
AND s.sql_hash_value = q.hash_value
AND s.sid            = :SID
;

There is also v$session_longops which shows all operations which run for more than 6 seconds.

Answer 2

The only escalated privilege an Oracle user needs to generate a trace file is ALTER SESSION. You may not even need that if your user has execute on SYS.DBMS_MONITOR, as the SESSION_TRACE_ENABLE and SESSION_TRACE_DISABLE allow instantiation of tracing.

On the other hand, retrieving the trace file requires either the assistance of the DBA or the DBA to perform configuration to allow trace files to be public (a bad idea on production). Dion Cho has an excellent example of how to enable Oracle trace files to be queried here.