I was astonished that I found no entry for TKPROF in the forum (or DBMS_MONITOR wich will give a funny 'Did you mean?' recommendation) -
These are my favourite tools when it comes down to know the root cause of slow DB performance and to jump into an Oracle session for tracing it...
Ever asked questions like:
1. what is the TRUE execution plan of that long running query at run-time when the trace where taken?
2. what were the EXACT row counts flowing through the execution steps of the plan?
3. what were my statement parse, execute and fetch times and counts
4. what were the root causes it took so long?
Well, the answer is: Oracle user trace file generation with DBMS_MONITOR package and TKPROF...
(You might know that not only the Ora DBA accounts but also the SAPR3 user has execution privileges for DBMS_MONITOR...wich is the reason I presented it to my developers and to the forum here)
I like the ST04 active session history (ASH) for a quick and easy pin-down of problem statements in active running sessions but sometimes more information is needed like mentioned above.
More than often the cursor cache was already flushed for your statement in question.
Let me comment on the questions above:
-> 1. Imagine you know that long running SQL and generate the execution plan arguing that an unexpected full table scan was going on - but the plan reveals a fine Index range scan with current statistics up...
what you want to know is if this plan was used at run-time or not (In our case they actually DELETED the statistics for the objects in the application, run the process, and our nightly Stats Gather job camouflaged it perfectly).
Or stored outlines are in place overwriting the CBO plan...
-> 2. your CBO row estimates MAYBE good but to have EXACT values can help you a lot to analyse that statement as poorly written...
->3. sometimes question raises like:
is the statement making a good use of bind variables or do I have a lot of library cache misses?
did they use bulk fetches and reuse the statement?
do they a lot of unnecessary work in reoccuring statements?
->4. Wait event information can also be retrieved from ST04 ASH, but with TKPROF you get it in a aggregated and ordered manner for the top 5 wait events - or you can read the original trace file wich will give you EVERY detail (i.e. segment and block number in the file causing the waits)