Using this utility you can trace SQL from any SQL prompt
Clone the scripts from git using:
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.)
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'));
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.