No access to the database server? Need to trace SQL?

Using this utility you can trace SQL from any SQL prompt

Clone the scripts from git using:

git clone https://github.com/johnthomas22/SQLTraceUtils.git

Have a look at what the scripts are doing, then if you’re happy, connect to your test database as SYS.

sqlplus / as sysdba @setup_trace_utils_sys.sql

Once set up, using it is simple.

You can start a SQL trace for your session, or start a trace by SQL ID (from V$SQL).

The package also contains procedures to start 10053 (optimiser) traces. (As Jonathan Lewis said recently, they’re interesting, but if you really need an optimiser trace something is already badly wrong.)

Example usage:

EXEC sqlutils.trace_utils.start_sql_trace

SELECT sqlutils.trace_utils.get_tracefile_name FROM dual;

SELECT job_title, max_salary, first_name, last_name
FROM hr.jobs j JOIN hr.employees e
ON j.job_id = e.job_id
/

SELECT * FROM TABLE(sqlutils.trace_utils.get_tracefile('XE_ora_6166.trc'));

SELECT *
FROM TABLE(sqlutils.trace_utils.get_tkprof('XE_ora_6166.trc'));
...

Sample output from trace_utils.get_tkprof, at your SQL prompt.

Please let me know if this is useful to you via my contact page.

If you need help interpreting the raw trace files, possibly the best book available to explain them is Cary Milsap & Geoff Holt’s “Optimising Oracle Performance”.

If you read only one thing on this page, learn to take six digits off the end of the wall clock time in trace files. It won’t tell you what the time of day is, but it will give you a good idea where your SQL’s time is going, showing you the second each timed event or wait happened.

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close