Estadísticas 101

Ahora, toca el turno a saber qué son las estadísticas y cómo se aplican de manera general a la base de datos Oracle. Esto surgió por una pregunta que me hizo un conocido acerca de las mismas.

Introducción

Comentando alguna ocasión con Karl, tocamos el tema acerca de que la base de datos Oracle está cimentada básicamente en teoría de conjuntos y estadística aplicada. Ésta última es la razón por la que surgió este sencillo y espero claro post. Ok, pongamos manos a la obra; para esto, haremos un pequeño recuerdo de cómo funciona.

¿Qué es la estadística?

Al revisar este término en la Real Academia de la Lengua Española, me encontré con lo que sigue como parte de la definición general de estadística:

Rama de la matemática que utiliza grandes conjuntos de datos numéricos para obtener inferencias basadas en el cálculo de probabilidades.

Así, Oracle usa los conjuntos de datos para poder inferir. ¿Qué infiere Oracle? o ¿Quién infiere qué en Oracle?

La respuesta la tiene el Cost Based Optimizer o CBO. Este se encarga de generar el mejor plan de ejecución para cada query que se ejecuta en la base de datos.

Uno de los elementos más importantes para la decisión del CBO en cuanto a los planes de ejecución, son las estadísticas para los objetos como las tablas y los índices. Así, la estadística aplicada adquiere una importancia sustancial.

Para poder realizar una estadística fidedigna, se requiere de una hipótesis y un error. La hipótesis se basa en una afirmación o especulación que se debe comprobar. El error es a partir de la forma en cómo se obtuvo la información esperada y la diferencia contra la que se obtuvo. De esta forma, la hipótesis se puede convertir en un hecho concreto o en una afirmación falsa.

Un ejemplo, ¿sencillo?

Para ilustar mejor lo comentado en el párrafo anterior, muestro el siguiente ejemplo. Para esto, vamos a crear nuestra hipótesis de la siguiente afirmación:

A todas las personas de la Ciudad de México les gusta la paleta de limón.

Ok, entonces ya está la hipótesis y es una mera especulación, porque no tenemos un dato real de si a todas las personas en la Ciudad de México les gusta la paleta de limón. ¿Cómo lo podemos corroborar? A través de un muestreo. Dicho muestreo se realiza a través de una encuesta.

Para esto, tomaré en cuenta la estimación aproximada de una población de 21 millones de personas para dicha Ciudad y la zona conurbada conformada por algunos municipios del Estado de México.

Para confirmar la hipótesis, vamos a pensar en un muestreo de 1,000 personas. Vamos a preguntarle a cada una de ellas si les gusta o no la paleta de limón. Supongamos que el 80% de ellas, nos responde afirmativamente. ¿Será suficiente este ejercicio inicial para poder confirmar la hipótesis? Me temo que no, de hecho, el error que se tendría aquí sería muy alto. En la siguiente imagen, vemos un aproximado de tamaño de la muestra contra el universo de personas:

y con el siguiente resultado:

Para tener un muestreo certero al 100, tendríamos que preguntar a las 21 millones de personas si les gusta la paleta de limón. Por supuesto, no podemos hacer semejante esfuerzo para tan básica afirmación. Por lo tanto, vamos a dimensionar nuevamente nuestro rango de muestreo. Por eso, voy pensar en una muestra de un 30%.

Con esto, iremos a preguntar a 7 millones de personas, si es gusta la paleta de limón. Esta cantidad, ya es representativa para confirmar o no la hipótesis. ¿No lo creen? Pero no será nada más la pregunta a dicha cantidad de personas, le agregarémos más sabor al preguntar a personas de distintas zonas de la ciudad, de Santa Fé, de Neza, de Tepito, de Azcapotzalco, de Coyoacán, de Naucalpan, etcétera. Es decir, de distintas zonas. Todavía más, también preguntaremos a personas de distintos tipos de trabajo, desde el barrendero que limpia la calle, pasando por albañiles, policías, servidores públicos, empleados, directores, gerentes, etcétera. En nuestra imagen de la muestra ya se ve algo mas representativo con respecto del universo:

