For a long time, as a DBA, I mostly didn’t have access to tuning tools, like the Diagnostics Pack. Oracle’s Diagnostics Pack is very handy and I’d recommend it to to anyone who has to deal with performance problems in Oracle databases. But you can do performance tuning without it. One, very exact, way of gathering performance data, it tracing a session – with wait events, also known as event 10046 SQL tracing.
The biggest challenge in SQL tracing, is starting the trace in time. If you are too late, you might have missed a slow SQL statement at the beginning and you are looking at data for Cursor #0. And you have no idea what Cursor #0 is.
So quickly starting a trace is the key. If you start a trace by hand, you still have to find the sid, and the serial# for the right session. First: what is the right session? What do you base that on? username? osuser? program? machine? And when you have found the right session, then you slowly type in the dbms_monitor command. What was that syntax again?
To speed things up, I use my trace_column.sql script. To use it you need to know two things beforehand: based on what column in v$session do you want to find your session. And two: for what values do you want to start a trace? (And you can enter a trace identifier, although I haven’t tested that very thoroughly yet). trace_column.sql automatically starts the trace and, also generates a script to stop the trace. That is important too, because you don’t want to fill up your file system with traces.
You can create a neat readable HTML report of the trace file with OraSRP.