Cuenta de Cloud Iniciar sesión en Cloud Registrarse para el Modo Gratuito de Cloud

Cuenta de Oracle

Oracle Database 12c: "Colección automática de estadísticas para cargas masivas"

Por Joel Pérez, Mahir M. Quluzade & Sebastián D'Alessandro (OCE)
Publicado en Abril 2016

Reciban estimados tecnólogos Oracle un cordial saludo. A través del presente artículo, tendremos la oportunidad de tratar un tema de gran importancia como lo es el de la colección de estadísticas para cargas masivas o comúnmente llamado como “BulkLoads”.

La colección y el entendimiento de las estadísticas de objetos de base de datos es parte de la columna vertebral de conocimiento que todos los “DBAs” deben poseer para desempeñar sus labores del día a día, sin embargo no vamos a pasar por alto reseñar de forma breve la esencia de su funcionamiento.

Si visualizamos una base datos como un elemento de transacciones en su modo más general podríamos hablar que la misma en un elemento informático que posee y nos permite a grandes rasgos las siguientes capacidades:

Guardar Información
Consultar Información
Modificar Información

Si visualizamos a una base de datos como un simple documento de texto, esas serian sus principales funciones. Obviamente una base de datos de ultima generación posee mucho mas que eso. Pero mas allá de analizar las múltiples funcionalidades de las bases de datos modernas, centrémonos para este caso en lo importante que es consultar la información.

La velocidad de consulta de información siempre ha sido un factor de alta importancia para los sistemas informativos. Existe toda una rama de especialización de cómo analizar inteligentemente los datos para el provecho a nivel de negocios en una empresa, es decir, la capacidad de consultar de forma rápida es tomada con una importancia mayor que inclusive la capacidad de escribir información en la base de datos.

Por supuesto, hay modelos de negocios en los que escribir de forma veloz podría ser más importante que tener una alta eficiencia en lectura. Sin embargo, la mayoría de las bases de datos podrían presentan mayores casos de atención para optimizar las lecturas que las escrituras.

Al momento de leer información en una base de datos se hace a través del lenguaje universal para base de datos relacionados llamado SQL “StructuredQueryLanguage”.

Cuando una base de datos Oracle recibe una sentencia “SQL” para resolver una consulta, se llevan a cabo diversas acciones para lograr la entrega del resultado.

Dentro de los diversos pasos uno de los más importantes es el llevado a cabo por el optimizador basado en costos “CostBasedOptimizer” “CBO”. Para que el “CBO” pueda determinar de forma exacta el plan de ejecución de para un “SQL Query” debe disponer de la información de las estadísticas de las tablas e índices que participan en el “SQL Query”, esta información comúnmente es conocida como "Optimizerstatistics" “Estadisticas del optimizador”, la misma describe como esta compuesto y distribuido internamente el objeto.

Estas estadísticas son utilizadas por el optimizador para elegir el mejor plan de ejecución para cada instrucción SQL.

El tiempo necesario para colectar las estadísticas en algunos casos puede ser de gran medida. En el manejador se pueden utilizar diversos métodos para tratar de reducir el tiempo de esta tarea en la mayor proporción posible.

A continuación presentaremos diversas alternativas para colectar estadísticas:

Lista de Código 1: Colectando estadísticas automáticamente para un índice en “Oracle Database 11g”
Lista de Código 2: Colectando estadísticas automáticamente para sentencias “CreateTable As Select” “CTAS”
Lista de Código 3: “Insert” de tipo "Direct-Path" en tabla no vacía
Lista de Código 4: “Insert” de tipo "Direct-Path" en tabla vacía
Lista de Código 5: “Inserts” paralelos
Lista de Código 6: Estadísticas automáticas para tablas particionadas
Lista de Código 7: Estadísticas automáticas por partición ( Tablas Particionadas )
Lista de Código 8: Colección de estadística en base a parámetro de base de datos

