Identify heaviest plan_line_id for a given execution plan (Oracle 11g)
I think the most mysterious question to me (well, as far as Oracle was the subject) was always that:
For a given SQL and its execution plan, where does Oracle spend most of its time?
Oracle 11g revealed this mystery by exposing Real Time SQL Monitoring, v$sql_monitor, and v$sql_plan_monitor. However, these performance views will only show information either for relatively long running queries (“when it has consumed at least 5 seconds of CPU or I/O time”) or for all parallel queries. Suppose you have SQL with shorter execution time, running many times? You won’t be able to find it there… (more…)