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
Thats very true !! Every DBA should work with aplication team. Because in a way their SQLs/code are affecting your database performance only.This is how you will expand your knowledge not only technically , functionally also. You will get to know that how application and that domain works.
ReplyDelete"Never stop learning "
Well said! Thanks for sharing this story with us.
ReplyDeleteThis is so true and I have seen this many times working as an application DBA. Usually you get involved at test or UAT stage, when the data structures are built and the code is written, often far too late. I helped reduce running times on batch processes to reduce an end of week batch run from 60 hours to under 24 hours, just by using tkprof to identify slow code and then working with the application developers (in a systems house) to improve performance.
ReplyDeleteYou can also look at the block level of the table to incrase the initrans parameter to be greater than 1 when building the table so you can have more than one lock per database block.