Blog

le 03/02/2007
Thomas Bordet
Archives blog-arkzoyd @fr

Exemple d'ordre SQL #2 /+Anti-Pattern/

Les statistiques sont probablement une des clés les plus importantes du bon fonctionnement d’Oracle 10g. En effet l’optimiseur dit « CBO : Cost Based Optimizer » calcule les plans d’exécution des requêtes SQL en fonction de la manière dont les données sont distribuées. Dans l’exemple qui suit, vous allez voir les effets des statistiques. Cette exemple a été réalisé avec Oracle 10.2.0.3 sur Windows.

SQL> create table demo#2 (id number primary key,
col1 number,
col2 varchar2(4000));

create index demo#2_col1_idx on demo#2(col1);

exec dbms_stats.gather_table_stats(USER, ‘DEMO#2’ –
, cascade=>true, method_opt=>’FOR ALL COLUMNS SIZE AUTO’);

begin
for i in 1..50000 loop
insert into demo#2 values (i,1,
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);

insert into demo#2 values (50000+i,1+i,
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
end loop;
commit;
end;
/

Quelques remarques à propos du script qui précède :

La table demo#2 a des statistiques éronnées puisqu’elle ont été prise alors que la table est vide
Les données de la colonne COL1 sont indexées. Elles sont distribuées de manière non homogène puisque la valeur 1 représente en fréquence la moitié des valeurs de la colonne.

SQL*Tuning Advisor
Comme dans le Post précédent nous allons utiliser le SQL Tuning advisor pour cette fois voir si l’instance ne peut pas gérer dans les meilleurs condition l’ordre « SELECT count(COL2) FROM demo#2 where col1=1 ». Comme dans le Post précédent, exécutez le script correspondant :

SQL> variable gg varchar2(100)
SQL> exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
      sql_text    => 'select count(col2) from demo#2 where col1=1', -
      user_name   => 'SYSTEM', -
      scope       => 'COMPREHENSIVE', -
      time_limit  => 30, -
      task_name   => 'DEMO#2', -
      description => 'Tuning d''un accès simple à la table DEMO#2');
SQL> exec dbms_sqltune.execute_tuning_task( task_name => 'DEMO#2' );

Pour affichez le résultat du SQL Tuning Advisor, tapez :

SQL > set long 10000
SQL > set longchunksize 10000
SQL > set lines 100
SQL > select dbms_sqltune.report_tuning_task('DEMO#2')
     from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#2')
----------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
Tuning Task Name                  : DEMO#2
Tuning Task Owner                 : SYSTEM
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 30
Completion Status                 : COMPLETED
Started at                        : 02/03/2007 15:14:45
Completed at                      : 02/03/2007 15:15:01
Number of Statistic Findings      : 1

----------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID     : 0f0qzt2ydjvqj
SQL Text   : select count(col2) from demo#2 where col1=1

----------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
----------------------------------------------------------------------------

1- Statistics Finding
---------------------
Les statistiques de l'optimiseur pour la table "SYSTEM"."DEMO#2" sont
obsolÞtes.

Recommendation
--------------
- Envisagez de collecter des statistiques d'optimiseur pour cette table et
 ses index.
 execute dbms_stats.gather_table_stats(ownname => 'SYSTEM', tabname =>
         'DEMO#2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
         method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Rationale
---------
 L'optimiseur exige des statistiques à jour de la table et de ses index
 afin de sélectionner un bon plan d'exécution.

----------------------------------------------------------------------------
EXPLAIN PLANS SECTION
----------------------------------------------------------------------------
[...]

SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('DEMO#2');

Le SQL Tuning Advisor conseille effectivement d'exécutez la prise de statistiques.

Prise de statistiques
Si vous prenez les statistiques comme recommandé, et que vous regardez le plan d'exécution de l'ordre SQL, vous constatez que vous passez par l'index.

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYSTEM', -
      tabname => 'DEMO#2', -
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
      method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
      cascade => TRUE);

SQL> set autotrace on
SQL>  select count(col2)
  from demo#2
  where col1=1;

COUNT(COL2)
-----------
   50000


Plan d'exécution
----------------------------------------------------------
Plan hash value: 559471190

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  1005 |  3149   (1)| 00:0
0:38 |
|   1 |  SORT AGGREGATE    |        |     1 |  1005 |            |          |
|*  2 |   TABLE ACCESS FULL| DEMO#2 | 49606 |    47M|  3149   (1)| 00:00:38 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("COL1"=1)


Statistiques
----------------------------------------------------------
       1  recursive calls
       0  db block gets
   14297  consistent gets
    7422  physical reads
       0  redo size
     422  bytes sent via SQL*Net to client
     396  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed

Remarque à propos des index
Vous remarquerez que dans ce cas le passage par l'index est moins performant. Exécutez plusieurs fois la requête ci-dessous pour vous en persuadez :

SQL> set timing on
SQL> set autotrace off
SQL> select /*+FULL(demo#2)*/ count(col2)
    from demo#2
    where col1=1;

COUNT(COL2)
-----------
      50000

Ecoulé : 00 :00 :00.06

SQL> select /*+INDEX(demo#2)*/ count(col2)
    from demo#2
    where col1=1;

COUNT(COL2)
-----------
      50000

Ecoulé : 00 :00 :00.10

Voilà... Dans un prochain "Post" vous verrez que le SQL Tuning Advisor ne descend pas dans le détail de comment passer les statistiques.

GarK!

Thomas Bordet

Voir tous ses articles

Blog

Exemple d'ordre SQL #2 /*+Anti-Pattern*/

Articles récents

Exemple d'ordre SQL #2 /+Anti-Pattern/