When we search for untuned SQLs we can look for SQL that its execution time is objectively long, but we should also search for SQL which has high amount of short executions, or in other words frequently executed SQL. When dealing with frequently executed SQLs, even a small improvement in one execution (average execution time) will yield a performance improvement, since this “small” improvement of one execution is multiplied by its many executions. (more…)
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…)