Nota: la columna “LAST_ANALYZED” la cual puede ser encontrada en vistas tales como: “DBA_TABLES”, “DBA_INDEXES”, “DBA_TAB_COL_STATISTICS” indica la fecha en que fue calculada la estadística para dicho objeto por ultima vez.

Lista de Codigo 1: Colectando estadísticas automáticamente para un índice en “Oracle Database 11g”

Copy




SQL> conn / as sysdba
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit  Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0       Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> conn usr/usr
Connected.
SQL> create table t as
     2   select level as n, 'Num :'||level as v from dual
     3   connect by level<=1e6;
Table created.
SQL>
 select table_name, to_char(last_analyzed,'dd.mm.yyyy  hh24:mi:ss') as last_analyzed
     2  from dba_tables where owner='USR';

TABLE_NAME                     LAST_ANALYZED
------------------------------ --------------
T

SQL> select index_name, last_analyzed from dba_indexes where  owner='USR';

no rows selected

SQL> create index idx_t on t(n);

Indexcreated.

SQL> select index_name, to_char(last_analyzed,'dd.mm.yyyy  hh24:mi:ss') as last_analyzed
     2  from user_indexes;

INDEX_NAME                     LAST_ANALYZED
------------------------------ -------------------
IDX_T                          19.10.2015 11:12:16

SQL> insert into t select * from t;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> alter index idx_t rebuild;

Indexaltered.

SQL> select index_name, to_char(last_analyzed,'dd.mm.yyyy  hh24:mi:ss') as last_analyzed
     2  from user_indexes;

INDEX_NAME                     LAST_ANALYZED
------------------------------ -------------------
IDX_T                          19.10.2015 11:13:29

En el listado de código 1 podemos observar que la creación del índice implica el análisis y colección de estadísticas automáticas para el mismo. También cuando el índice es reconstruido se genera de forma automática la colección de estadísticas.

La colección de estadísticas ha sido considerablemente mejorada en “Oracle Database 12c Release 1”, entre sus características cuenta la colección automática de estadísticas posterior a operaciones masivas como las siguientes:

* “CTAS – create table as select ...”
* “Insert into ... select using with --+ APPEND hint (Direct Path Insert)”
* “Parallelinserts”

Veamos en acción estas nuevas caracteristicas:

Lista de Codigo 2: Colectando estadísticas automáticamente para sentencias “CreateTable As Select” “CTAS”

Copy




