Database Reference
In-Depth Information
Formula 7-1.
The estimated CPU cost to access a column depends on its position in the table. This formula gives the
cost of accessing one row. If several rows are accessed, the CPU cost increases proportionally. Chapter 16 provides further
information on why the position of a column is relevant
.
cpu_cost
=
column_position20
⋅
The following example, which is an excerpt of the
cpu_cost_column_access.sql
script, shows Formula 7-1 in
action. A table with nine columns is created, one row is inserted, and then with the
EXPLAIN PLAN
statement, the CPU
cost of independently accessing the nine columns is displayed. See Chapter 10 for detailed information about this SQL
statement. Notice how there's an initial CPU cost of 35,757 to access the table, and then for each subsequent column,
a CPU cost of 20 is added. At the same time, the I/O cost is constant. This makes sense because all columns are stored in the
very same database block, and therefore the number of physical reads required to read them is the same for all queries:
SQL> CREATE TABLE t (c1 NUMBER, c2 NUMBER, c3 NUMBER,
2 c4 NUMBER, c5 NUMBER, c6 NUMBER,
3 c7 NUMBER, c8 NUMBER, c9 NUMBER);
SQL> INSERT INTO t VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9);
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c1' FOR SELECT c1 FROM t;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c2' FOR SELECT c2 FROM t;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c3' FOR SELECT c3 FROM t;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c4' FOR SELECT c4 FROM t;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c5' FOR SELECT c5 FROM t;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c6' FOR SELECT c6 FROM t;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c7' FOR SELECT c7 FROM t;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c8' FOR SELECT c8 FROM t;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'c9' FOR SELECT c9 FROM t;
SQL> SELECT statement_id, cpu_cost AS total_cpu_cost,
2 cpu_cost-lag(cpu_cost) OVER (ORDER BY statement_id) AS cpu_cost_1_coll,
3 io_cost
4 FROM plan_table
5 WHERE id = 0
6 ORDER BY statement_id;
STATEMENT_ID TOTAL_CPU_COST CPU_COST_1_COLL IO_COST
------------ -------------- --------------- -------
c1 35757 3
c2 35777 20 3
c3 35797 20 3
c4 35817 20 3
c5 35837 20 3
c6 35857 20 3
c7 35877 20 3
c8 35897 20 3
c9 35917 20 3
The I/O and CPU costs are expressed with different units of measurement. Obviously then, the overall cost of a
SQL statement can't be calculated simply by summing up the costs. To solve this problem, the query optimizer uses
Formula 7-2 with workload statistics. Simply put, the CPU cost is divided by
cpuspeed
to get the estimated elapsed
