SERPland Flugverkehr/Airtraffic Zürich – Auto Direktimport Dänemark – Disneyland Paris – Legoland Deutschland

17Oct/060

SQL Trace with Oracle Database


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

get oracle tracefile via 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.

Related Posts:

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.