Estadísticas y Tablas Externas.

Todos sabemos que la base para que el optimizador pueda hacer su trabajo de manera eficiente es que las estadísticas de las tablas sean lo más precisas posible. Las tablas externas no son una excepción. Oracle permite calcular estadísticas para tablas externas de manera que el optimizador pueda tomar un camino u otro a la hora de decidir qué plan de ejecución es el más idóneo.

Pero el cálculo de estadísticas opara tablas externas tiene una particularidad:

carlos@db01.xxxxxx> SELECT TABLE_NAME,
  2         TABLESPACE_NAME,
  3         STATUS,
  4         NUM_ROWS,
  5         AVG_SPACE,
  6         LAST_ANALYZED
  7    FROM USER_TABLES;

TABLE_NAME    TABLESPACE_NAME STATUS NUM_ROWS AVG_SPACE LAST_ANALYZED
------------- --------------- ------ -------- --------- -------------------
PRUEBA01      USERS           VALID         0         0 26/11/2007 17:44:16
DUMMY         USERS           VALID         1         0 26/11/2007 17:44:08
EXT_TABLA_001                 VALID     86810         0 27/11/2007 09:40:50
EXT_TABLA_002                 VALID    455888         0 27/11/2007 09:41:32
EXCEPTIONS    USERS           VALID         0         0 26/11/2007 17:44:15

Para verlo, primero borraremos las estadísticas existentes:

carlos@db01.xxxxxx> EXEC  DBMS_STATS.DELETE_SCHEMA_STATS (ownname=>'CARLOS');

Procedimiento PL/SQL terminado correctamente.

carlos@db01.xxxxxx> SELECT TABLE_NAME,
  2         TABLESPACE_NAME,
  3         STATUS,
  4         NUM_ROWS,
  5         AVG_SPACE,
  6         LAST_ANALYZED
  7    FROM USER_TABLES;

TABLE_NAME    TABLESPACE_NAME STATUS NUM_ROWS AVG_SPACE LAST_ANALYZED
------------- --------------- ------ -------- --------- -------------------
PRUEBA01      USERS           VALID
DUMMY         USERS           VALID
EXT_TABLA_001                 VALID
EXT_TABLA_002                 VALID
EXCEPTIONS    USERS           VALID

Ahora calculamos las estadísticas para el esquema:

carlos@db01.xxxxxx> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (ownname=>'CARLOS');

Procedimiento PL/SQL terminado correctamente.

En teoría (Oracle no dice lo contrario) se han calculado las estadísticas de TODAS las tablas. ¿Sí?

carlos@db01.xxxxxx> SELECT TABLE_NAME,
  2         TABLESPACE_NAME,
  3         STATUS,
  4         NUM_ROWS,
  5         AVG_SPACE,
  6         LAST_ANALYZED
  7    FROM USER_TABLES;

TABLE_NAME    TABLESPACE_NAME STATUS NUM_ROWS AVG_SPACE LAST_ANALYZED
------------- --------------- ------ -------- --------- -------------------
PRUEBA01      USERS           VALID         0         0 28/11/2007 11:04:07
DUMMY         USERS           VALID         1         0 28/11/2007 11:04:06
EXT_TABLA_001                 VALID
EXT_TABLA_002                 VALID
EXCEPTIONS    USERS           VALID         0         0 28/11/2007 11:04:07

No. Las tablas externas EXT_TABLA_001 y EXT_TABLA_002 no tienen estadísticas.

Vamos a intentar computando en vez de estimando:

carlos@db01.xxxxxx> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (ownname=>'CARLOS',
  2         estimate_percent=>100);

Procedimiento PL/SQL terminado correctamente.

carlos@db01.xxxxxx> SELECT TABLE_NAME,
  2         TABLESPACE_NAME,
  3         STATUS,
  4         NUM_ROWS,
  5         AVG_SPACE,
  6         LAST_ANALYZED
  7    FROM USER_TABLES;

TABLE_NAME    TABLESPACE_NAME STATUS NUM_ROWS AVG_SPACE LAST_ANALYZED
------------- --------------- ------ -------- --------- -------------------
PRUEBA01      USERS           VALID         0         0 28/11/2007 11:05:03
DUMMY         USERS           VALID         1         0 28/11/2007 11:05:03
EXT_TABLA_001                 VALID
EXT_TABLA_002                 VALID
EXCEPTIONS    USERS           VALID         0         0 28/11/2007 11:05:03

carlos@db01.xxxxxx> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (ownname=>'CARLOS',
  2         estimate_percent=>NULL);

Procedimiento PL/SQL terminado correctamente.

carlos@db01.xxxxxx> SELECT TABLE_NAME,
  2         TABLESPACE_NAME,
  3         STATUS,
  4         NUM_ROWS,
  5         AVG_SPACE,
  6         LAST_ANALYZED
  7    FROM USER_TABLES;

TABLE_NAME    TABLESPACE_NAME STATUS NUM_ROWS AVG_SPACE LAST_ANALYZED
------------- --------------- ------ -------- --------- -------------------
PRUEBA01      USERS           VALID         0         0 28/11/2007 11:05:37
DUMMY         USERS           VALID         1         0 28/11/2007 11:05:37
EXT_TABLA_001                 VALID
EXT_TABLA_002                 VALID
EXCEPTIONS    USERS           VALID         0         0 28/11/2007 11:05:37

carlos@db01.xxxxxx>

Tampoco.

