Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Santhosh.

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

Last updated: December 12, 2017 - 3:12 pm UTC

Version: 11G

Viewed 100K+ times! This question is

You Asked

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!

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:313416745628
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5639863000346345766
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:40112614814595

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

  (3 ratings)

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

Comments

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.


Thanks in Advance.
Connor McDonald
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.

Broken link

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

The link in this article is broken: https://blogs.oracle.com/sql/entry/how_to_create_an_execution

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

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

Looks like it got truncated somehow

Great answer

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!!
Chris Saxon
December 12, 2017 - 3:12 pm UTC

Thanks! :)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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