Consistent Gets not the Best Way to Look at Query Performance

Aug 13, 2008 / By Grégory Guillou

Tags:

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!

3 Responses to “Consistent Gets not the Best Way to Look at Query Performance”

  • Narendra says:

    Hi,

    I did not quite get your point.
    Can you please elaborate how your example explains your point ?
    I could see that you are comparing 2 different queries for consistent gets / response time. The only interesting observation here (for me) was though first query returned count of 1 and second query returned count of 1000000, in both cases, consistent gets were same. I guess that effectively means Oracle was able to access same number of blocks, irrespective of “accessing” a single row or “accessing” 1000000 rows.
    It would be interesting to see TKProf to determine the WAIT events for both queries.

  • Jan says:

    Number of rows matters!

    I saw it several times, usually after applying SQL Profiles, that the LIO went down, but the execution time went up. I had to look at execution plans. Sometimes it was because of an increased number of rows in the execution plan, sometimes because of PIO increased (less frequent blocks were used hence they were not in a buffer very often).

    My rule of thumb during SQL tuning: Filters must be applied first, joins are the second.

  • Maniek says:

    Excellent test of hash join performance ;)

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>