Oracle – Cost and Cards

Last month I have received one question.In the explain plan the COST & CARDS is based on what? In this article we will see what it means and its usage.

First of all we see about the Cost.

COST

Cost of the operation as estimated by the optimizer’s cost-based approach.

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.

For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations.

CPU_COST

CPU costs depend upon system load – The CPU costs associated with servicing an Oracle query depend upon the server load, and CPU costs are generally not important unless the entire Oracle instance is using excessive CPU resources.

IO_COST

Oracle estimates the I/O cost of the SQL based upon its knowledge of the settings for db_file_multiblock_read_count, the tablespace block size and the presence of indexes. Oracle does NOT use data buffer statistics because Oracle cannot have an a-priori knowledge of whether a desired data block is already cached in the RAM data buffers.

Next one is CARDS

Estimate by the cost-based approach of the number of rows accessed by the operation.

Example:

1. Execution plan

SQL> select * from dba_objects

2 where status=’INVALID’

3 order by object_name;

———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=75 Card=263 Bytes=46551)
1 0 SORT (ORDER BY) (Cost=75 Card=263 Bytes=46551)
2 1 VIEW OF ‘DBA_OBJECTS’ (VIEW) (Cost=74 Card=263 Bytes=46551)
3 2 UNION-ALL
4 3 FILTER
5 4 HASH JOIN (Cost=74 Card=281 Bytes=25852)
6 5 TABLE ACCESS (FULL) OF ‘USER Card=336 Bytes=5376)
7 5 TABLE ACCESS (FULL) OF ‘OBJ ard=281 Bytes=21356)
8 4 TABLE ACCESS (BY INDEX ROWID) OF ‘IND (Cost=2 Card=1 Bytes=8)
9 8 INDEX (UNIQUE SCAN) OF ‘I_IND1’ (INDEX (UNIQUE)) (Cost=1 Card=1)
10 3 FILTER
11 10 HASH JOIN (Cost=8 Card=27 Bytes=945)
12 11 TABLE ACCESS (FULL) OF ‘LINK ard=27 Bytes=513)
13 11 TABLE ACCESS (FULL) OF ‘USER Card=336 Bytes=5376)

Here CARD means number of rows accessed by this query.

Bytes –> No of bytes records fetched by query

Here Cost=75 Card=263 Bytes=46551

2. Explain plan

SQL> Explain plan for select * from dba_objects
2 where status=’INVALID’
3 order by object_name;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
————————————————————————————————–
Plan hash value: 125482147

———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 263 | 46551 | 75 (4)| 00:00:02 |
| 1 | SORT ORDER BY | | 263 | 46551 | 75 (4)| 00:00:02 |
| 2 | VIEW | DBA_OBJECTS | 263 | 46551 | 74 (3)| 00:00:02 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | HASH JOIN | | 281 | 25852 | 74 (3)| 00:00:02 |
| 6 | TABLE ACCESS FULL | USER$ | 336 | 5376 | 5 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | OBJ$ | 281 | 21356 | 69 (3)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
|* 10 | FILTER | | | | | |
|* 11 | HASH JOIN | | 27 | 945 | 8 (13)| 00:00:01 |
| 12 | TABLE ACCESS FULL | LINK$ | 27 | 513 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | USER$ | 336 | 5376 | 5 (0)| 00:00:01 |
———————————————————————————————–

I Hope this article helped you to understand about cost and cards.Suggestions are welcome.