Unit of COST in an Oracle execution plan

explainoracle-11g-r2

Is there a unit for cost in an Oracle execution plan?

I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage? What does this number stand for?

Is there any way I can deduce how many seconds or ms this operation is going to take to complete?

Best Answer

Unfortunately the answers are as follows:

Is there a unit for cost in an Oracle execution plan?

Not really.

I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage?

Nope.

What does this number stand for?

It's defined like this: (see the glossary)

A numeric internal measure that represents the estimated resource usage for an execution plan. The lower the cost, the more efficient the plan.

That's about all you'll get in terms of definition of that number.

Is there any way I can deduce how many seconds or ms this operation is going to take to complete?

No. It's a value that, in my experience, should only be used to compare different plans for the same query, and then with a bit (well, a lot) of salt. If one plan has a cost of 10, and another a cost of 1M, chances are the first will be faster. If the costs are close, chances are they'll have similar runtime behavior, but that's not always the case.

The The Oracle Optimizer - Explain the Explain Plan (PDF) document has a few words on that value, but:

The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query. [...]
The cost is an internal unit and is only displayed to allow for plan comparisons.

How does Oracle calculate the cost in an explain plan? on Stack Overflow has some good input related to that cost value (including a "formula" for Oracle 9i), and good advice from Justin Cave: the cardinality estimates are more meaningful, and you can actually compare them with reality.

Related Question