What is the meaning of Cost, Cardinality and Bytes in Explain Plan?
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
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.