Ultimately something's different between the executions. There are many possibilities here, here are a couple of the most common reasons for what you've observed:

- When the query is slow it's processing more data than when fast
- When the query is fast it's reading data cached in memory, when slow it's reading data from disk

In the example below, the first query reads one row for memory. This takes less than a tenth of a second.

Then I clear the buffer cache, so the second query reads 100,000 rows from disk. This takes ~8 seconds.

Yet both have the same plan!

So a possible reason for what you've observed is:

- The slow run reads lots of data from disk
- Gathering stats loads this data into the cache
- The fast runs after read less data from memory

There may be other differences which lead to the behaviour you see. To see what's happened capture the plan stats as I've done below by setting statistics_level = all, or add the gather_plan_statistics hint to the query. If you have it available you could also check the SQL Monitor.

If - after capturing this information - you're still struggling to spot the difference, post the plans here and we'll see how we can help.

create table t ( 
  c1 int, c2 int, 
  c3 varchar2(1000) default rpad ( 'stuff', 1000, 'f' ),
  c4 varchar2(1000) default rpad ( 'stuff', 1000, 'f' ) 
);

insert into t ( c1, c2 )
  with rws as ( select level x from dual connect by level <= 100000 ) 
  select 0, x from rws;
insert into t ( c1, c2 )
  with rws as ( select level x from dual connect by level <= 10 ) 
  select x, x from rws;

commit;
create index i on t ( c1 );

set timing on
set serveroutput off
alter session set statistics_level = all;

var v1 number;
exec :v1 := 1;

-- Fetch one row from disk = fast
select count ( distinct c2 ) from t
where  c1 = :v1;

COUNT(DISTINCTC2)
-----------------
                1

Elapsed: 00:00:00.05

select * from dbms_xplan.display_cursor( format => 'ROWSTATS LAST');

Plan hash value: 1398793779

--------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |      1 |        |      1 |
|   1 |  SORT AGGREGATE                        |          |      1 |      1 |      1 |
|   2 |   VIEW                                 | VW_DAG_0 |      1 |      1 |      1 |
|   3 |    HASH GROUP BY                       |          |      1 |      1 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |      1 |      1 |
|*  5 |      INDEX RANGE SCAN                  | I        |      1 |      1 |      1 |
--------------------------------------------------------------------------------------

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

   5 - access("C1"=:V1)

-- Clear data from memory
alter system flush buffer_cache;

exec :v1 := 0;

-- Fetch 100,000 rows from disk => slow
select count ( distinct c2 ) from t
where  c1 = :v1;

COUNT(DISTINCTC2)
-----------------
           100000

Elapsed: 00:00:08.18

select * from dbms_xplan.display_cursor( format => 'ALLSTATS LAST');

Plan hash value: 1398793779

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |      1 |        |      1 |00:00:08.15 |   50176 |  33529 |       |       |          |
|   1 |  SORT AGGREGATE                        |          |      1 |      1 |      1 |00:00:08.15 |   50176 |  33529 |       |       |          |
|   2 |   VIEW                                 | VW_DAG_0 |      1 |      1 |    100K|00:00:08.15 |   50176 |  33529 |       |       |          |
|   3 |    HASH GROUP BY                       |          |      1 |      1 |    100K|00:00:08.15 |   50176 |  33529 |  5838K|  2749K| 4538K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |      1 |    100K|00:00:08.10 |   50176 |  33529 |       |       |          |
|*  5 |      INDEX RANGE SCAN                  | I        |      1 |      1 |    100K|00:00:00.05 |     183 |    192 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("C1"=:V1)