Así que hay que calcular las estadísticas para las tablas externas con DBMS_STATS.GATHER_TABLE_STATS

carlos@db01.xxxxxx> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=>'CARLOS',
  2         tabname=>'EXT_TABLA_001');

BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname=>'CARLOS', tabname=>'EXT_TABLA_001'); END;

*
ERROR en línea 1:
ORA-20000: Unable to analyze TABLE "CARLOS"."EXT_TABLA_001", sampling on external table is not supported
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1

¡Ojo! ¡Las estadísticas de tablas externas deben ser computadas, no estimadas!

carlos@db01.xxxxxx> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=>'CARLOS',
  2         tabname=>'EXT_TABLA_001',
  3         estimate_percent=>NULL);

Procedimiento PL/SQL terminado correctamente.

carlos@db01.xxxxxx> SELECT TABLE_NAME,
  2         TABLESPACE_NAME,
  3         STATUS,
  4         NUM_ROWS,
  5         AVG_SPACE,
  6         LAST_ANALYZED
  7    FROM USER_TABLES
  8    /

TABLE_NAME    TABLESPACE_NAME STATUS NUM_ROWS AVG_SPACE LAST_ANALYZED
------------- --------------- ------ -------- --------- -------------------
PRUEBA01      USERS           VALID         0         0 28/11/2007 11:05:37
DUMMY         USERS           VALID         1         0 28/11/2007 11:05:37
EXT_TABLA_001                 VALID     86810         0 28/11/2007 11:10:41
EXT_TABLA_002                 VALID
EXCEPTIONS    USERS           VALID         0         0 28/11/2007 11:05:37

carlos@db01.xxxxxx> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=>'CARLOS',
  2         tabname=>'EXT_TABLA_002',
  3         estimate_percent=>NULL);

Procedimiento PL/SQL terminado correctamente.

carlos@db01.xxxxxx> SELECT TABLE_NAME,
  2         TABLESPACE_NAME,
  3         STATUS,
  4         NUM_ROWS,
  5         AVG_SPACE,
  6         LAST_ANALYZED
  7    FROM USER_TABLES
  8    /

TABLE_NAME    TABLESPACE_NAME STATUS NUM_ROWS AVG_SPACE LAST_ANALYZED
------------- --------------- ------ -------- --------- -------------------
PRUEBA01      USERS           VALID         0         0 28/11/2007 11:05:37
DUMMY         USERS           VALID         1         0 28/11/2007 11:05:37
EXT_TABLA_001                 VALID     86810         0 28/11/2007 11:10:41
EXT_TABLA_002                 VALID    455888         0 28/11/2007 11:11:39
EXCEPTIONS    USERS           VALID         0         0 28/11/2007 11:05:37

Ahora sí que sí.

Así pues, las estadísticas de las tablas externas no se calculan con DBMS_STATS.GATHER_SCHEMA_STATS, sino con DBMS_STATS.GATHER_TABLE_STATS y computando (estimate_percent=>NULL), no estimando.

Nota: El funcionamiento de DBMS_STATS.GATHER_SCHEMA_STATS para las bases de datos 9i es diferente, como se puede ver aquí.

Saludos.

Carlos.

Anuncios

5 respuestas a Estadísticas y Tablas Externas.

  1. […] pueda tomar un camino u otro a la hora de decidir qu plan de ejecucin es el ms idneo.Mas: https://carlosal.wordpress.com/2007/11/28/estadisticas-y-tablas-externas/ […]

  2. Dani dice:

    Pero entonces hay una cosa que no entiendo Carlos. Este lunes estuve en un cliente, y revisando su base de datos ví que el proceso de generación de estadísticas le estaba fallando. El tenia un ‘DBMS_STATS.GATHER_SCHEMA_STATS’ y el fallo resultó ser por que intentaba sacar estadisticas de una tabla externa que apuntaba a un fichero que no existia.

    Elimine esa tabla externa (despues de hablar con el cliente claro) y el proceso de estadísticas volvio a funcionar. El error era del tipo:

    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-04040: file data.csv in EXT_TABLES not found

    Datos:
    Era un oracle 9.2.0.6 con un sistema operativo Red Hat 3

  3. carlosal dice:

    Dani:

    No tengo ningún 9i a mano para probar.

    Es cierto que la documentación de 9i dice:

    The DBMS_STATS package can gather statistics on indexes, tables, columns, and
    partitions, as well as statistics on all schema objects in a schema or database. It does not gather cluster statistics—you can use DBMS_STATS to gather statistics on the individual tables instead of the whole cluster.

    sin referirse en absoluto a las tablas externas. Pero en la documentación Oracle 10g (que es la que uso actualmente y obre la que se dsarrolló el ejemplo del artículo) se dice:

    For external tables, statistics are not collected during GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, and automatic statistics gathering processing. However, you can collect statistics on an individual external table using GATHER_TABLE_STATS. Sampling on external tables is not supported so the ESTIMATE_PERCENT option should be explicitly set to NULL. Because data manipulation is not allowed against external tables, it is sufficient to analyze external tables when the corresponding file changes.

    Saludos.

    Carlos.

  4. Dani dice:

    Gracias por la info Carlos, lo tendré en cuenta cuanto vaya algun cliente con 10g.

    PD : Gran blog ¡ Sigué así

  5. […] y Tablas Externas (II) En un reciente artículo, “Estadísticas y Tablas Externas“, se apuntaba la circunstancia de que DBMS_STATS.GATHER_SCHEMA_STATS() no recogía las […]

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: