What is the meaning of Cost, Cardinality and Bytes in Explain Plan?

Question

This is a simple query: select * from customers

When I write this query in PL/SQL Developer and press F5, I see Explain Plan, but I don't know what are Cost, Cardinality and Bytes represent.

Answer 1

See section 12.10 for a description of the plan table columns.

https://docs.oracle.com/cd/E11882_01/server.112/e41573/ex_plan.htm#PFGRF009

Cost is the amount of work the optimizer estimates to run your query via a specific plan. The optimizer generally prefers lower cost plans.

Cost is determined by several different factors but the table statistics are one of the largest.

Cardinality is the number of rows the optimizer guesses will be processed for a plan step. If the stats are old, missing, or incomplete - then this can be wildly wrong. You want to look for where the optimizer sees 5 rows (cardinality) but in reality there are 50,000 rows.

Bytes are same concept as cardinality but in sheer terms of data to be processed vs rows in a table.

This is an extremely deep topic that requires active learning and experience. I'm sure many can contribute ideas for places to go. I personally enjoy Maria's blog. She's the former product manager for the optimizer.

Answer 2

From http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf:

What is Cost?

The Oracle Optimizer is a cost-based optimizer. The execution plan selected for a SQL statement is just one of the many alternative execution plans considered by the Optimizer. The Optimizer selects the execution plan with the lowest cost, where cost represents the estimated resource usage for that plan. The lower the cost the more efficient the plan is expected to be. The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query. Figure 8: Cost is found in the fifth column of the execution plan The cost of the entire plan (indicated on line 0) and each individual operation is displayed in the execution plan. However, it is not something that can be tuned or changed. The cost is an internal unit and is only displayed to allow for plan comparisons.

You can also look in the Database Performance Tuning Guide, where it says:

The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.

So - COST is a dimensionless value which is a function of CPU and IO cost.