Re: Tuning a query on a non-dedicated machine

conwayjim_at_my-dejanews.com >>
We are looking for the right (mostly oracle) utilities to run...and statistics to capture to determine which version of a query would run more efficiently (speedwise). We don't have a dedicated system to use to benchmark the queries..   <<<

You are taking the right approach.
1) Explain everything and just look at the results. It is not always obvious from an explain if the query will run well or not, but you can often spot reasonable and unreasonable access paths. 2) Try running the queries. Even on a non-dedicated machine this will give good results of one version of the query vs. another version of the same query by using multiple runs.
3) Run tkprof and compare the I/O statistics for the various versions of the same query. Lower I/O requests usually equals lower run time. Again you can use multiple runs to eliminate the effects of other jobs on system resources.

I hope this helps.
Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Tue Oct 13 1998 - 09:32:51 CDT