• Questions
• # Explain plan cardinality and cost

Thanks for the question, Santhosh.

Asked: March 22, 2017 - 9:22 am UTC

Last updated: August 11, 2022 - 4:32 am UTC

Version: 11G

Viewed 100K+ times! This question is

Hi,

I checked EXPLAIN PLAN of a Select Query(In SQL Devloper Tool by pressing F10 Short cut Key), there i noticed one column Cardinality with COST.

As Cardinality Increases i noticed increase in COST, i read Optimizer chooses less cost plan to Execute the Queries so i am thinking Cost is the Time Taken by the optimzer to execute the query. then what is this cardinality? and relation of its with Cost.

## and Chris said...

Cardinality is the estimated number of rows the step will return.

Cost is the estimated amount of work the plan will do.

A higher cardinality => you're going to fetch more rows => you're going to do more work => the query will take longer. Thus the cost is (usually) higher.

All other things being equal, a query with a higher cost will use more resources and thus take longer to run. But all things rarely are equal. A lower cost query can run faster than a higher cost one!

The cost is useful for figuring out why the optimizer chose (e.g.) a full table scan over an index. But in most cases ignore it.

A better question to ask is:

"Is the (estimated) cardinality roughly equal to the actual number of rows the statement processed?"

If the answer is yes for all steps of the plan, it's likely you have the "best" plan for your query.

To figure this out, you need the execution plan. This shows you the number of rows processed. For instructions on how to create these, see:

https://blogs.oracle.com/sql/entry/how_to_create_an_execution

Then compare the estimates to the actuals and see if they're close. Note you need to multiply the values in the "starts" column to those in the "estimated" to get the value to compare to actual rows.

Final note: if you're trying to get the estimated time, explain plans include this:

```set serveroutput off
explain plan for
select * from dual;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 4017058736

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
```

Just remember: it is an estimate and only granular to one second!

## Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

A reader, March 22, 2017 - 1:29 pm UTC

Please Explain What is the Difference between auto trace and Explain Plan with Real time Example.

March 23, 2017 - 3:31 am UTC

The issue is that they are not always a realistic view of what will actually take place. eg

```
SQL> create table t as
2  select d.* , cast(object_id as varchar2(20)) abc
3  from dba_objects d;

Table created.

SQL>
SQL> create index t_ix on t ( abc );

Index created.

SQL>
SQL> variable b1 number
SQL> exec :b1 := 12345

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select created
2  from t
3  where abc = :b1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3343387620

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     1 |    14 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_IX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ABC"=:B1)

SQL>
```

So based on that, I might think my query is fine. Let's see what *really* happens when I run it:

```SQL> set autotrace off
SQL> select created
2  from t
3  where abc = :b1;

CREATED
---------
08-MAR-17

1 row selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b4dcv66pbub14, child number 0
-------------------------------------
select created from t where abc = :b1

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   450 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    14 |   450   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER("ABC")=:B1)

18 rows selected.

SQL>
SQL>
```

I didnt use the index because my data types didn't match. So 'explain plan' and 'autotrace' can be misleading.

gary stephens, October 16, 2017 - 6:55 pm UTC

Seems to happen a lot .... thanks for what you do, though!
October 17, 2017 - 3:14 am UTC

Wendy H, December 12, 2017 - 1:41 pm UTC

I feel like I have read a thousand articles about reviewing an explain plan. This is as boiled down as it gets. Thank you!!
December 12, 2017 - 3:12 pm UTC

Thanks! :)

### explain plan in SQL developer

alan koh, August 01, 2022 - 5:57 pm UTC

hi all

Does anyone know explain plan F10 function in SQL developer actually display the real execution plan or just explain plan ?

thank you
August 02, 2022 - 4:03 pm UTC

Check what this key is bound to:

Preferences -> Shortcut Keys -> type F10 in the search at the top

Explain plan => prediction
Autotrace => execution plan (reality)

### explain plan in SQL developer

alan koh, August 03, 2022 - 3:21 am UTC

thanks chris

how can we know if the autotrace in sql developer is displaying the actual plan ?

if i were to run
set autotrace on as script in sql developer - is it still the actual plan ?

August 03, 2022 - 1:00 pm UTC

The best way to be sure it's the "real" plan is to see if the A(ctual)-rows, A(ctual)-time, buffers, columns etc. are populated in the plan. Without this information, it's tough to know if the plan is "good", even if you're seeing the actual plan used.

To get this information either you need to add the gather_plan_statistics hint or set statistics_level to ALL.

Doing this in SQL Developer and running in script mode I see:

```alter session set statistics_level = all;
set autotrace on

select count(*) c from t;

C
----------
4

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  gkr95yuc0xv76, child number 0
-------------------------------------
select count(*) c from t

Plan hash value: 1750303431

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE       |              |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   INDEX FAST FULL SCAN| SYS_C0033689 |      1 |      4 |      4 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------```

So yes, that's the "real" plan.

### autotrace

alan koh, August 10, 2022 - 4:34 pm UTC

hi chris

the 1st reply from tom kyte way back was the explain plan from autotrace is a prediction and not the actual plan

in the same thread - you did mentioned that the autotrace from sql developer is the real plan and earlier you just demonstrated that running autotrace from sql developer script mode return the actual plan

so the plan return from autotrace is actually dependent on the tool being use ?
August 11, 2022 - 4:32 am UTC

Yes.

There is no *database* command called "autotrace". It is simply an implementation of a tool being used.

Autotrace in SQL Dev and SQLcl mimic to some degree that of the old style autotrace in SQL Plus, but we have endeavoured to make them evolve to be better over time

# More to Explore

##### Performance

Get all the information about database performance in the Database Performance guide.