EXPLAIN PLAN/DBMS_SPACE : estimation du temps/espace disque avec CREATE Table, Index - EXPLAIN PLAN and DBMS_SPACE : estimation

21 octobre 2019

EXPLAIN PLAN/DBMS_SPACE : estimation du temps/espace disque avec CREATE Table, Index - EXPLAIN PLAN and DBMS_SPACE : estimation

Introduction
Pour estimer la durée d'un CREATE TABLE, d'un CREATE INDEX voir d'un ALTER INDEX ... REBUILD, on peut utiliser la commande EXPLAIN PLAN. Celle-ci ne sert pas qu'à voir le plan d'exécution d'un SELECT, la doc Oracle dit qu'elle gère les opérations suivantes : SELECT, INSERT, UPDATE, DELETE, MERGE, CREATE TABLE, CREATE INDEX, ALTER INDEX ... REBUILD.

Mais le résultat affiché est-il fiable?

Points d'attention
N/A.

Base de tests
Une base Oracle 18.

Exemples
============================================================================================
Création du jeu de test
============================================================================================
Création de mon jeu de test : une table de 100 lignes et une autre de 1 000 000 lignes.
     SQL> set timing on;
     SQL> CREATE TABLE zztest(id number, name varchar2(50 CHAR));

     SQL> begin
     for i in 1..100 loop
    INSERT INTO zztest values (i, 'MARTIN');
     end loop;
     end;
     /

     SQL> CREATE TABLE zztest02(id02 number, name02 varchar2(50 CHAR));

     SQL> begin
     for i in 1..1000000 loop
          INSERT INTO zztest02 values (i, 'DUPONT');
     end loop;
     end;
     /

     SQL> commit;

On génère les stats sur les deux tables.
     SQL> exec dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'ZZTEST');
     SQL> exec dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'ZZTEST02');


============================================================================================
EXPLAIN PLAN pour un CREATE TABLE : estimation du temps
============================================================================================
Table de 1 000 000 (un million) de lignes
On utilise EXPLAIN PLAN pour un CREATE TABLE d'une table de 1 000 000 de lignes et on regarde la colonne Time.
Elle vaut 1 seconde pour ce test.
     SQL> EXPLAIN PLAN FOR CREATE TABLE zztest_explain_create AS SELECT * FROM zztest02;

     SQL> select * from table(dbms_xplan.display);
     PLAN_TABLE_OUTPUT
     -------------------------------------------------------------------------------------------------
     Plan hash value: 999890859

     -------------------------------------------------------------------------------------------------
     | Id | Operation           | Name        | Rows | Bytes | Cost (%CPU)| Time   |
     -------------------------------------------------------------------------------------------------
     |   0 | CREATE TABLE STATEMENT       |           | 1000K|    11M| 1012   (1)| 00:00:01 |
     |   1 | LOAD AS SELECT        | ZZTEST_EXPLAIN_CREATE |   |   |          |    |
     |   2 |   OPTIMIZER STATISTICS GATHERING |           | 1000K|    11M|   653   (1)| 00:00:01 |
     |   3 |    TABLE ACCESS FULL       | ZZTEST02       | 1000K|    11M|   653   (1)| 00:00:01 |
     -------------------------------------------------------------------------------------------------

     10 rows selected.

On crée maintenant vraiment la table pour voir si EXPLAIN PLAN estime correctement le temps.
C'est OK, concordance entre l'EXPLAIN PLAN et la création de la table : 1 seconde.
     SQL> CREATE TABLE zztest_explain_create01 AS SELECT * FROM zztest02;
     Elapsed: 00:00:00.99

Table de 100 000 000 (cent millions) de lignes
Néanmoins ce test me semble trop simple, 1 seconde... c'est bien trop court pour tirer une conclusion, je vais maintenant faire un produit cartésien entre les deux tables pour vérifier si la colonne Time est correctement mise à jour. J'ai un million de lignes dans une table, cent dans une autre : cela fait une table résultat de cent millions d'enregistrements! Impossible donc de créer celle-ci en une seconde.

Aïe, les résultats n'ont rien à voir, EXPLAIN PLAN estime la durée à 5 secondes contre 2 minutes 14 secondes en vrai...
     SQL> EXPLAIN PLAN FOR CREATE TABLE zztest_explain_create AS SELECT * FROM zztest, zztest02;

     SQL> select * from table(dbms_xplan.display);
     PLAN_TABLE_OUTPUT
     -------------------------------------------------------------------------------------------------
     Plan hash value: 3414066546

     -------------------------------------------------------------------------------------------------
     | Id | Operation           | Name        | Rows | Bytes | Cost (%CPU)| Time   |
     -------------------------------------------------------------------------------------------------
     |   0 | CREATE TABLE STATEMENT       |           |   100M| 2098M|   124K (1)| 00:00:05 |
     |   1 | LOAD AS SELECT        | ZZTEST_EXPLAIN_CREATE |   |   |          |    |
     |   2 |   OPTIMIZER STATISTICS GATHERING |           |   100M| 2098M| 65158   (1)| 00:00:03 |
     |   3 |    MERGE JOIN CARTESIAN    |           |   100M| 2098M| 65158   (1)| 00:00:03 |
     |   4 |     TABLE ACCESS FULL       | ZZTEST       |   100 | 1000 |     3   (0)| 00:00:01 |
     |   5 |     BUFFER SORT        |           | 1000K|    11M| 65155   (1)| 00:00:03 |
     |   6 |      TABLE ACCESS FULL       | ZZTEST02       | 1000K|    11M|   652   (1)| 00:00:01 |
     -------------------------------------------------------------------------------------------------

     13 rows selected.

     SQL> CREATE TABLE zztest_explain_create02 AS SELECT * FROM zztest, zztest02;
     Elapsed: 00:02:13.59

     SQL> select count(*) from zztest_explain_create02;
     COUNT(*)
     ----------
     100000000

