---
title: Consistent Gets not the Best Way to Look at Query Performance
description: This Consistent Gets is the only thing that matters? It's not, that's why Statspack and AWR provide the top queries sorted by Consistent Gets. Check for yourself.
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [Consistent Gets not the Best Way to Look at Query Performance](https://www.pythian.com/blog/consistent-gets-not-best-way-to-look-at-query-performance)

 Written by [Pythian Marketing](https://www.pythian.com/blog/author/pythian-marketing) | Aug 13, 2008 4:00:00 AM

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](https://blog.pythian.coms/author/shamsudeen) for opening my eyes!

## Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

 

[View full post](https://www.pythian.com/blog/consistent-gets-not-best-way-to-look-at-query-performance)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Pythian Marketing"
  },
  "dateModified" : "2026-04-04T02:06:45.799Z",
  "datePublished" : "2008-08-13T04:00:00Z",
  "headline" : "Consistent Gets not the Best Way to Look at Query Performance",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/consistent-gets-not-best-way-to-look-at-query-performance",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```