Problem solve Get help with specific problems with your technologies, process and projects.

Cards and costs explained

With the cost based optimizer, can you please explain the output of explain plan, especially the "cards" and "costs" values. Is getting a higher value for cards and costs good, or are lower values good?

Cards means cardinality and shows for that specific operation the number of rows it will return. The costs columns is the cost, relative to this query only, of the operation. Usually, to begin tuning, you look to the high cost operations. For cardinality there is no right or wrong value, it is just a measure, however it can help indicate that your nested loop may be inverted (you are driving 10,000 - 1 row scans instead of 1 - 10,000 row scan). The cost value on the other hand should be as low as achievable.

Don Burleson adds:

Understand the execution plan is not a simple task, and there are some third-party tools that make "explain plans" easier to read.

The "cost" values depend heavily on your Oracle optimizer init.ora parameters: http://www.dba-oracle.com/art_otn_cbo_p1.htm.

It also depends on whether you are considering CPU costing too: http://www.dba-oracle.com/oracle_tips_optimizer_cost_model.htm.

The "card" column is the estimated number of rows returned in an intermediate table join (or group-by) operation, and it is important that Oracle join the tables together such that the amount of intermediate row baggage is minimized: http://www.dba-oracle.com/oracle_tips_histograms.htm.

For more details, I might suggest the Kimberly Floss book on Oracle CBO internals.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.