DB OPTimize

As Fast As It Can Get

Contact Us

Oracle Performance Tuning & DBA Consulting

Latest Posts

Thinking of adding a new index? Tune SQL’s text before

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…)

Beyond database tuning

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…)

Tuning a frequently executed SQL

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…)

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…)