Ahora si, con la mezcla que establecimos, tenemos una muestra importante para poder confirmar de mejor manera nuestra hipótesis. Vamos les preguntamos si les gusta la paleta de limón y nos dice el 92.5% que efectivamente les gusta ese sabor de paleta:

En esta ocasión, podemos pensar que nuestro muestreo es algo más contundente y fidedigno y podemos afirmar que a la tipica persona que vive en la Ciudad de México le gusta la paleta de limón.

Ahora pensemos en lo siguiente, cada día que pasa, salen por diversas razones y para no volver jamás, 10,000 personas de la Ciudad. Al mismo tiempo, cada día, 10,000 nuevas personas llegan a la Ciudad para vivir en ella.

Al final del año, 3,650,000 de personas han cambiado en la Ciudad de México; son totalmente nuevas las personas. ¿Nos sirve la encuesta que tomamos un año antes para saber si le gusta la paleta de limón al típico ciudadano de dicha población? Creo que no. ¿Cómo lo podemos resolver? Respuesta: Realizando una nueva encuesta.

Por otro lado, un caso diametralmente diferente al primero, cada día sale una persona de la Ciudad de México y cada día llega una nueva. Al final del año, 365 nuevas personas estarán en la Ciudad. ¿Seguirá por la cantidad de personas distintas siendo válida nuestra encuesta? Respuesta: Creo que sí. Por lo que no será necesario hacer una nueva para poder afirmar que al típico ciudadano de la mencionada población, le gusta la paleta de limón.

¿Cómo funciona esto en Oracle?

La similitud con Oracle reside en que nuestras tablas y/o índices serán nuestra Ciudad y las personas que entran y salen de la misma, son registros que se insertan, borran y/o cambian en dichas tablas y/o índices.

Como ya comentamos, el CBO de la base de datos, usa las estadísticas de los objetos para poder decidir por cual camino ir. Así, y de acuerdo a lo que ya vimos en nuestro ejemplo teórico, deberemos tener las estadísticas frescas para aquellas tablas y/o índices cuyos registros son muy volátiles; es decir, que cambian contínuamente.

Por el contrario, una tabla y/o índice cuyos registros prácticamente no cambian al lo largo del tiempo, no requieren que se estén refrescando las estadísticas.

¿Cómo refresco las estadísticas de un objeto?

En el campo LAST_ANALYZED de varias vistas como DBA_TABLES, DBA_INDEXES, etcétera, podemos ver la última fecha en que se le corrieron las estadísticas a algún objeto. Para realizar un refrescamiento de las mismas, Oracle nos provee de un paquete llamado DBMS_STATS. El cual, entre otros, contiene una serie de procedimientos y funciones que nos servirán para generar las estadísticas para:

Una tabla

GATHER_TABLE_STATS (ownname          => 'PROPIETARIO',
                    tabname          => 'NOM_TABLA',
                    estimate_percent => porcentaje_muestra);

Con este procedimiento del paquete DBMS_STATS, podemos actualizar las estadísticas para una tabla nada más. Por ejemplo:

SQL> select table_name, last_analyzed from user_tables;

TABLE_NAME                     LAST_ANALY
------------------------------ ----------
EMP                            14/12/2009
SALGRADE                       18/11/2009
PRUEBA2                        05/12/2009
NUMBER_NORMAL                  09/12/2009
NUMBER_PRECISION               09/12/2009
DEPT_CLUSTER                   15/12/2009
EMP_CLUSTER                    15/12/2009
T_OBJECTS                      15/12/2009
SYS_IOT_OVER_10539             15/12/2009
BIMONTHLY_REGIONAL_SALES       14/12/2009
SALES_COMPOSITE                14/12/2009
T_IOT                          15/12/2009
SALES_HASH                     09/12/2009
SALES_LIST                     09/12/2009
PRUEBA1                        01/12/2009
DEPT                           24/11/2009
BONUS                          19/12/2009
T_OBJECTS2                     15/12/2009

18 rows selected.

SQL> exec dbms_stats.gather_table_stats (ownname=>'PRUEBA', tabname=>'T_OBJECTS2', estimate_percent=>30);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed from user_tables;

