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:

(more…)