[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=dbprm
[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 19 15:47:31 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit  Production
With the Partitioning, OLAP, Advanced Analytics, Real Application  Testing
and Unified Auditing options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit  Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL>
SQL> create user usr identified by usr;

User created.

SQL> grant create session, resource, dba to usr;

Grant succeeded.

SQL> alter user usr quota unlimited on users;

User altered.

SQL> conn usr/usr
Connected.
SQL>
SQL> select table_name, to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss')  as last_analyzed
     2 from dba_tables
     3 where owner='USR';

no rows selected

SQL> create table t as
2   select level as n, 'Num :'||level as v from dual
3   connect by level<=1e6;
Table created.
SQL>
 select table_name, num_rows, to_char(last_analyzed,'dd.mm.yyyy  hh24:mi:ss')  as last_analyzed
2 from dba_tables
3 where owner = 'USR';

TABLE_NAME  NUM_ROWS     LAST_ANALYZED
----------- ------------ ---------------------
T           1000000      19.10.2015 18:04:50

SQL> select table_name, column_name, num_distinct, notes from  dba_tab_col_statistics
2  where table_name = 'T';

TABLE_NAME   COLUMN_NAME  NUM_DISTINCT    NOTES
------------ ------------ --------------- ----------------------------------
T            N            1000000         STATS_ON_LOAD
T            V            998912          STATS_ON_LOAD

La tabla “T” fue creada bajo el método “CTAS”. La operación creó la tabla y generó las estadísticas en la misma operación. En la columna "DBA_TAB_COL_STATISTICS" puede observarse el valor "STATS_ON_LOAD", como evidencia de la acción de esta nueva característica.

Lista de Codigo 3: “Insert” de tipo "Direct-Path" en tabla no vacía

Copy




SQL> insert /*+ APPEND */ into t select * from t;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> select table_name, num_rows, to_char(last_analyzed,'dd.mm.yyyy  hh24:mi:ss')  as last_analyzed
     2 from dba_tables where owner = 'USR';

TABLE_NAME NUM_ROWS   LAST_ANALYZED
---------- ---------- -----------------------
T          1000000    19.10.2015 18:04:50

Como puede observarse en la salida anterior, luego de insertar 1000000 filas en la tabla “T” las estadísticas NO fueron actualizadas automáticamente, mantienen la misma fecha con respecto al listado de código 2. Esto se debe a que la tabla “T”, donde se realizó el “insert”, ya contenía datos previamente a la operación.

Lista de Codigo 4: “Insert” de tipo "Direct-Path" en tabla vacía

Copy



SQL> create table t1 as select * from t where 1!=1;

Table created.

SQL> select table_name, num_rows,  to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss')  as last_analyzed
     2 from dba_tables where owner = 'USR';

TABLE_NAME NUM_ROWS   LAST_ANALYZED
---------- ---------- -----------------------
T          1000000    19.10.2015 18:04:50
T1         0          19.10.2015 18:06:16

SQL> insert /*+ append */ into t1 select * from t;

2000000 rows created.

SQL> commit;

Commit complete.

SQL> select table_name, num_rows,  to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed
     2 from dba_tables where owner = 'USR';

TABLE_NAME NUM_ROWS   LAST_ANALYZED
---------- ---------- -----------------------
T          1000000    19.10.2015 18:04:50
T1         2000000    19.10.2015 18:08:26

En el listado de código anterior, se procedió a crear una nueva tabla (T1) sin registros y luego realizar una operación de insert en modo “direct-path”

En este caso el manejador Oracle SI colectó estadísticas para la tabla durante la operación de “insert” en modo “direct_path”. Esto se debe a que la tabla T1estaba vacía antes de ejecutar el insert.

Similar comportamiento podemos observar en operaciones de “insert” paralelo, las estadísticas son calculadas de manera automática cuando estas se realizan sobre tablas vacías.

Lista de Código 5: “Inserts” paralelos

Copy




SQL> create table t2 as select * from t where 1!=1;

Table created.

SQL> select table_name, num_rows,  to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss')  as last_analyzed
     2 from dba_tables where owner = 'USR';

TABLE_NAME NUM_ROWS   LAST_ANALYZED
---------- ---------- -----------------------
T          1000000    19.10.2015 18:04:50
T1         2000000    19.10.2015 18:08:26
T2         0          19.10.2015 18:10:31

SQL> insert /*+ enable_parallel_dml parallel */  into t2 select /*+ parallel */ * from t;

2000000 rows created.

SQL> select table_name, num_rows,  to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss')  as last_analyzed
     2 from dba_tables where owner = 'USR';

TABLE_NAME NUM_ROWS      LAST_ANALYZED
---------- ------------- --------------------------
T          1000000       19.10.2015 18:04:50
T1         2000000       19.10.2015 18:08:26
T2         2000000       19.10.2015 18:35:01

Podemos ver en la salida del código la creación de otra tabla vacía (T2) y una operación de insert paralelo donde las estadísticas también fueron calculadas automáticamente.

Esta característica funciona de igual manera en tablas particionadas con una pequeña diferencia. Cuando se realizan operaciones de insert en tablas particionadas vacías utilizando el modo “direct path”, el manejador calcula automáticamente estadísticas globales pero no a nivel de partición.

Lista de Código 6: Estadísticas automáticas para tablas particionadas

Copy



SQL> CREATE TABLE d
2 (
3  n, day
4 )
5 PARTITION BY RANGE (day)
6 (
7  PARTITION day_q2010 VALUES LESS THAN  (TO_DATE('01/01/2011', 'DD/MM/YYYY')),
8  PARTITION day_q2011 VALUES LESS THAN  (TO_DATE('01/01/2012', 'DD/MM/YYYY')),
9  PARTITION day_q2012 VALUES LESS THAN  (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
10 PARTITION day_q2013 VALUES LESS THAN  (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
11 PARTITION day_q2014 VALUES LESS THAN  (TO_DATE('01/01/2015', 'DD/MM/YYYY'))
12 )
13 AS
14 select  to_number(to_char(to_date('01012010','ddmmyyyy') - 1 + level, 'YYYYMMDD')) AS  n,
15         to_date('01012010','ddmmyyyy') - 1 + level as day
16 from dual
17 connect by level <= to_date('31122014','ddmmyyyy')  - to_date('01012010','ddmmyyyy') + 1;
Table created.
SQL>
 select table_name, num_rows,  to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss')  as last_analyzed
2  from dba_tables where owner = 'USR';

TABLE_NAME NUM_ROWS   LAST_ANALYZED
---------- ---------- -----------------------
T          1000000    19.10.2015 18:04:50
T1         2000000    19.10.2015 18:08:26
T2         2000000    19.10.2015 18:35:01
D          1826       20.10.2015 15:58:07

SQL> select table_name, partition_name,
2          num_rows,to_char(last_analyzed,'dd.mm.yyyy  hh24:mi:ss') as last_analyzed
3  from dba_tab_partitions
4  where table_name ='D';

TABLE_NAME PARTITION_NAME      NUM_ROWS    LAST_ANALYZED
---------- ------------------- ----------  -----------------
D          DAY_Q2010
D          DAY_Q2011
D          DAY_Q2012
D          DAY_Q2013
D          DAY_Q2014

5 rows selected.

Como podemos observar en la salida, el manejador colecta estadísticas globales para la tabla particionada pero no lo hace a nivel partición.

A continuación agregamos una nueva partición a la tabla y probamos nuevamente el “insert” de filas en modo “direct-path”. En este caso Oracle si colecta estadísticas para esta partición.

Podemos ver un ejemplode esto en el siguiente código:

Lista de Código 7: Estadísticas automáticas por partición ( Tablas Particionadas )

Copy




SQL> alter table d add partition day_q2015 values  less than (TO_DATE('01/01/2016', 'DD/MM/YYYY'));

Table altered.

SQL> select table_name, partition_name,
     2      num_rows,to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed
     3 from dba_tab_partitions
     4 where table_name ='D';

TABLE_NAME PARTITION_NAME      NUM_ROWS    LAST_ANALYZED
---------- ------------------- ----------  -----------------
D          DAY_Q2010
D          DAY_Q2011
D          DAY_Q2012
D          DAY_Q2013
D          DAY_Q2014
D          DAY_Q2015

6 rows selected.

SQL> insert /*+ APPEND */ into D Partition  (day_q2015)
     2 select *
     3   from (select level + 2015000 as  n, to_date('01012015','ddmmyyyy') - 1 + level from dual
     4        connect by level <= to_date('31122015','ddmmyyyy') -  to_date('01012015','ddmmyyyy') + 1);
365 rows created.
SQL> 
commit;

Commit complete.

SQL> select table_name, partition_name,
     2           num_rows,to_char(last_analyzed,'dd.mm.yyyy hh24:mi:ss') as last_analyzed
     3 from dba_tab_partitions
     4 where table_name ='D';

TABLE_NAME PARTITION_NAME      NUM_ROWS    LAST_ANALYZED
---------- ------------------- ----------  -----------------
D          DAY_Q2010
D          DAY_Q2011
D          DAY_Q2012
D          DAY_Q2013
D          DAY_Q2014
D          DAY_Q2015           365         20.10.2015 16:08:10

6 rows selected.

Como controlar la toma de estadísticas durante la carga?

El cálculoautomático de estadísticas para tablas en Oracle Database 12c es controlado mediante un parámetro oculto llamado _optimizer_gather_stats_on_load (Lista de código 8). El valor por defecto para este parámetro es TRUE y esto significa que el cálculo automático de estadísticas está habilitado, en el caso de no querer que las estadísticas sean tomas en línea durante operaciones de carga masiva, puede ser modificado al valor: FALSE. No obstante, se recomienda con mucho énfasis no modificar parámetros ocultos sin la supervisión del soporte de Oracle.

Lista de Código 8: Colección de estadística en base a parámetro de base de datos

Copy




SQL> select
     2  a.ksppinmParameterName,
     3  b.ksppstvlSessionValue,
     4  c.ksppstvlInstanceValue,
     5  a.ksppdesc Description
     6  from x$ksppi a,
     7       x$ksppcv b,
     8       x$ksppsv c
     9  where a.indx = b.indx
     10 and  a.indx = c.indx
     11 and  a.ksppinm LIKE  '/_optimizer_gather_stats_on_load' escape'/';

PARAMETERNAME                      SESSIONVALUE INSTANCE VALUE  DESCRIPTION
---------------------------------  -----------  --------------  -----------------------
_optimizer_gather_stats_on_load    TRUE         TRUE            enable/disable online statistics gathering

Es posible utilizar el “hint”: GATHER_OPTIMIZER_STATISTICS para habilitar explícitamente esta funcionalidad y NO_GATHER_OPTIMIZER_STATISTICS para deshabilitarla. Debemos aclarar que esta funcionalidad no es aplicable a ningún objeto propiedad de SYS y tampoco a tablas anidadas, tablas organizadas por índices, tablas externas, con columnas virtuales y tablas temporales globales que sean definidas con la opción “on commit delete rows”.

Conclusión:

El cálculo de las estadísticas de objetos es una tarea que puede llegar a consumir mucho tiempo en bases de datos con un tamaño considerable, la funcionalidad de colección de estadísticas automáticas durante operaciones de carga masiva es muy útil para reducir esos tiempos.

Actualizar nuestras bases de datos a Oracle 12c nos brinda la posibilidad de utilizar esta opción y otras interesantes características nuevas.

Estimados lectores hemos llegado al final de esta entrega, esperando que el presente artículo sea de utilidad. Nos despedimos hasta la próxima entrega. Saludos!

Joel Pérez es un experto DBA (Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g) con más de 16 años de experiencia real en el mundo de tecnología Oracle, especializado en diseño e implementación de soluciones de: Cloud, Alta disponibilidad, Recuperación contra desastres, Upgrades, Replicación y toda área relacionada con bases de datos Oracle. Joel se desempeña como "Chief Technologist & MAA, HA Architect" para www.Enmotech.com Yunhe Enmo (Beijing) Technology Co. Ltd. Beijing, China. Perfil OCM Joel Pérez: http://education.oracle.com/education/otn/JoelPerez.htm

Mahir M. Quluzade es un Senior DBA ( Oracle ACE ), Oracle CertifiedExpert Real ApplicationClusters 11g and GridInfrastructureAdminstrator entre otras certificaciones con mas de 10 anos de experiencia en bases de datos Oracle con especial foco en "High Availability" & "DisasterRecoverySolutions (RAC, Data Guard, RMAN,…)". Mahir actualmente trabaja en el "Central Bank of theRepublic of Azerbaijan" siendo su país de origen "Azerbaijan". Mahir es frecuente orador en el "Azerbaijan Oracle UserGroup (AZEROUG)", escritor de artículos para la revista "OTech Magazine" y mas.

Sebastián D'Alessandro es un Senior DBA con más de 12 años de experiencia en tecnología Oracle, focalizado principalmente en seguridad de base de datos, soluciones de alta disponibilidad, disaster recovery y virtualización. Actualmente desarrolla su actividad como consultor e instructor de manera independiente.

Este artículo ha sido revisado por el equipo de productos Oracle y se encuentra en cumplimiento de las normas y prácticas para el uso de los productos Oracle.