If a query is used frequently, fine-tuning may improve its performance. Identify the most frequently used query using the select statement with order by:
select elap_avg, cnt, qtext from sysquerymetrics order by cnt
elap_avg cnt qtext ----------- ----------- -------------------------------------------------- 0 1 select c1, c2 from t_metrics1 where c1 = 333 16 2 select distinct c1 from t_metrics1 where c2 in (select c2 from t_metrics2) 24 3 select min(c1) from t_metrics1 where c2 in (select t_metrics2.c2 from t_metrics2, t_metrics3 where (t_metrics2.c2 = t_metrics3.c2 and t_metrics3.c3 = 1)) 78 4 select count(t_metrics1.c1) from t_metrics1, t_metrics2, t_metrics3 where (t_metrics1.c2 = t_metrics2.c2 and t_metrics2.c2 = t_metrics3.c2 and t_metrics3.c3 = 0) (4 rows affected)
The best candidate for tuning can be seen in the last statement of the above results, which has the biggest value (78).