TABLE_NAME                     LAST_ANALY
------------------------------ ----------
EMP                            14/12/2009
SALGRADE                       18/11/2009
PRUEBA2                        05/12/2009
NUMBER_NORMAL                  09/12/2009
NUMBER_PRECISION               09/12/2009
DEPT_CLUSTER                   15/12/2009
EMP_CLUSTER                    15/12/2009
T_OBJECTS                      15/12/2009
SYS_IOT_OVER_10539             15/12/2009
BIMONTHLY_REGIONAL_SALES       14/12/2009
SALES_COMPOSITE                14/12/2009
T_IOT                          15/12/2009
SALES_HASH                     09/12/2009
SALES_LIST                     09/12/2009
PRUEBA1                        01/12/2009
DEPT                           24/11/2009
BONUS                          19/12/2009
T_OBJECTS2                     24/12/2009

18 rows selected.

SQL>

Un índice

GATHER_INDEX_STATS (ownname          => 'PROPIETARIO',
                    indname          => 'NOM_INDICE',
                    estimate_percent => porcentaje_muestra);

Con este procedimiento del paquete DBMS_STATS, podemos actualizar las estadísticas para un índice, como se puede observar en el siguiente ejemplo:

SQL> select index_name, last_analyzed from user_indexes;

INDEX_NAME                     LAST_ANALY
------------------------------ ----------
PK_DEPT                        19/12/2009
EMP_03                         19/12/2009
EMP_02                         14/12/2009
EMP_01                         19/12/2009
PK_EMP                         14/12/2009
PRUEBA2_01                     05/12/2009
T_IOT_PK                       15/12/2009
IDX_CLUEMPDEPT                 15/12/2009

8 rows selected.

SQL> exec dbms_stats.gather_index_stats (ownname=>'PRUEBA', indname=>'PK_DEPT', estimate_percent=>30);

PL/SQL procedure successfully completed.

SQL> select index_name, last_analyzed from user_indexes;

INDEX_NAME                     LAST_ANALY
------------------------------ ----------
PK_DEPT                        24/12/2009
EMP_03                         19/12/2009
EMP_02                         14/12/2009
EMP_01                         19/12/2009
PK_EMP                         14/12/2009
PRUEBA2_01                     05/12/2009
T_IOT_PK                       15/12/2009
IDX_CLUEMPDEPT                 15/12/2009

8 rows selected.

Un esquema (usuario)

GATHER_SCHEMA_STATS (ownname          => 'PROPIETARIO',
                     estimate_percent => porcentaje_muestra);

Con este otro procedimiento del paquete DBMS_STATS, se refrescan las estadísticas para todos los objetos que pertenecen a un esquema o usuario. Con los privilegios necesarios, desde dentro del mismo usuario se pueden generar las estadísticas sin problemas como se puede observar en el siguiente ejemplo:

SQL> conn curso/curso
Connected.
SQL> select table_name, last_analyzed from user_tables;
TABLE_NAME                     LAST_ANA
------------------------------ --------
NUMBER_NORMAL                  09/12/09
SQL> exec dbms_stats.gather_schema_stats (ownname=>'CURSO', estimate_percent=>30);
PL/SQL procedure successfully completed.
SQL> select table_name, last_analyzed from user_tables;
TABLE_NAME                     LAST_ANA
------------------------------ --------
NUMBER_NORMAL                  24/12/09

Toda la base de datos

GATHER_DATABASE_STATS (estimate_percent => porcentaje_muestra);

Con este procedimiento del paquete DBMS_STATS, podremos actualizar las estadísticas de todos los objetos de la base de datos. Puede demorar una considerable cantidad de tiempo; por lo que si se requiere, se deberá tener una ventana de tiempo grande. Para ejecutar este procedimiento, se debe hacer con el usuario SYS o SYSTEM de preferencia. Por ejemplo:

SQL> conn prueba/prueba
Connected.
SQL> select *
  2  from  (select 'TABLE', table_name object, last_analyzed from user_tables
  3         union
  4         select 'INDEX', index_name object, last_analyzed from user_indexes)
  5  order by 1, 2;