Conclusion, EXPLAIN PLAN n'est pas fiable pour estimer le temps d'un CREATE TABLE :-(
Dommage!


============================================================================================
EXPLAIN PLAN pour un CREATE INDEX : estimation du temps
============================================================================================
On peut aussi utiliser EXPLAIN PLAN pour estimer le temps d'un CREATE INDEX.
Tiens tiens, Oracle estime aussi l'espace disque de l'index en plus du temps pour le créer, ça peut être sympa :-)

Table de 1 000 000 (un million) de lignes
Là encore on est sur une seule seconde, estimée et réelle.
     SQL> EXPLAIN PLAN FOR CREATE INDEX zztest_explain_create_index ON zztest02(id02);

     SQL> select * from table(dbms_xplan.display);
     PLAN_TABLE_OUTPUT
     -------------------------------------------------------------------------------------------------
     Plan hash value: 2543093122

     -------------------------------------------------------------------------------------------------
     | Id | Operation           | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
     -------------------------------------------------------------------------------------------------
     |   0 | CREATE INDEX STATEMENT |                 |   1000K|   4882K|   1160   (1)| 00:00:01 |
     |   1 | INDEX BUILD NON UNIQUE| ZZTEST_EXPLAIN_CREATE_INDEX |         |         |          |         |
     |   2 |   SORT CREATE INDEX    |                 |   1000K|   4882K|          |         |
     |   3 |    TABLE ACCESS FULL   | ZZTEST02             |   1000K|   4882K|   653   (1)| 00:00:01 |
     -------------------------------------------------------------------------------------------------

     Note
     -----
     - estimated index size: 24M bytes

     14 rows selected.

     SQL> CREATE INDEX zztest_explain_create_index ON zztest02(id02);
     Elapsed: 00:00:00.99

Table de test de 100 000 000 (cent millions) de lignes
On va faire un nouveau test, avec la table issue du produit cartésien pour voir si l'EXPLAIN PLAN est encore correct sur une table de 100 000 000 de lignes.
     SQL> select count(*) from zztest_explain_create02;
     COUNT(*)
     ----------
     100000000

L'EXPLAIN PLAN parle de 6 secondes pour créer un index de cent millions de lignes...
     SQL> EXPLAIN PLAN FOR CREATE INDEX zztest_explain_create_index02 ON zztest_explain_create02(id);

     SQL> select * from table(dbms_xplan.display);
     PLAN_TABLE_OUTPUT
     -------------------------------------------------------------------------------------------------
     Plan hash value: 2987526290

     -------------------------------------------------------------------------------------------------
     | Id | Operation           | Name                   | Rows | Bytes | Cost (%CPU)| Time     |
     -------------------------------------------------------------------------------------------------
     |   0 | CREATE INDEX STATEMENT |                   |   100M|   286M|   152K (1)| 00:00:06 |
     |   1 | INDEX BUILD NON UNIQUE| ZZTEST_EXPLAIN_CREATE_INDEX02 |       |       |        |           |
     |   2 |   SORT CREATE INDEX    |                   |   100M|   286M|        |           |
     |   3 |    TABLE ACCESS FULL   | ZZTEST_EXPLAIN_CREATE02       |   100M|   286M|   101K (1)| 00:00:04 |
     -------------------------------------------------------------------------------------------------

     Note
     -----
     - estimated index size: 2415M bytes

     14 rows selected.

On crée maintenant l'index pour comparer les temps.
Houlalalalala, 6 secondes estimées, 3 minutes 26 secondes en réel...
SQL> CREATE INDEX zztest_explain_create_index02 ON zztest_explain_create02(id);
     Index created.

     Elapsed: 00:03:26.03

Bon, on abandonne l'EXPLAIN PLAN pour estimer le temps de création d'une table et d'un index, OK?


============================================================================================
DBMS_SPACE pour un CREATE TABLE : estimation de l'espace disque
============================================================================================
Vu que la commande EXPLAIN PLAN n'est pas fiable pour estimer le temps de création d'un objet, je ne vais pas l'utiliser pour estimer l'espace disque nécessaire d'un index. Je vais plutôt utiliser le package dédié à ce besoin, à savoir : DBMS_SPACE  https://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68105

Je réinitialise ma base de test et droppe les tables.
     SQL> drop table zztest purge;
     SQL> drop table zztest02 purge;

Table de test de 10 000 00 (pas 100 000 000)
On crée la table en premier pour avoir la valeur du champ AVG_ROW_LEN qui est indispensable quand on utilise le package DBMS_SPACE.
J'utilise cette fois une table plus grosse (10 millions de lignes) pour avoir des nombres plus réalistes lors de l'estimation.
     SQL> CREATE TABLE zztest(id number(10), name varchar2(50 CHAR)) pctfree 10;

     SQL> begin
     for i in 1..10000000 loop
          INSERT INTO zztest values (i, 'DUPONT');
     end loop;
     end;
     /

     SQL> exec dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'ZZTEST');

     SQL> select AVG_SPACE, AVG_ROW_LEN, NUM_ROWS, BLOCKS, EMPTY_BLOCKS from DBA_TAB_STATISTICS where owner = 'HR' and TABLE_NAME = 'ZZTEST';
     AVG_SPACE AVG_ROW_LEN   NUM_ROWS     BLOCKS EMPTY_BLOCKS
     ---------- ----------- ---------- ---------- ------------
     0           13          10000000      25397           0

La taille disque réelle est de 200 Mo.
     SQL> select round((25397*8192)/(1024*1024), 2) AS "Taille Mo" from dual;
     Taille Mo
     ----------
         198.41

Regardons maintenant dans DBA_SEGMENTS. La taille est sensiblement la même, le nombre de blocs est différent mais quand on crée une table, Oracle crée non pas les blocs à la demande un par un mais par paquets de X blocs, formant un extent. Il y a dons dans DBA_SEGMENTS des blocs vides qu'a priori on ne retrouve pas dans DBA_TAB_STATISTICS.
     SQL> SELECT BYTES/(1024*1024) AS "Taille Mo", BLOCKS FROM DBA_SEGMENTS where owner = 'HR' and SEGMENT_NAME = 'ZZTEST';
     Taille Mo     BLOCKS
     ---------- ----------
          200   25600

Dans DBMS_SPACE.CREATE_TABLE_COST, le deuxième paramètre est avg_row_size; on va le renseigenr avec la colonne AVG_ROW_LEN de DBA_TAB_STATISTICS puisque nous avons des données sur un seul octet.
Résultat? Il y a un écart de plus de 15% mais bon, on a le résultat immédiatement et c'est juste une estimation :-)
     SQL> set serveroutput on
     SQL> DECLARE
        ub NUMBER;
        ab NUMBER;
        ubM NUMBER;
        abM NUMBER;

          BEGIN
            DBMS_SPACE.CREATE_TABLE_COST('USERS',13,10000000,10,ub,ab);

            ubM := ub/(1024*1024);
            abM := ab/(1024*1024);

            DBMS_OUTPUT.PUT_LINE('Used Bytes Mo: ' || TO_CHAR(ubM));
            DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(abM));
          END;
          /

     Used Bytes Mo: 172.09
     Alloc Bytes: 176


============================================================================================
DBMS_SPACE pour un CREATE INDEX : estimation de l'espace disque
============================================================================================
On va maintenant estimer la taille disque pour la création d'un index.
     SQL> variable used_bytes number
     SQL> variable alloc_bytes number
     SQL> exec dbms_space.create_index_cost( 'create index zztest_id on zztest(id)', :used_bytes, :alloc_bytes );
     SQL> print :used_bytes
     USED_BYTES
     ----------
     60000000

     SQL> select round(60000000/(1024*1024),2) AS "Taille Mo" from dual;
     Taille Mo
     ----------
          57.22

     SQL> print :alloc_bytes
     ALLOC_BYTES
     -----------
       243269632

Tiens, quand j'ai utilisé EXPLAIN PLAN sur un CREATE INDEX pour une table de 1 000 000 de lignes, la taille estimée dans la note était de 24Mo et de 2415Mo pour une table de 100 000 000 de lignes. Ici on a le même rapport : 232Mo pour 10 000 000 millions soit en moyenne 23Mo ou 24Mo par millions de lignes. Est-ce que EXPLAIN PLAN utilise sous le capot DBMS_SPACE?
     SQL> select round(243269632/(1024*1024),2) AS "Taille Mo" from dual;
     Taille Mo
     ----------
            232

Si on crée réellement l'index, quelles sont les données? OK, il y a un écart de 10 à 15% mais c'est juste une estimation et elle est immédiate!
     SQL> CREATE INDEX zztest_idex_id ON zztest(id);

     SQL> exec dbms_stats.gather_index_stats(ownname => 'HR', indname => 'zztest_idex_id');

     SQL> SELECT BYTES/(1024*1024) AS "Taille Mo", BLOCKS FROM DBA_SEGMENTS where owner = 'HR' and SEGMENT_NAME = 'ZZTEST_IDEX_ID';
     Taille Mo     BLOCKS
     ---------- ----------
         192       24576

============================================================================================
Conclusions
============================================================================================
De mes tests, j'estime que la fonction EXPLAIN_PLAN n'est pas fiable pour estimer le temps de création d'une table ou d'un index. En revanche, le package DBMS_SPACE donne une estimation réaliste de l'espace disque pour un CREATE TABLE ou un CREATE INDEX.