Search code examples
sqloracle-databasedatabase-performance

Inconsistent Time in Oracle Explain Plan


I have following query with its EXPLAIN PLAN shown below.

The query take 2.3 seconds.

But EXPLAIN PLAN shows INDEX FAST FULL SCAN show Time = 00:10:06 seconds

Any reason why ?

select count(*) from c1
COUNT(*)
----------
  68656440

Elapsed: 00:00:02.31

Execution Plan
----------------------------------------------------------
Plan hash value: 2610173813

-------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 | 50450   (1)| 00:10:06 |
|   1 |  SORT AGGREGATE       |         |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PF_TEST |    83M| 50450   (1)| 00:10:06 |
-------------------------------------------------------------------------

Note
-----

- dynamic sampling used for this statement (level=2)

Statistics


      0  recursive calls
      0  db block gets
 185865  consistent gets
 185820  physical reads
      0  redo size
    529  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Solution

  • explain plan is an estimate of what oracle think it will take to execute the statement, try this:

    set timi on
    select count(*) from c1;
    set timi off
    
    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    

    Then look at the A-time column, is it accurate? It should be.