Activa las estadísticas en Oracle

Las estadísticas son necesarias para que Oracle sepa ejecutar las consultas SQL de la mejor forma posible (utilizando un índice en lugar de otro, haciendo un full scan en lugar de usar índices, etc. etc.)

Además, si no lanzamos nunca las estadísticas el log de la export o dump de la base de datos aparecerá plagado de avisos por estadísticas no fiables.

Lo primero que hay que hacer es crear una tabla especial para contener los registros de estadísticas:

$sqlplus "/as sysdba"
SQL> exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'BACKUP_STATS');

Si no hemos creado la tabla y lanzamos las estadísticas saldrá este error:

ORA-20000: TABLE "SYSTEM"."BACKUP_STATS" does not exist or insufficient
privileges
ORA-06512: en "SYS.DBMS_STATS", línea 3687
ORA-06512: en "SYS.DBMS_STATS", línea 11124
ORA-06512: en "SYS.DBMS_STATS", línea 11303
ORA-06512: en "SYS.DBMS_STATS", línea 12238

El comando para eliminar la tabla sería:
SQL> exec dbms_stats.drop_stat_table(ownname => 'SYSTEM', stattab => 'BACKUP_STATS');

Ahora que ya tenemos la tabla podemos preparar un script que ejecutaremos todos los días a la misma hora programándolo con el crontab.

Por supuesto, tendrás que modificarlo para tu instalación:

umask 022
ORACLE_BASE=/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:.:$PATH
export PATH
NLS_LANG=spanish_spain.WE8ISO8859P1
export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
LIBPATH=$ORACLE_HOME/lib
export LIBPATH
ORACLE_SID=xxxxxxxxx
export ORACLE_SID
sqlplus /nolog @/oralog/batchs/stats/stats.sql

Lo más importante está en la última línea. Una llamada a un script SQL que realmente es la que lanza las estadísticas:

set echo on
connect system/xxxxxx as sysdba
create pfile from spfile;
alter database backup controlfile to trace;
execute dbms_stats.export_database_stats(stattab=>'BACKUP_STATS', statown=>'SYSTEM');
execute dbms_stats.gather_database_stats(estimate_percent=>80, cascade=>TRUE, options=>'GATHER AUTO');
exit

Explicación de lo que hace:

DBMS_STATS.EXPORT_DATABASE_STATS:

Guarda las estadísticas de todos los objetos de la base de datos en la tabla que hemos creado antes. Esto nos permite por ejemplo, importar después las estadísticas del entorno de producción en un entorno de pruebas. Si no, al tener datos diferentes las ejecuciones de los procedimientos almacenados y consultas SQL pueden ser diferentes.

DBMS_STATS.GATHER_DATABASE_STATS:

Recoge las estadísticas de todos los objetos de la base de datos.
Los parámetros son muy interesantes.

STIMATE_PERCENT
Es el porcentaje de filas que queremos que lea para estimar las estadísticas. En mi ejemplo el 80%.

CASCADE
Calcula estadísticas de índices también.

GATHER AUTO
Oracle automáticamente sabrá de qué objetos necesita estadísticas.

Si queréis más detalles de los parámetros disponibles podéis verlo aquí:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1041371

Saludos.