Enable SQL Trace for my own single session:
ALTER SESSION SET SQL_TRACE = TRUE;
Or use a pl/sql program instead:
begin
dbms_session.set_sql_trace(true);
end;
For other sessions you can use:
BEGIN
sys.DBMS_SYSTEM.set_sql_trace_in_session(,,TRUE);
END;
Whereby and is to be determined as follows:
SELECT username, sid, serial#
FROM v$session
WHERE username=xxx
The moreover you can steere whether the trace file contains time measurements:
ALTER SESSION SET TIMED_STATISTCS = TRUE
The trace file lies on the data base server (udump directory) and can be read by means of ftp
The tracefile can than be visualized. It’s recommended to use TOAD’s TKPROF utility.
Alternatevely, a similar functionality as the SQL_TRACE with TKPROF, described above, can be made also with the Trivadis Utiltity „Explainer “. The Explainer for the SQL statement also materials out and spends also the number of Rows in the Execution plan!
Note: In the Tool the COMMAND
ALTER SESSION SET STATISTICS_LEVEL = ALL
needs to be set.