Consistent Gets not Necessarily the Best Way to Look at Query Performance
Posted by Grégory Guillou on Aug 13, 2008
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:
