Menu
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.
737
The cardinality is the estimated number of rows that will be returned by each operation. The Optimizer determines the cardinality for each operation based on a complex set of formulas that use both table and column level statistics as input (or the statistics derived by dynamic sampling).
The cost of the plan is reduced by rewriting the SQL statement to use an EXISTS . This plan is more effective, because two indexes are used to satisfy the predicates in the parent query, thus returning only a few employee_ids . The employee_ids are then used to access the orders table through an index.
The cost column is essentially an estimate of the run-time for a given operation. In sum, the cost column is not valuable for SQL tuning, because the "best" execution plan may not be the one with the lowest cost.
The cost of execution of query 1 is around 400 and the cost of execution of Query 2 is 200. But the execution time seems to be reverse. Query 1 executes faster and query 2 seems to execute slower. I checked this out by setting the "set timing on" command on sqlplus.
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.
156
From http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf:
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.
29
answered Oct 10 '22 23:10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