'TABL OBJECT                         LAST_ANALY
----- ------------------------------ ----------
INDEX EMP_01                         19/12/2009
INDEX EMP_02                         14/12/2009
INDEX EMP_03                         19/12/2009
INDEX IDX_CLUEMPDEPT                 15/12/2009
INDEX PK_DEPT                        24/12/2009
INDEX PK_EMP                         14/12/2009
INDEX PRUEBA2_01                     05/12/2009
INDEX T_IOT_PK                       15/12/2009
TABLE BIMONTHLY_REGIONAL_SALES       14/12/2009
TABLE BONUS                          19/12/2009
TABLE DEPT                           24/11/2009
TABLE DEPT_CLUSTER                   15/12/2009
TABLE EMP                            14/12/2009
TABLE EMP_CLUSTER                    15/12/2009
TABLE NUMBER_NORMAL                  09/12/2009
TABLE NUMBER_PRECISION               09/12/2009
TABLE PRUEBA1                        01/12/2009
TABLE PRUEBA2                        05/12/2009
TABLE SALES_COMPOSITE                14/12/2009
TABLE SALES_HASH                     09/12/2009
TABLE SALES_LIST                     09/12/2009
TABLE SALGRADE                       18/11/2009
TABLE SYS_IOT_OVER_10539             15/12/2009
TABLE T_IOT                          15/12/2009
TABLE T_OBJECTS                      15/12/2009
TABLE T_OBJECTS2                     24/12/2009

26 rows selected.

Elapsed: 00:00:00.15
SQL> exec dbms_stats.gather_database_stats (estimate_percent=>30);
BEGIN dbms_stats.gather_database_stats (estimate_percent=>30); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Database
ORA-06512: at "SYS.DBMS_STATS", line 13197
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13700
ORA-06512: at "SYS.DBMS_STATS", line 13664
ORA-06512: at line 1

Elapsed: 00:00:00.15

 Pero al hacerlo con SYS y volver a ejecutar el query de los objetos:

SQL> show user
USER is "SYS"
SQL> set timing on
SQL> exec dbms_stats.gather_database_stats (estimate_percent=>30);

PL/SQL procedure successfully completed.

Elapsed: 00:02:32.42

SQL> show user
USER is "PRUEBA"
SQL> select *
  2  from  (select 'TABLE', table_name object, last_analyzed from user_tables
  3         union
  4         select 'INDEX', index_name object, last_analyzed from user_indexes)
  5  order by 1, 2;

'TABL OBJECT                         LAST_ANALY
----- ------------------------------ ----------
INDEX EMP_01                         24/12/2009
INDEX EMP_02                         24/12/2009
INDEX EMP_03                         24/12/2009
INDEX IDX_CLUEMPDEPT                 24/12/2009
INDEX PK_DEPT                        24/12/2009
INDEX PK_EMP                         24/12/2009
INDEX PRUEBA2_01                     24/12/2009
INDEX T_IOT_PK                       24/12/2009
TABLE BIMONTHLY_REGIONAL_SALES       24/12/2009
TABLE BONUS                          24/12/2009
TABLE DEPT                           24/12/2009
TABLE DEPT_CLUSTER                   24/12/2009
TABLE EMP                            24/12/2009
TABLE EMP_CLUSTER                    24/12/2009
TABLE NUMBER_NORMAL                  24/12/2009
TABLE NUMBER_PRECISION               24/12/2009
TABLE PRUEBA1                        24/12/2009
TABLE PRUEBA2                        24/12/2009
TABLE SALES_COMPOSITE                24/12/2009
TABLE SALES_HASH                     24/12/2009
TABLE SALES_LIST                     24/12/2009
TABLE SALGRADE                       24/12/2009
TABLE SYS_IOT_OVER_10539             24/12/2009
TABLE T_IOT                          24/12/2009
TABLE T_OBJECTS                      24/12/2009
TABLE T_OBJECTS2                     24/12/2009

26 rows selected.

Si la información de este post te ha sido de utilidad o quieres que agregue algo más, deja por favor un comentario, contestaré a la brevedad.

Anuncios

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: