Recently in a class of mine we were all reviewing a trace file (10046) of a transaction that ran on the client’s system. There was one SQL statement that consumed about 70% of the total time for the run. The statement was rather simple one table and one predicate. The predicate was a concatenation of several columns (each had an index) with an equality operator to a concatenation of a couple of binds.
The predicate looked something like this (it was longer and slightly more complicated, then this but this captures the flavor):
where COL1 || '-' || COL2 = :B1 || '-' || :B2
Pretty clearly the Optimizer can’t use the indexes on the columns because they are concatenated together in the predicate. The STAT lines weren’t in the trace data so we couldn’t see for sure but it was likely doing a full table scan which was driving about 60,000 LIOs for the statement. After some discussion several options were bantered about on how to fix the SQL, with a new index and rewriting the predicate being the top two.
As we were finishing up the discussion, the person who brought in the trace said “But this statement isn’t the performance problem.”
To which I replied “Oh yes it is.”
This is something that I’ve experienced before. Folks will think another SQL statement is the problem before they start and when it isn’t they still focus on the statement they think is the problem. We all do it. We have a preconceived idea of what the problem is and when faced with something else, we have a hard time getting over our preconceived idea.
This is the root of why having some one with little knowledge of the system can seem to have such great insight. Really it’s just that they have no preconceived idea of what is wrong. They can then follow the clues without prejudice and see what everyone else may have missed.
Cardinal Thomas Wosley (1471-1530) was an advisor to King Henry the VIII. He gave the King the advice of "Be very, very careful what you put into that head, because you will never, ever get it out." Several 100 years later this advice still rings true.
Hi Ric,
ReplyDeleteI enjoyed this one. I totally agree with your comments on preconceived ideas by the way. Even for an outside guy it can be very hard because the insiders often tell you what they think the problem is before you even start. And even those that have more self control and try not to prejudice you will quite often define the problem in terms that lead to a certain under lying cause. It takes a lot of self discipline to not let your mind go down the diagnosis path until after you have actually looked at all the facts, even for a "trained professional".
So what was the problem anyway? I thought nothing could hide from the almighty trace file. ;)
Kerry