Query runs fine on InstanceA but slow on InstanceB...
- From: David Mann <dmann99@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 6 Jan 2012 09:42:25 -0500
I need some advice. I seem to be asked more and more the dreaded "why
does my query run faster on InstanceA and slower on InstanceB?"
I provide all kinds of query tuning help to our developers, testers,
and product support. Mostly this covers a surgical strike of 1 SQL
statement. I investigate the issue, find a couple of ways to improve
the query, document my improvement and provide my recommendation ...
and then move on.
This A vs B seems to take me about 4X the time of a regular tuning
request. I am now investigating the performance of a query on
InstanceA, a query on InstanceB, and using my ever evolving checklist
of why execution plans can differ to drill down into each reason
(instance params, data volume, system stats, object stats, etc) and
look for significant differences. I can usually find a way to improve
the Query@InstanceB performance but I am not always successful finding
a root cause for the difference. Plus I usually want to know this as
much as my customer so I know what to look out for when dealing with
future tuning issues with their apps.
I have my own checklist to guide my investigation and a bunch of
custom queries to check data volume, system statistics, object
statistics (basic table statistics right now, would like to expand to
cover histograms, etc). I don't think I am covering all the bases
since my checklist still gets changed/expanded after every tuning
engagement.
SQLT probably covers everything I look for (and more) has been a good
tool to get an idea of the environment surrounding the execution of a
query on a system. but it is a little cumbersome at times (I can be
called on to help with any of 600 DB instances - I end up installing
it pretty much every time I need it somewhere to make sure I have the
latest/greatest version). I had high hopes but limited success with
SQLTCompare. I have only gotten it to compare successfully maybe 2 out
of the 6 times I used it.
I have also had some success with 10053 traces for smaller queries.
But at least for me these quickly get out of hand so I usually only
use them as a last resort.
So my question is how do you approach something like this? Is there a
'best' method or tool for investigating and drilling down to a root
cause of the same statement against 2 difference DB instances? Are
there any book chapters, blog posts, or other resources you have seen
covering this type of investigation? Is it time for me to get busy
writing my own tool? :)
--
Dave Mann
www.brainio.us
www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
--
//www.freelists.org/webpage/oracle-l
Other related posts:
