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
