Bloqueando estadísticas de tablas

Tener estadísticas exactas de objetos de bases de datos es obligatorio para obtener la mejor trayectoria de ejecución, y usualmente puedes recabar estadísticas usando el paquete DBMS_STATS sin problemas. Pero algunas veces tienes tablas especiales que necesitan tratamiento especial, como tablas "temporales" (tablas normales que son llenadas y sus datos modificados muy rápido) que pueden cambiar mucho el plan de ejecución de una sentencia SQL y volverte loco tratando de averiguar la causa raíz de esos cambios del plan de ejecución.

Como demostración de este comportamiento crearemos dos tablas, una que cambiará mucho (test1) y otra que es más o menos estática (test2):

SQL> create table test1 (id number primary key, id_char varchar2(15));

Table created.

SQL> declare
mynum number := 1;
begin
while mynum < 10 loop
  insert into test1 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create table test2 (id number primary key, id_char varchar2(15));

Table created.

SQL> declare
mynum number := 1;
begin
while mynum < 100000 loop
  insert into test2 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Al principio la tabla test1 tiene 9 registros, y queremos unir las tablas test1 y test2 para obtener algunos registros, pero primero recopilaremos estadísticas de test1:

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
  2  /

PL/SQL procedure successfully completed.

SQL> select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

 ID         ID_CHAR
---------- ---------------
 4          4
 5          5
 6          6
 7          7

A continuación obtendremos el plan de ejecución actual para esta pequeña sentencia:

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  4    | 56    |  4   (0)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |  4    | 56    |  4   (0)   | 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  5    | 55    |  4   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004858 |  5    |       |  3   (0)   | 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | SYS_C004857 |  1    |  3    |  0   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("B"."ID">=4 AND "B"."ID"<=7)
   4 - access("A"."ID"="B"."ID")
       filter("A"."ID">=4 AND "A"."ID"<=7)

18 rows selected.

Este plan de ejecución se ve bien y realmente no importa mucho ya que obtuvimos sólo unos cuantos registros, pero espera! Nueve registros no es el tamaño típico de la tabla test1; ahora crearemos más registros:

SQL> delete from test1;

9 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10000 loop
  insert into test1 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Podrías pensar que no hay mucha diferencia teniendo diez o diez mil registros en test1, pero el optimizador de Oracle no piensa lo mismo y esta vez el plan de ejecución es diferente:

SQL> EXPLAIN PLAN FOR
  2  select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2418654178

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  1    | 14    |  3   (0)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |  1    | 14    |  3   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004857 |  1    |  3    |  1   (0)   | 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C004858 |  1    |       |  1   (0)   | 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  1    | 11    |  2   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("A"."ID">=4000 AND "A"."ID"<=7000)
   4 - access("A"."ID"="B"."ID")
       filter("B"."ID"<=7000 AND "B"."ID">=4000)

19 rows selected.

Podrías pensar que recuperar cuatro registros es diferente de recuperar cuatro mil y es correcto, pero el optimizador escogió el plan de ejecución basado en datos incorrectos; ve la cantidad de registros con los que Oracle piensa que va a trabajar.

Lo verás claramente actualizando las estadísticas de la tabla test1 y obteniendo la nueva trayectoria de ejecución:

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'SYS', TABNAME => 'TEST1'); END;
  2  /

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  3001 | 45015 | 17  (12)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |  3001 | 45015 | 17  (12)   | 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  3002 | 33022 | 15   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004858 |  3002 |       |  8   (0)   | 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | SYS_C004857 |  1    |  4    |  0   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("B"."ID">=4000 AND "B"."ID"<=7000)
   4 - access("A"."ID"="B"."ID")
       filter("A"."ID">=4000 AND "A"."ID"<=7000)

18 rows selected.

Puedes ver el problema? Teniendo que tratar con este tipo de tablas puedes: usar hints para forzar la ejecución óptima de la sentencia SQL, refrescar estadísticas cada vez que cambies una tabla como esta, o bloquear las estadísticas cuando tengas un tamaño típico de tabla o un plan de ejecución óptimo.

Consideraremos que en este punto la tabla test1 tiene un tamaño típico y sus estadísticas ayudan al optimizador a escoger un plan de ejecución óptimo, por lo tanto bloquearemos las estadísticas de esta forma:

SQL> exec DBMS_STATS.LOCK_TABLE_STATS ('MYUSER', 'TEST1');

PL/SQL procedure successfully completed.

Ahora volveremos a poner información en la tabla test1 con 9 registros:

SQL> delete from TEST1;

9999 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10 loop
  insert into test1 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
  2  /
BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

Ya que las estadísticas de la tabla test1 fueron bloqueadas no puedes recopilar nuevas estadísticas a menos que desbloquees las estadísticas de la tabla. De cualquier forma, el punto de hacer esto es ayudar al optimizador a escoger un buen plan de ejecución:

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  4    | 60    |  4   (0)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |  4    | 60    |  4   (0)   | 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  5    | 55    |  4   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004858 |  5    |       |  3   (0)   | 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | SYS_C004857 |  1    |  4    |  0   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("B"."ID">=4 AND "B"."ID"<=7)
   4 - access("A"."ID"="B"."ID")
       filter("A"."ID"<=7 AND "A"."ID">=4)

18 rows selected.

SQL> delete from TEST1;

9 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10000 loop
  insert into test1 values(mynum,to_char(mynum));
  mynum := mynum + 1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  3001 | 45015 | 17  (12)   | 00:00:01 |
|   1 |  NESTED LOOPS                |             |  3001 | 45015 | 17  (12)   | 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2       |  3002 | 33022 | 15   (0)   | 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C004858 |  3002 |       |  8   (0)   | 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | SYS_C004857 |  1    |  4    |  0   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("B"."ID">=4000 AND "B"."ID"<=7000)
   4 - access("A"."ID"="B"."ID")
       filter("A"."ID">=4000 AND "A"."ID"<=7000)

18 rows selected.

Como puedes ver, esta vez el plan de ejecución fue el mismo para diez o diez mil registros en la tabla test1 y el numero de registros manejados en los planes de ejecución fue más o menos preciso.

Por cierto, si quieres desbloquear las estadísticas de una tabla puedes hacerlo de esta forma:

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS ('MYUSER', 'TEST1');

PL/SQL procedure successfully completed.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
  2  /

PL/SQL procedure successfully completed.

Más información:

Locking or Unlocking Statistics
Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS