NB: Ces tests ont été effectués sur une base 10.2.0.3 sous Linux X86_64

Celui qui vous a dit que comparer les ordres SQL les plus consommateurs de « Buffer Gets » était la meilleure façon de comparer les ordres SQL vous a trompé. Si AWR ou Statspack classe les ordres SQL selon plusieurs axes, c’est que les Buffer Gets ne sont pas toujours significatifs de la consommation des ordres SQL; Un exemple vaut toutes les palabres

Créer un schéma exemple

Créez une table pour les besoins de cette illustration.

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');

Cas 1 : 4164 Consistent Gets et 0.14 secondes

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)| Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    2 |  998   (7)| 00:00:12 |
|   1 |  SORT AGGREGATE       |      |     1 |    2 |           |          |
|   2 |   VIEW                |      |     1 |    2 |  998   (7)| 00:00:12 |
|   3 |    SORT UNIQUE NOSORT |      |     1 |   14 |  998   (7)| 00:00:12 |
|*  4 |     HASH JOIN         |      |    10 |  140 |  997   (6)| 00:00:12 |
|*  5 |      TABLE ACCESS FULL| X1   |     1 |    9 |  494   (6)| 00:00:06 |
|   6 |      TABLE ACCESS FULL| X1   |  1002K| 4895K|  491   (5)| 00:00:06 |
----------------------------------------------------------------------------

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

Cas 2 : 4164 Consistent Gets et 4.17 secondes

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 |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    1 |        |       | 24821   (6)| 00:04:58 |
|   1 |  SORT AGGREGATE       |      |    1 |        |       |            |          |
|   2 |   VIEW                |      | 1002K|        |       | 24821   (6)| 00:04:58 |
|   3 |    HASH UNIQUE        |      | 1002K|    13M |  255M | 24821   (6)| 00:04:58 |
|*  4 |     HASH JOIN         |      |   10M|   134M |   16M |  2932   (7)| 00:00:36 |
|   5 |      TABLE ACCESS FULL| X1   | 1002K|  4895K |       |   491   (5)| 00:00:06 |
|   6 |      TABLE ACCESS FULL| X1   | 1002K|  8811K |       |   491   (5)| 00:00:06 |
--------------------------------------------------------------------------------------

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 processed

Conclusion

Ne vous fiez qu’à vos tests et supprimez X1 :

drop table X1 purge;