Recently I got involved into some application performance problem. Now it’s funny to hear how sure everybody is when it comes to performance issues that it is not the application and the programming itself.
Everything was checked twice, nothing had been changed since ages and the application part in question did always work reliable …. What to do?
Sure, it must be an database issue and the dba is in charge to solve solve the problem by tweaking the db and therewith rescue the world for all.
Hmmmmm …. ok with everybody that sure I first kept my mouth shut, logged in into the system and checked the cpu and memory usage.ย A cpu usage of 37% on a stable level over a longer period of time, a database server process consuming 1,6GB of memory …. really not an all day thingy.
So I asked the developers what the application would do there right now?! Nothing specific they said! Running aย simple queryย which usually completes within milliseconds. Ah .. ok …. but it didn’t look that was it would complete in milliseconds right now.
Ok then, having some point to start with I picked up the query, had a look at the tables , substituted the bind variables with values they gave me, did run the query and asked the database for the execution plan … All fine!! ๐
So I finally I had the idea to simply count the variance and frequence of the stored primary key attributes.
And see there with the right value used within the where clause chosen for filling the bind variablesย the whole execution plan changed! No index where used anymoreย and a full table scan was performed!
Someone should really know about his or her data stored within the db and it not …. remember some simple mathematic operations which even the database will do for you! ๐
I left as quiet as I came, as for it was anyway very calm in the room ….