Creating an index can certainly do marvellous things to SQLs. Even after all those years in the field, it is still always thrilling for me to witness huge performance improvement seen after applying a suitable index to an unturned query. The performance gain is simply spectacular and the notices slump in resource consumption this query is taking (I/O, CPU, etc) is immediate. (more…)
In my previous post “Beyond Database Tuning” I have shown a situation where application was running a very heavy SQL (~2 hours) each day, while in fact, after speaking with the application, I understood that this SQL is simply not needed and its output is only functioning as a loop counter in a Java code. (more…)
Let me share with you something that happened to me today.
I saw this very heavy SQL, running several times a day, taking up to 2 hours each time, consuming lot of database resources (CPU & IO).
This select was doing a count on a very big table (150M rows) joined with a smaller table (5M rows) and had some non selective filter predicates in its where clause.
Tuning capability didn’t look so promising for this SQL.
I have decided to go and ask the application for the purpose of this SQL, and investigate what it does with its result (count).
What I found was quite a shock. They code was basically doing that: (more…)
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…)