DB OPTimize

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:

1.      select count(*) from T1, T2 where … /* This brings back a number after 2 hours processing. Lets call it X */

 

2.      devide X/100 (to get number of loops to be made)

 

3.      for 1 in 1.. x/100 {
     select … from T1,T2 where … and rownum =< 100 /* rowset to be processed. 100 each time */
}

As a result, row processing (the SELECT inside the loop) couldn’t not start till the count query is finished.
In other words, program is wasting two hours for nothing waiting for this count to finish, while in fact this count is unnecessary, causing unneeded overhead, and is just bad code writing.
Application should remove the count query, and simply run select in a loop till no_data_found is returned. It should logically look like that (pseudo):

cont_processing = 1
while ( cont_processing ) {
select … from T1,T2 where … and rownum =< 100 ; — just the inner loop
if no_data_found then cont_processing =0
}

I know. This is pure code tuning, and has nothing to do with SQL tuning. Is it DBA responsibility? Some will say yes, and some will say no. But, if I wouldn’t have gone to the application, and examine the code by myself, this extremely heavy SQL will continue to run in the database, interfering others, causing performance degradation.

What is the conclusion from this real life story?
I believe that DBA and application should work together. It is always proving itself. So my advice to you is to talk with your application representative as much as possible. Know the source of the SQL you see running in your database. Learn their code, and remember: Knowledge is power, and when you know more, you expand your options for solving a problem.

Merav Kedem,
Founder of
DB OPTimize
Oracle Performance Tuning & DBA Consulting