This post is for those who think Consistent Gets is the only thing that matters. It’s not. That’s why Statspack and AWR provide not only the top queries sorted by Consistent Gets but also Sorted by IO, CPU, Cluster Waits, and so on. I won’t argue. Check for yourself.

I’ve run the queries that follow on top of 10.2.0.3 on Linux X86_64.

Sample Table

Create and Fill up a table to run your queries. You’ll find the script you need below:

create table X1(a number,b number);

begin
   for i in 1..1000000 loop
      insert into X1 values (i,mod(i,100000));
   end loop;
end;
/

commit;

exec dbms_stats.gather_table_stats(user, 'X1');

Case 1: 4164 Consistent Gets for 0.14 seconds

First, let’s assume that a few Consistent Gets means good performance. Look at the following query:

set timing on
set autotrace on

select count(*)
  from (select distinct X2.a
          from X1,X1 X2
         where X1.b=X2.b
           and X2.a=1);

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 4182727558

-------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    2  |  998   (7) |
|   1 |  SORT AGGREGATE       |      |     1 |    2  |            |
|   2 |   VIEW                |      |     1 |    2  |  998   (7) |
|   3 |    SORT UNIQUE NOSORT |      |     1 |   14  |  998   (7) |
|*  4 |     HASH JOIN         |      |    10 |  140  |  997   (6) |
|*  5 |      TABLE ACCESS FULL| X1   |     1 |    9  |  494   (6) |
|   6 |      TABLE ACCESS FULL| X1   |  1002K| 4895K |  491   (5) |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("X1"."B"="X2"."B")
   5 - filter("X2"."A"=1)

Statistics
----------------------------------------------------------
        1  recursive calls
        0  db block gets
     4164  consistent gets
        0  physical reads
        0  redo size
      515  bytes sent via SQL*Net to client
      492  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
        1  rows processed

Case 2: 4164 Consistent Gets for 4.17 seconds

The beauty of this case is that the execution plan is almost the same and the autotrace statistics are exactly the same:

set timing on
set autotrace on

select count(*)
  from (select distinct X2.a
          from X1,X1 X2
         where X1.b=X2.b);

COUNT(*)
----------
   1000000

Elapsed: 00:00:04.17

Execution Plan
----------------------------------------------------------
Plan hash value: 920584761

-----------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    1 |        |24821   (6)|
|   1 |  SORT AGGREGATE       |      |    1 |        |           |
|   2 |   VIEW                |      | 1002K|        |24821   (6)|
|   3 |    HASH UNIQUE        |      | 1002K|    13M |24821   (6)|
|*  4 |     HASH JOIN         |      |   10M|   134M | 2932   (7)|
|   5 |      TABLE ACCESS FULL| X1   | 1002K|  4895K |  491   (5)|
|   6 |      TABLE ACCESS FULL| X1   | 1002K|  8811K |  491   (5)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
    4 - access("X1"."B"="X2"."B")

Statistics
----------------------------------------------------------
        0  recursive calls
        0  db block gets
     4164  consistent gets
        0  physical reads
        0  redo size
      515  bytes sent via SQL*Net to client
      492  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
        1  rows processes

Conclusion

Don’t trust “the common wisdom”, even if you’ve experienced it dozens of times. Oh, and don’t forget to drop the table:

drop table X1 purge;

P.S.: Thank you Riyaj for opening my eyes!