CODEWITHSUNDEEP
oracle-databaseperformancesql-execution-plansql-optimization
emilly
emilly

Reputation: 10538

Should Plan cost be less than 100?

I know lesser the cost faster will be query. But recently i came across the statement that cost should always be less than 100 . Is that true ?

As per my understanding cost is proportioal to (IO operation/CPU usage/network resources used). Which means when i say cost is high ,any/all of these three operations i.e IO operation/CPU usage/network resources usage can be high . Is that correct ?

Upvotes: 1

Views: 105

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

A cost should be accurate. Unless we're talking about examining different plans for the same query from a 10053 trace, worry far more about accuracy rather than whether a cost is low or high.

If you have a query that is hitting millions of rows and performing a bunch of aggregates, a plan with a cost of 10 is likely to be highly inefficient. Logically, you know that the query is going to be pretty expensive. If the optimizer thinks that the query is very cheap, that almost guarantees that your statistics are inaccurate. If your statistics are inaccurate, that means that the optimizer is going to pick a plan that is less efficient than it should be. Personally, I wouldn't be surprised if I've identified more queries that needed attention by noticing a cost that is way too low than by noticing a cost that is way too high.

Picking any sort of cut-off for cost is ridiculous. If you know that every query is going to be part of an OLTP application that fetches a few dozen rows, it might make sense to take a second look at a plan with a cost in the thousands to see if the optimizer picked a bad plan because of invalid statistics. But very, very few systems only have that very specific sort of OLTP query-- most systems have a miss of very cheap queries that return a row or two and more expensive queries that read and aggregate larger numbers of rows. Creating any sort of cap tends to lead developers to do stupid things-- putting "cheap" queries inside a loop rather than writing an "expensive" join may mean that all your queries have low cost but that your entire system runs much less efficiently than it could.

Upvotes: 3

Related Questions