CODEWITHSUNDEEP
sqloracle-database
Lilienthal
Lilienthal

Reputation: 4378

Optimising Queries With Already Low Cost

I've been doing some basic optimising of my SQL queries by indexing and clustering but have come upon what is perhaps an odd question. Most of my queries have costs that range from the high hundreds to the low thousands and I've added indices for them, bringing their costs down.

However, some of my queries already have a very low cost, usually a single digit. Can I forget about these? Is it still worth optimising these queries or should I not put in the effort?

Basically, I'd like to know if there's a general range or metric I can use to determine if a query should be optimised further or not. If I should determine this on a case-by-case basis, it would be helpful to have some guidelines to use in deciding which queries to optimise.

Upvotes: 1

Views: 138

Answers (2)

David Aldridge
David Aldridge

Reputation: 52376

The easy answer would be that low-cost queries are significant if the number of executions makes them so.

The tricky part is that a low estimated cost may not mean a low execution cost -- and the same applies to high cost queries.

I would definitely look at AWR or Statspack reports to see what your most significant queries are, and also apply your own common sense to judge whther execution plans look optimal for what the query is meant to do.

Upvotes: 2

the_lotus
the_lotus

Reputation: 12748

There's no strict guideline. You might want to get statistique on how long a query last and how many time it is executed. Also depend on the criticality of the query (is it for real-time data or for running a single report).

If a query takes 10 second but is executed 1 times a day then you might not need to optimize it.
If a query takes 1 second but is executed 10000 times a day then you might need to optimize it.

Upvotes: 1

Related Questions