Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

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

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

  (6 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! :)

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
Chris Saxon
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 ?


Chris Saxon
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

thanks for your reply

https://asktom.oracle.com/pls/apex/asktom.search?tag=difference-of-explain-plan-autotrace-and-tkprof

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 ?
Connor McDonald
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

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.