UN VISTAZO A LA NUEVA VERSIÓN DE BASE DE DATOS ORACLE: 12C

Que tal amigos!

La recien liberada versión Oracle 12c nos ofrece una nueva arquitectura: 'MULTITENANT DATABASE'.

Un ejemplo común en Internet de este tipo de arquitectura es la de un tren. Con la arquitectura tradicional de Oracle tendriamos una locomotora  con un vagon de carga enganchado o 'conectado'. Si vamos a agregar un vagón, tendriamos que conseguir otra locomotora para conectar el nuevo vagón. Al final tendriamos dos locomotoras, cada una arrastrando su propio vagón.

En la arquitectura 'MULTITENANT' tendriamos una sola locomotora arrastrando los dos vagones. La locomotora principal vendria siendo el 'CONTAINER DATABASE' y los vagones serían las 'PLUGGABLES DATABASE'. La memoria 'PGA', 'SGA'  y  los procesos de 'BACKGROUND' como 'PMON' y 'SMON' únicamente va residir en el 'CONTAINER DATABASE'.

Si generamos la base de datos con el asistente 'DBCA' de Oracle con la opción 'MULTITENAT' tendriamos la siguiente arquitectura:

1. Una base de datos 'CONTAINER DATABASE ' generada automáticamente por Oracle identificada con el nombre CDB$ROOT'
2. Una base de datos 'PLUGGABLE DATABASE' generada  por Oracle para uso interno del  manejador, conocida como base de datos 'sembrada'  identificada con el nombre 'PDB$SEED'.  Esta se encuentra en modo 'READ ONLY' y es utilizada como un 'template'  por parte del manejador para generar  nuevas bases de datos 'PLUGGABLES'.
3. Una (o más)  'PLUGGABLE DATABASE' donde van a residir los datos de los aplicativos. Por ejemplo si vamos a tener una base de datos para un aplicativo 'CRM' tendriamos la base 'PDBCRM' y otra 'PDBDWH' para un Datawarehouse.

Del diccionario de datos de Oracle compuesto por las vistas  'DBA_' ,'USER_' , y 'ALL_', habra que familiarizarse un nuevo grupo de vistas identificadas con el prefijo 'CDB_'.  En estas últimas podremos visualizar los objetos residentes tanto en el 'CONTAINER' como en las bases de datos  'PLUGGABLES'.

En versiones anteriores a Oracle 12c,  debiamos realizar una consulta a la tabla 'DBA_TABLES'  de cada instancia de Oracle para conocer el total de tablas. En 12c, con una consulta a la tabla 'CDB_TABLES' podremos conocer las tablas de cada base de datos identificadas con una nueva columna :  'CON_ID' que nos va indicar en que base nos encontramos. Por default, Oracle asigna los siguientes valores a la columna 'CON_ID'

• Valor de 0 según la documentación de Oracle incluye tanto el 'container' como las base de datos no 'container.
• Valor de 1  para el container 'CDB$ROOT'
• Valor de 2 para la base de datos 'sembrada' o 'PDB$SEED'
• Valor de 3 o más para las bases de datos donde van a residir la información de aplicativos

Si requerimos el nombre de todas las tablas de los diferentes aplicativos, lo realizariamos con un 'SELECT table_name FROM cdb_tables where CON_ID>2',  sin necesidad de utilizar un viejo conocido : el  'export ORACLE_SID=id_instancia'  requerido para cambiarse de instancia.

Cuando ingresamos a una configuración tipo 'MULTITENANT', por default nos va a posicionar en la base de datos contenedor o 'CONTAINER DATABASE', lo cual podemos verificar con el siguiente comando desde sqlplus :

SQL> show con_name

          
           CON_NAME 
    _________________
    
           CDB$ROOT

Si deseamos conectarnos a la base de datos de tipo 'pluggable' llamada  'pdborcl' , es necesario ejecutar la siguiente sentencia:

 SQL> ALTER SESSION SET CONTAINER=PDBORCL;

             Session altered

  

La base de datos contenedor o 'CDB$ROOT' contiene su propio 'system' y 'sysaux'.  Un aspecto curioso, es que los tablespaces 'undo' y 'temp' que contienen los datos para todas las 'pluggables databases',  van a residir en la base de datos contenedor. Aunque es la configuración  por default, puede ser modificada.

De la misma forma, solo van a existir los 'redo logs' únicamente en el contenedor, por lo que podriamos tener transacciones de diferentes bases de datos concentrados en estos. Sería interesante validar en un futuro, si esto no va a provocar problemas de contención en disco. El archivo de control o 'control file' únicamente va a existir en la base de datos contenedor.

Las 'pluggable databases' van a tener sus propios tablespaces 'system' , 'sysaux' y de datos. Se puede conocer su estatus por medio de la siguiente consulta a la vista dinámica 'v$pdbs':

 SQL> SELECT name, open_mode FROM v$pdbs;

  NAME                             OPEN_MODE
 --------------------------        ----------------
 PDB$SEED                       READ ONLY
 PDBORCL                        READ WRITE

Por medio de la siguiente consulta podemos conocer que parámetros son modificables a nivel 'pluggable database'.

SQL> SELECT substr(NAME,1,30), substr(value,1,30) FROM V$SYSTEM_PARAMETER
   where ISPDB_MODIFIABLE ='TRUE'
      ORDER BY NAME;

Adicionalmente,  sera obligado familiarizarnos con el 'package' : 'dbms_resource_manager' para modificar parámetros de las 'pluggables databases'.

En versiones anteriores a 12c,  cuando requeriamos clonar una base de datos a un ambiente diferente, utilizabamos el comando 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS .....'.  que contenia principalmente la lista de 'redo logs' y 'datafiles' de la base a clonar, no importando que esta se encontrara en estado 'OPEN'.

En 12c,  ya no podremos utilizar este comando para clonar, ya que las 'pluggable databases' no tienen 'control file' !! ... únicamente se tiene el 'control file' para la base de datos 'CONTAINER'.  Para exportar la definición de la 'pluggable database'  es necesario que esta se encuentre en modo read only:

  SQL> alter database open read only;

La definición de la 'pluggable database'  se exporta en formato 'XML' con el siguiente comando:

SQL> BEGIN
         DBMS_PDB.DESCRIBE(
            pdb_descr_file => 'c:\orcl\pdborcl.xml');
        END;
    /

Otra forma de exportar la metada a un archivo 'XML ' es por medio del siguiente comando:

SQL> ALTER PLUGGABLE DATABASE pdborcl UNPLUG INTO 'C:\orcl\pdborcl.xml';

Pluggable database altered.

Sin embargo hay que ser cuidadosos con él comando 'UNPLUG', ya que una vez ejecutado,   no es posible realizar ningún operación sobre la 'pluggable database'.  Si queremos dar de baja  la base 'pdborcl' recien 'desconectada' nos va a enviar el siguiente mensaje:

SQL> shutdown immediate
ORA-65086: no se puede abrir/cerrar la base de datos de conexi¾n

De la misma forma no podemos abrir la base de datos:

SQL> startup
ORA-65086: no se puede abrir/cerrar la base de datos de conexi¾n

Si ejecutamos el comando 'UNPLUG' y resulta que realmente no deseabamos 'desconectar' la base de datos y queremos realizar un 'PLUG IN', de la base de datos, veremos que al parecer no existe o no es necesario un comando para realizarlo la 're-conexión.'  

Para volver a regenerar la base de datos 'pdborcl' en el mismo 'container' donde realizamos el 'unplug', tenemos que borrar la base de datos 'pdborcl', teniendo cuidado de no borrar los 'datafiles', con el siguiente comando:

SQL> DROP PLUGGABLE DATABASE pdborcl  KEEP DATAFILES;

Pluggable database dropped.

Con el siguiente comando recreamos las base de datos 'pdborcl' a partir de la metadata contenida en el archivo 'pdborcl.xml', con lo que ya tendriamos disponible nuevamente la base de datos 'pdborcl'

SQL>CREATE PLUGGABLE DATABASE pdborcl USING  'C:\orcl\pdborcl.xml'  COPY TEMPFILE REUSE;

Pluggable database created.

En 12c clonar una base de datos 'pluggable' dentro de la misma instancia es mucho más rápido y sencillo que en versiones anteriores.  Vamos a clonar la base de datos 'pdborcl' a una nueva llamada 'pdbventas' utilizando la opción 'FILE_NAME_CONVERT'.  Esta claúsula funciona por pares, es decir los 'datafiles' contenidos en el directorio de la base de datos origen  'c:\pdborcl1\' van a ser copiados al directorio de la base destino o clonado  'c:\pdbventas1\'.   Con el siguiente comando creamos la nueva base de datos 'pdbventas' en apenas unos segundos.

SQL> CREATE PLUGGABLE DATABASE PDBVENTAS FROM PDBORCL

           FILE_NAME_CONVERT =('c:\pdborcl1\', 'c:\pdbventas1\');

Pluggable database created.
 
Si por ejemplo intentamos clonar sin especificar su par para el directorio 'c:\pdborcl2\', recibimos el siguiente mensaje 

SQL> CREATE PLUGGABLE DATABASE PDBROCL FROM PDBVENTAS FILE_NAME_CONVERT
           =('c:\pdborcl1\', 'c:\pdbventas1\', 'c:\pdborcl2\' );

         CREATE PLUGGABLE DATABASE PDVENTAS FROM PDBORCL FILE_NAME_CONVERT =('c:\pdborcl1\', 'c:\pdbventas1\', 'c:\pdborcl2\' );
                                                                                   *
ERROR at line 1:
ORA-02000: falta la palabra clave ,

Con esto finalizamos un pequeño vistazo a esta nueva versión. Habra que familiarizarse con nuevos comandos para clonar, crear, respaldar y recuperar base de datos en 12c.

Conclusiones

La nueva funcionalidad 'MULTITENAT' es una de las más atractivas de la nueva versión Oracle 12c. Permite optimizar el uso de recursos, al eliminar la necesidad de tener varias instancias en un solo equipo. La generación de nuevas bases de datos es un proceso mucho más rápido y sencillo que en versiones anteriores. 

Se podrá tener un control centralizado de las diferentes bases de datos a partir de un contenedor principal. La administración de usuarios tambien observa cambios,  ya que tendremos usuarios comunes o 'common users' , consolidación de esquemas  o 'schema consolidation'.  Se tendrán también reubicación de 'datafiles' en línea,  una nueva infraestructura para 'RAC' llamada 'Flex Cluster' , alta redundancia para la instancia de 'ASM'  llamda 'Flex ASM',  tener más de un índice sobre una misma columna, entre otras nuevas funcionalidades.

Saludos !

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México

CUANDO LOS LOBS AFECTAN EL DESEMPEÑO DE LA BASE DE DATOS

Hola amigos !

El siguiente caso  (que no pretende ser una guía para la reubicación de LOBS)  fue originado por el llamado de un cliente reportando  problemas de lentitud de una base de datos Oracle 11.1.0.7 en configuración 'RAC' de cuatro nodos, con un aplicativo 'Oracle Business Process Management' operando en ambiente productivo. La primera actividad fue la obtención de un reporte de rendimiento por medio de la utilería 'AWR'  ejecutando el script 'awrrpti.sql' por cada nodo del cluster. 

Una vez generado el reporte, en la sección 'Top 5 Timed Foreground Events' del 'AWR' nos aparecio el evento 'enq HW -contention'  en todos los nodos del cluster, el cual no es muy común en este tipo de reportes: (solo muestro el top de un nodo)

Top 5 Timed Foreground Events

Event                                     Waits          Time(s)           Avg wait (ms)       %DB time    Wait Class

enq HW -contention           586,913         14,728                 25                        45.61       Configuration
db file sequential read      1,272,487          5,816                   5                        18.01        User I/O
DB CPU                                                         4,442                                            13.69
enq TX - row lock cont         3,973            1,310                330                         4.06        Application
direct path read                  948,724               943                    1                         2.92         User I/O

El evento 'enq HW -contention' nos hace referencia  a la 'marca de agua' o 'high water mark', la cual indica al manejador en que punto existen bloques libres y usados en un segmento de datos.  Que este evento aparezca en el 'Top 5'  indica que existe  un alto número de actualizaciones en algún segmento.  La nota Doc ID 9801919.8 de 'metalink' nos indica lo siguiente:

            "enq: HW - contention" may be seen for segments that have heavy  concurrency

              and often need to add an extent due to repeated SEG$ updates of dictionary data" 

Otras notas de 'metalink' nos sugieren que puede existir un problema con actualizaciones en campos tipo 'LOBs' (Large Objects) que contienen información no estructurada como archivos, imagenes o 'XMLs' entre otros.  Continuamos con  la búsqueda del ´culpable' de este evento en los 'tops' de las sentencias 'SQL'  en el reporte 'AWR'

En la sección 'sqls ordenados por tiempo de ejecución' nos aparece en primer lugar un 'UPDATE' sobre la tabla 'PPROCINSTANCE' el cual de forma notable ocupa el 75.91% del tiempo de la base de datos (por cuestiones de formato no incluí todas las columnas del 'AWR')

Elapsed Time(s)  CPU Time(s)   Executions   %Total DB Time     SQL text

  24,247                  1,259              84,911              75.91                        UPDATE PPROCINSTANCE SET .....
    1,832                     109              12,881                 5.74                        INSERT INTO PPROCINSTANCE ....

Continuamos en la sección 'sql ordenados por tiempo de CPU requerido'  y nuevamente aparece la sentencia 'UPDATE'  sobre la tabla 'PPROCINSTANCE':

CPU Time(s)   Elapsed Time(s)    %Total   %Total DB Time         SQL text

   1,259                 24,247                  34.20             75.91                       UPDATE PPROCINSTANCE SET ..
      174                      624                     4.73                1.95                      SELECT PPROCESSID, INSTID.... 

En la sección 'sql  ordenados por accesos a memoria' el 'UPDATE' de la tabla 'PPROCINSTANCE' aparece nuevamente

Buffer Gets      Gets per Exec   %Total     CPU Time (s)         SQL text

37,273,368          3,727.34             31.60        113.22                    DELETE FROM PCORRELATION ...
35,175,421             414.26             29.82      1259.44                    UPDATE PPROCINSTANCE SET ....

La sentencia tiene condiciones para los campos 'processid', 'instid' y 'threadid'. Sospechamos que puede existir un 'full scan'. Sin embargo, al revisar el 'explain plan'  se valida que el acceso es vía índice (aqui les debo el explain plan :-( )

     UPDATE pprocinstance SET (varios campos)..WHERE processid= ? AND instid= ? AND threadid=?

Para obtener el 'explain plan' sin necesidad de ejecutar nuevamente la sentencia, es muy útil el 'package' 'dbms_xplan.display_awr'.  Con el  'id query' obtenido en el reporte  'AWR'  se agrega como parámetro (en este caso el id 'g1jx24n27sykx' ) en el llamado del ´package'.

     SELECT plan_table_output FROM table (dbms_xplan.display_awr('g1jx24n27sykx'));

El siguiente paso es verificar si existe un 'LOB' en la tabla PPROCINSTANCE por medio de una consulta a la tabla  'DBA_LOBS'.   Identificamos al 'LOB'  'SYS_LOB0000108088C00003$$'  

     SELECT SUBSTR(table_name,1,40),  SUBSTR(segment_name, 1,30) ,  SUBSTR(tablespace_name,  1,30) 
           FROM dba_lobs 
       WHERE table_name = 'PPROCINSTANCE'
  
     SUBSTR(TABLE_NAME,1,40)                SUBSTR(SEGMENT_NAME,1,30)     SUBSTR(TABLESPACE_NAME,1,30)
       -------------------------------------------------       ------------------------------------------------    ---------------------------------------------------

          PPROCINSTANCE                                  SYS_LOB0000108088C00003$$                      USERS

Ahora que conocemos el segmento tipo 'LOB',  vamos a buscar en el reporte 'AWR' para validar si realmente tiene un impacto en el desempeño de la base de datos. En la sección de 'segmentos con más lecturas a disco' aparece el 'LOB'  de la tabla  PPROCINSTANCE con el 78% del total !

             Object Name                                               Obj. Type    Physical Writes    %Total DB Time   

           SYS_LOB0000108088C00003$$            LOB               993, 397                    73.40 
           SYS_LOB0000108088C00008$$            LOB                  31,622                      2.34 

Verificamos que índices tiene la tabla 'PPROCINSTANCE'  con la siguiente consulta. La tabla tiene tres índices que se encuentran en el tablespace 'USERS' que es el mismo que el utilizado por el 'LOB'.

            SELECT SUBSTR(index_name, 1,20), substr(tablespace_name,1,30) FROM dba_indexes
               WHERE table_name = 'PPROCINSTANCE';

             SUBSTR(INDEX_NAME,1,          SUBSTR(TABLESPACE_NAME,1,30)
            -------------------------------------          ------------------------------------------------
                   INSTPART                                                   USERS
                   ACT                                                               USERS
                   ASSIGPART                                                USERS

Ahora validamos el tablespace por default para la tabla PPROCINSTANCE  y no resulta una sorpresa que también sea USERS. Por alguna razón,  se generaron los datos, índices y lobs de la tabla maestra PPROCINSTANCE en el mismo tablespace, lo que genera problemas de contención.

          SELECT tablespace_name FROM dba_tables WHERE table_name = 'PPROCINSTANCE';

                  TABLESPACE_NAME
                  ------------------------------
                          USERS

La guía de Oracle 'Performance Tuning for Oracle Business Process Management Suite 10g' nos recomienda utilizar tablespaces independientes para los segmentos 'LOBS':

                     ' Use Separate Tablespaces for BLOBs If you are storing large

                        BLOBs in the database, you should consider a separate

                        tablespaces for the BLOBs'

Platicando con el cliente, le comentamos la necesidad de particionar la tabla y distribuirla en diferentes tablespaces. El regenerar la tabla PPROCINSTANCE impacta al resto del modelo de datos, implicando la recompilación de todos los objetos relacionados como stored procedures y packages entre otros. Por ser ambiente productivo, el cliente no desea que la tabla se vuelva a generar. Nos da como opción redistribuirla en diferentes tablespaces, pero sin regenerar ningún objeto. 

Para redistribuir la tabla PPROCINSTANCE se van a a generar tres tablespaces: para datos, índices y uno dedicado al segmento 'LOB'

                   CREATE TABLESPACE TBS_DATOS_PROC DATAFILE  '+BDBPMDATA' size 2048M;  

                   CREATE TABLESPACE TBS_IND_PROC DATAFILE   '+BDBPMINDEX' size 2048M;       

                   CREATE TABLESPACE TBS_LOBS_PROC DATAFILE  '+BDBPMLOG' size 10,000M   

Previo al proceso de reubicación es recomendable un 'full backup' de la base de datos y generar un listado que contenga el estatus de todos los objetos de la base de datos con la siguiente sentencia:

              SELECT object_name, object_status FROM dba_objects;

Una vez generados los tablespaces dedicados a la tabla PPROCINSTANCE es necesario reubicar los datos con la  siguiente secuencia :

1. Reubicar los datos de la tabla al tablespace 'TBS_DATOS_PROC'
2. Reubicar los índices de la tabla TBS_IND_PROC  al tablespace TBS_IND_PROC'
3. Reubicar el 'LOB'  ubicado en el campo INSTANCEDATA al tablespace TBS_LOBS_PROC
4. Reconstruir los índices inválidos

Ejecutamos el primer paso al reubicar los datos  de la tabla al tablespace 'TBS_DATOS_PROC'. Este paso va a invalidar todos los índices asociados a la tabla.

           ALTER TABLE OBPMENGORDERS.PPROCINSTANCE MOVE TABLESPACE TBS_DATOS_PROC;

 Continuamos con el segundo paso que es reubicar los índices al nuevo tablespace 'TBS_IND_PROC':

           ALTER INDEX OBPMENGORDERS.INSTPART REBUILD TABLESPACE TBS_IND_PROC;

              ALTER INDEX OBPMENGORDERS.ACT REBUILD TABLESPACE TBS_IND_PROC;

              ALTER INDEX OBPMENGORDERS.ASSIGPART REBUILD TABLESPACE TBS_IND_PRO;

Realizamos la etapa de reubicación del lob del campo INSTANCEDATA.  Por ser únicamente un campo el reubicado, no podemos utilizar la opción  'MOVE TABLESPACE'. Utilizaremos la opción 'MOVE LOB' indicando el campo a mover. Esta opción es la más lenta de todas, por lo que si se desea realizar una reubicación de 'LOBS', es recomendable realizar una prueba en ambiente pre-productivo para calcular ventanas de tiempo para el mantenimiento.

   ALTER TABLE OBPMENGORDERS.PPROCINSTANCE                                                                                                                                               MOVE LOB(INSTANCEDATA) STORE AS (TABLESPACE TBS_LOBS_PROC);

En caso de que el campo 'LOB'  a reubicar sea del tipo 'XML Type',  se puede utilizar el siguiente comando
donde se incluye la sentencia 'XMLDATA'

 ALTER TABLE xxxxxx move lob (CAMPO.XMLDATA) store as (TABLESPACE tablespace_destino);

Procedemos a  reconstruir los índices inválidados de la tabla PPROCINSTANCE

           ALTER INDEX OBPMENGORDERS.INSTPART REBUILD;

               ALTER INDEX OBPMENGORDERS.ACT REBUILD;

               ALTER INDEX OBPMENGORDERS.ASSIGPART REBUILD;

Por último volvemos a ejecutar el query para validar los objetos de la base de datos, asi como su estatus, que debe coincidir con el conteo previo a la reubicación.

              SELECT object_name, object_status FROM dba_objects;

Una vez que el conteo  es el correcto, finalizamos la reubicación.

Conclusiones

En el caso de tablas que contenga campos tipo 'LOB' y observan un elevado número de actualizaciones, es recomendable generar 'tablespaces' en discos independientes para reducir la contención y de ser posible generar la tabla particionada.  Se puede programar el uso de la sentencia 'alter table xxx shrink space compact' para reclamar el espacio eliminado.  Si se genero la tabla y ya cuenta con información, se puede realizar la reubicación por medio de la opción 'MOVE LOB', sin embargo hay que tener en cuenta los tiempos requeridos con esta opción.

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México

APLICANDO ARCHIVES EN UN FULL RECOVERY

El respaldo y recuperación de bases de datos Oracle es uno de los puntos más criticos dentro de la labor de un DBA.

Este ejercicio tiene como objetivo realizar un respaldo completo en frio  o 'cold full backup' .  Posterior a la realización del respaldo se van a generar dos tablas 'TEST1' y 'TEST2'  las cuales deberán ser respaldadas en automático por los 'ARCHIVE LOGS'.

Se va a realizar un 'full recovery', posteriormente se aplicaran  los  'ARCHIVE LOGS'. Oracle recomienda realizar los respaldos por medio de la herramienta 'RECOVER MANAGER' o 'RMAN'.   Este ejercicio se va a realizar por medio de copias de 'datafiles', 'redo logs' y 'control files' a nivel sistema operatívo.

El primer paso es  verificar que la instancia se encuentre en modo 'ARCHIVE LOG'

SQL> archive log list
Database log mode                      Archive Mode
Automatic archival                      Enabled
Archive destination                      USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence          6
Next log sequence to archive      8
Current log sequence                  8

Verificamos el nombre de la instancia a respaldar, en este caso 'test'

SQL> SELECT   instance_name FROM v$instance;

           INSTANCE_NAME
           ----------------
            test

Generamos un listado de todos los 'datafiles' a respaldar y que componen la instancia 'test'

SQL> SELECT NAME from v$datafile order by name;

NAME
--------------------------------------------------------------------------------
C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_9OX3J7DO_.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_9OX3LLWQ_.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_9OX3OB5M_.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_USERS_9OX3O8J6_.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_01.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_02.DBF

6 rows selected.

Se genera un listado con los 'REDO LOGS' de la instancia, donde se tienen 3 grupos de 'redo logs' con dos miembros por grupo

SQL> SELECT group#, status, substr(member,1,100) FROM  v$logfile order by 1;

    GROUP# STATUS  SUBSTR(MEMBER,1,100)
---------- ------- ----------------------------------------------------------------------------------------------------
         1         C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_1_9OX3ST80_.LOG
         1         C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_1_9OX3SVGR_.LOG
         2         C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_2_9OX3SXC6_.LOG
         2         C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_2_9OX3SYL4_.LOG
         3         C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_3_9OX3T03B_.LOG
         3         C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_3_9OX3T4SM_.LOG

6 rows selected.

Se genera un listado con los 'CONTROL FILES' de la instancia. Tenemos dos controlfile 'multiplexados' para fines de disponibilidad

SQL> SELECT substr(NAME, 1,80) FROM  v$controlfile;

SUBSTR(NAME,1,80)
--------------------------------------------------------------------------------
C:\APP12\BD\ORADATA\TEST\CONTROLFILE\O1_MF_9OX3SPDX_.CTL
C:\APP12\BD\FAST_RECOVERY_AREA\TEST\CONTROLFILE\O1_MF_9OX3SPQK_.CTL

Como se va realizar un respaldo en frío o 'COLD BACKUP' se da de baja la base de datos para que el respaldo sea consistente.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Finalizado el  shutdown respaldamos los 'datafiles'

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_9OX3J7DO_.DBF C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_9OX3LLWQ_.DBF C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_9OX3OB5M_.DB C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_USERS_9OX3O8J6_.DBF C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_01.DBF C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_02.DBF C:\BACKUP_TEST
        1 file(s) copied.

Se respaldan los seis 'redo logs':

C:\>COPY  C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_1_9OX3ST80_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_1_9OX3SVGR_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_2_9OX3SXC6_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_2_9OX3SYL4_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_3_9OX3T03B_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_3_9OX3T4SM_.LOG C:\BACKUP_TEST
        1 file(s) copied. 

Respaldamos los 'control files'

C:\>COPY C:\APP12\BD\ORADATA\TEST\CONTROLFILE\O1_MF_9OX3SPDX_.CTL C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\FAST_RECOVERY_AREA\TEST\CONTROLFILE\O1_MF_9OX3SPQK_.CTL C:\BACKUP_TEST
        1 file(s) copied.

Posterior al respaldo vamos a generar las tablas 'TEST1' y 'TEST2' con 8860 y 8880 registros respectivamente

SQL> show user
USER is "TEST"

SQL> CREATE TABLE test1 AS SELECT * FROM dba_extents;

Table created.

SQL> CREATE TABLE test2 AS SELECT * FROM dba_extents;

Table created.

SQL> SELECT count(*) FROM test1;

    COUNT(*)
   ----------
      8860

SQL> SELECT count(*) FROM test2;

  COUNT(*) 
   ----------
      8880

Unicamente se generaron dos 'archive logs'. El 'archive log' 'O1_MF_1_8_9P2HOYM9_.ARC' va a contener los datos de las tablas 'TEST1' y 'TEST2', que fueron creadas posterior al respaldo de los 'datafiles'

 Directory of C:\app12\BD\fast_recovery_area\TEST\ARCHIVELOG\2014_04_30

30/04/2014  01:21 p.m.    <DIR>          .
30/04/2014  01:21 p.m.    <DIR>          ..
30/04/2014  01:19 p.m.        13,605,888 O1_MF_1_8_9P2HOYM9_.ARC
30/04/2014  01:21 p.m.            12,288 O1_MF_1_9_9P2HRH8F_.ARC
               2 File(s)     13,618,176 bytes
 
Vamos a simular la pérdida de todos los 'datafiles' por lo que tenemos que realizar un 'FULL RECOVERY' realizando los siguientes pasos:

 1.- Se copian los 'datafiles' del directorio respaldo al directorio origen
 2.- Se copian los 'redo logs' del directorio respaldo al directorio origen
 3.- Se copian los 'control files' del directorio respaldo al directorio origen

Copia de 'datafiles'

C:\APP12\BD\ORADATA\TEST\DATAFILE\

C:\>cd back*

C:\BACKUP_TEST>copy *.dbf c:\app12\BD\ORADATA\TEST\DATAFILE\
O1_MF_SYSAUX_9OX3J7DO_.DBF
O1_MF_SYSTEM_9OX3LLWQ_.DBF
O1_MF_UNDOTBS1_9OX3OB5M_.DBF
O1_MF_USERS_9OX3O8J6_.DBF
TBS_01.DBF
TBS_02.DBF
        6 file(s) copied.

Copia de 'redo logs'

C:\BACKUP_TEST>copy *.LOG c:\app12\BD\ORADATA\TEST\DATAFILE\
O1_MF_1_9OX3ST80_.LOG
O1_MF_1_9OX3SVGR_.LOG
O1_MF_2_9OX3SXC6_.LOG
O1_MF_2_9OX3SYL4_.LOG
O1_MF_3_9OX3T03B_.LOG
O1_MF_3_9OX3T4SM_.LOG
        6 file(s) copied.

Copia de 'controlfiles'

C:\BACKUP_TEST>copy *.CTL c:\app12\BD\ORADATA\TEST\DATAFILE\
O1_MF_9OX3SPDX_.CTL
O1_MF_9OX3SPQK_.CTL
        2 file(s) copied.

Reiniciamos la instancia recordando que Oracle realiza las siguientes pasos durante el proceso de 'startup'

1.- NOMOUNT .- Ejecutada con el comando 'STARTUP NOMOUNT' .-  Oracle lee el archivo de inicio 'init.ora' y únicamente se inicia la memoria de la instancia.

2.- MOUNT.- Se lee el archivo de control o 'control file'. Todas las recuperaciones 'o recoverys' de una instancia Oracle se realizan en este modo.

3.- OPEN.- Todos los 'datafiles' se encuentran sincronizados y la base de datos es consistente y se encuentra operativa

Iniciamos la instancia en modo 'MOUNT'

SQL> startup mount
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2403352 bytes
Variable Size             322962408 bytes
Database Buffers           83886080 bytes
Redo Buffers                8294400 bytes
Database mounted.

Vamos a suponer el que el 'full backup fue realizado a las 10:00 AM y la creación de las tablas 'TEST1' y 'TEST2' a las 11:00 AM,  por lo que el 'full backup' no contiene las transacciones generadas entre las 10:00 AM y las 11:AM.

Una vez recuperados los 'datafiles', se realiza la siguiente consulta sobre la vista dinámica del sistema 'v$datafile_header', donde se muestra que los 'datafiles' se encuentra sincronizados con el número 2330727 que corresponde  a las 10:00 AM 

Este número es originado por medio del 'System Change Number'  o 'SCN'.  Los datafiles tienen en su cabecero o 'header' el 'SCN' que indica la última vez que fueron actualizados por medio del 'checkpoint'.

EL 'SCN' es indispensable para el recovery, ya que le permite conocer al manejador el punto en el tiempo la base de datos es consistente. En este caso, los 6 datafiles tienen el mismo número, por lo cual son consistentes. El 'SCN' también es almacenado en el archivo de control o 'control file'

SQL> SELECT checkpoint_change#,  substr(name, 1,70) FROM v$datafile_header ORDER BY 1;

CHECKPOINT_CHANGE# SUBSTR(NAME,1,70)
------------------ ----------------------------------------------------------------------
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_9OX3LLWQ_.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_01.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_USERS_9OX3O8J6_.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_02.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_9OX3OB5M_.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_9OX3J7DO_.DBF

6 rows selected.

Una vez finalizada la copia para recuperar todos los 'datafiles', 'redo logs' y 'control files' tendriamos una recuperación completa hasta las 10:00 AM que es la hora en que se realizo el respaldo 'full backup'. Sin embargo nos faltan las transacciones  (principalmente las tablas 'TEST1' y 'TEST2') que se crearon despues de las 10:00 AM. Estas transacciones faltantes, estan contenidas en los 'ARCHIVE LOGS', por lo cual hay que aplicarlos, para complementar el 'full recovery'

En modo 'MOUNT' damos el comando 'RECOVER DATABASE' , sin embargo vemos que no se realiza la recuperación o aplicación de los 'ARCHIVE LOGS' y el manejador dice 'no necesito ningún recovery, debido a que los datafiles son de las 10:00 AM y el archivo de control me indica que el respaldo corresponde  a las 10:00 AM'

SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Como la función 'RECOVER DATABASE' no aplico 'archive logs', hay que agregar la claúsula 'USING BACKUP CONTROLFILE', lo que le indica al manejador 'no hagas caso que el controlfile tiene un SCN de las 10:00 AM y aplica las transacciones posteriores a ese hora'. 

La sintáxis del comando 'USING BACKUP CONTROLFILE' se puede prestar a confusión, ya que en realidad no se esta utilizando ningún backup del  controlfile, lo único que se le esta indicando es que 'ignora la secuencia del controlfile '

  

Se ejecuta el comando y el manejador indica que 'archive logs' necesita aplicar para tener las transacciones faltantes entre las 10:00 y las 11:00 AM.  Se requiere el 'archive log' 'O1_MF_1_8_9P2HOYM9_.ARC'   para complementar el respaldo,  recordando que que las tablas 'TEST1' y 'TEST2'  se encuentran contenidas en este 'archive log'.  

Se aplica el 'archive log' y 'O1_MF_1_8_9P2HOYM9_.ARC' y nos indica que los demas 'archives' no son necesarios para la recuperación . Se puede autilizar la opción 'AUTO' para que se apliquen de forma automática los 'logs archive logs' requeridos, sin necesidad de especificarlos uno por uno.

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: el cambio 2330727 generado en 04/30/2014 12:29:32 es necesario para el thread 1
ORA-00289: sugerencia: C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_8_9P2HOYM9_.ARCORA-00280: el cambio 2330727 para el thread 1 estß en la secuencia n·mero 8

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: el cambio 2346630 generado en 04/30/2014 13:19:41 es necesario para el thread 1
ORA-00289: sugerencia: C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_9_9P2HRH8F_.ARC
ORA-00280: el cambio 2346630 para el thread 1 estß en la secuencia n·mero 9
ORA-00278: el archivo log 'C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_8_9P2HOYM9_.ARC' ya no es necesario para esta recuperaci¾n

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: el cambio 2346667 generado en 04/30/2014 13:21:02 es necesario para el thread 1
ORA-00289: sugerencia: C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_10_%U_.ARC
ORA-00280: el cambio 2346667 para el thread 1 estß en la secuencia n·mero 10
ORA-00278: el archivo log 'C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_9_9P2HRH8F_.ARC' ya no es necesario para esta recuperaci¾n

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Para iniciar la base de datos en modo 'OPEN' se da la opción 'RESETLOGS' para que los 'REDO LOGS'  se reinicien.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

En la  vista dinámica del sistema 'v$datafile_header' se observa que la secuencia basada en el 'SCN' se incremento de 2330727   a 2346671  con la aplicación de los 'archive logs' y el reinicio de la instacia.


SQL> SELECT checkpoint_change#,  substr(name, 1,70) FROM v$datafile_header ORDER BY 1;

CHECKPOINT_CHANGE# SUBSTR(NAME,1,70)
------------------ ----------------------------------------------------------------------
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_9OX3LLWQ_.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_01.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_USERS_9OX3O8J6_.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_02.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_9OX3OB5M_.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_9OX3J7DO_.DBF

6 rows selected.

Validamos la existencia de las tablas 'TEST1' y 'TEST2' , confirmando que ya fueron recuperadas a partir de los 'archive logs', con lo cual ya tenemos un recovery completo.

SQL> SELECT COUNT(*) FROM TEST.TEST1;

  COUNT(*)
----------
      8860

SQL> SELECT COUNT(*) FROM TEST.TEST2;

  COUNT(*)
----------
      8880

Conclusiones

Los 'archive logs' son un elemento clave en la recuperación de una base de datos Oracle.  Se debe tener especial cuidado en respaldarlos,  ya que en ocasiones, son el único mecanismo para lograr que la base de datos sea consistente en un momento en el tiempo.


José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México

POR QUE MI QUERY REALIZA UN FULL SCAN A PESAR DEL ÍNDICE

Algunos desarrolladores consideran que generando índices se resuelven la mayor parte de los problemas de desempeño en sus querys. Generan planes de ejecución buscando al supuesto culpable de todos sus males: la sentencia 'full scan'.  Incluso algunas herramientas  lo muestran en rojo, para que sea más fácil identificarlo.

Una vez que el desarrollador detecta el 'full scan'  procede a la generación del indice, normalmente de tipo b-tree, que es el default de Oracle. Una vez generado, observan con molestia que se sigue realizando el 'full scan' a la tabla.

El siguiente ejercicio tiene como objetivo generar un índice y observar su comportamiento. Previo a su generación, vamos a realizarnos  las siguientes preguntas básicas:

 1.- Del total de registros de la tabla, cuantos va a recuperar el query?

 2.- Cuantos valores distintos contiene el campo o campos sobre el cual se va a generar el índice?

 3.- Contiene valores nulos el campo a indexar?

 4.- El campo a indexar contiene los datos ordenados?

 5.- Verificar que los siguientes parametros a nivel instancia que afectan la ejecución de indices,  contengan   los valores por default:

• OPTIMIZER_INDEX_COST_ADJ=100.- El valor por default es 100. Si se disminuye este valor, Oracle considera que tiene 'menor costo' el utilizar un índice y favorece su ejecución. Lo recomendable es modificar este valor a nivel sesión, no a nivel instancia.
• OPTIMIZER_INDEX_CACHING=0.- El valor por default es 0. Si asignamos un valor,  el indice tendra la informacion en cache y se ejecutara más rápido. Es recomendable modificarlo a nivel sesión, no instancia.

Para verificar los valores apropiados para estos parámetros se puede revisar la guía de performance OPDG  (Oracle Performance Diagnostic Guide) del sitio de soporte oficial de Oracle.

Iniciamos el ejercicio creando la tabla TEST la cual va a contener un solo campo de tipo númerico

SQL> CREATE TABLE TEST ( campo1 number);

Table created.

Insertamos 100 mil registros únicos en la tabla TEST, ordenados de forma ascendente por medio del siguiente bloque pl/sql:

SQL>  DECLARE
                vcuenta NUMBER := 100000;
              BEGIN
                 FOR i IN 1..100000 LOOP
                   vcuenta  := vcuenta+1;
                   INSERT INTO TEST values (vcuenta);
                 END LOOP;
                   COMMIT;
          END;
   /

PL/SQL procedure successfully completed. 

Validamos la ejecución del bloque PL/SQL. Se tienen 100 mil registros únicos iniciando con el valor 100,000 y finalizando con el registro 200,000   

SQL> SELECT min(campo1), max(campo1), count(*) FROM test;

   MIN(CAMPO1) MAX(CAMPO1)   COUNT(*)
-----------            -----------                       ----------
     100001          200000                         100000

Generamos el índice idx_test1 en el campo campo1

    
SQL> CREATE INDEX idx_test1 ON test (campo1);

Index created.

Actualizamos estadísticas de la tabla TEST con el package 'DBMS_STATS' con los parámetros básicos,

incluyendo la opción 'cascade=> TRUE' para que se actualicen las estadísticas del índice:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TEST', cascade=> TRUE);

PL/SQL procedure successfully completed.

Actualizamos las estadísticas del índice recien generado IDX_TEST1 (ya habiamos dado la opción cascade, por si las dudas)

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>'TEST', indname=> 'IDX_TEST1');

PL/SQL procedure successfully completed.

Verificamos las estadísticas actualizadas de la tabla 'TEST' en la tabla del sistema 'dba_tables'

SQL> SELECT num_rows, last_analyzed, sample_size

           FROM dba_tables WHERE table_name = 'TEST';

  

   NUM_ROWS   LAST_ANA  SAMPLE_SIZE
----------                --------                  -----------
    100000           29/04/14           100000    

Verificamos las estadísticas del índice en la tabla del sistema dba_indexes.  Esta consulta nos puede indicar, de forma muy básica, que tan probable es que nuestro índice funcione correctamente, verificando los siguientes puntos:

1.- NUM_ROWS .- El índice contiene los rowids del total de registros de la tabla : 100,000. El rowid es la  dirección física donde se encuentra el registro guardado. El índice va a recuperar estos rowids.
               
2.- LAST_ANALIZED .- Contiene la fecha de la última actualización de estadísticas para el índice

3.- SAMPLE_SIZE .- Contiene el tamaño muestra utilizado por el 'package' DBMS_STATS para calcular las estadísticas.  Lo ideal es que este valor sea igual al campo 'NUM_ROWS'. En este caso tenemos un tamaño muestra apropiado de 100,000 registros, que el mismo que el el 'NUM_ROWS'

4.- DISTINCT_KEYS.- Tal vez sea uno de los campos más importantes para validar la viabilidad del índice. En este caso tenemos 100,000 valores distintos, que es lo más apropiado. Si por ejemplo se tuvieran 100,000 mil registros, de los cuales 50,000  contienen el valor 1 y los otros 50 mil restantes el valor de 2, tendriamos un valor de DISTINCT_KEYS igual a 2, por lo que seguramente el índice no funcionaria.

5.- CLUSTERING_FACTOR .- En este caso ingresamos 100,000 registros de forma ascendente 1,2,3,4,5 sucesivamente, por lo que los datos se encuentran ordenados fisicamente en la tabla, lo que facilita las búsquedas. El valor contenido en este campo, de 152  indica un buen grado de ordenamiento en la tabla. Entre menor sea el valor de este campo, mejor ordenamiento físico se tendrá.
                      

SQL> SELECT num_rows, last_analyzed, sample_size, distinct_keys, clustering_factor FROM dba_indexes where index_name ='IDX_TEST1';

  NUM_ROWS         LAST_ANA        SAMPLE_SIZE        DISTINCT_KEYS    CLUSTERING_FACTOR
----------                      --------                    -----------                      -------------                       -----------------
    100000                29/04/14               100000                       100000                            152   
   

Con el índice generado vamos a realizar una primera consulta que va a recuperar el 100% de los registros de la tabla.

El siguiente plan de ejecución muestra que se está utilizando él índice 'IDX_TEST1'. Se esta realizando un 'INDEX FAST FULL SCAN', el cual permite contar el total de registros, por medio del mismo índice, sin necesidad de contar los registros en la tabla. Este tipo de 'barrido' o 'scan' es muy útil cuando se recupera un volumen importante de información  ya que utiliza lecturas 'multibloque' y puede ser invocado por el hint 'index_ffs'.  En este momento se está realizando un 'FULL SCAN'

  1* SELECT count(*) FROM test WHERE campo1 between 100000 AND 200000

  COUNT(*)
----------
    100000

Execution Plan
----------------------------------------------------------
Plan hash value: 2671621383

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |    63   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST1 |   100K|   488K|    63   (2)| 00:00:01 |
-----------------------------------------------------------------------------------

Vamos a realizar otra consulta para recuperar ahora el 30% del total de registros y como nos muestra el plan de ejecución, se sigue realizando un 'FULL SCAN'
   
SQL> SELECT count(*) FROM test WHERE campo1 between 100000 AND 130000;

  COUNT(*)
  ----------
     30000

Execution Plan
----------------------------------------------------------
Plan hash value: 2671621383

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |    63   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST1 | 30000 |   146K|    63   (2)| 00:00:01 |

-----------------------------------------------------------------------------------           

Ahora modificamos el query para que recupere el 27% del total de registros. En este momento se observa en el plan de ejecución cambio de un 'INDEX FAST FULL SCAN' a un 'INDEX RANGE SCAN' por lo que en este punto es donde el índice estaría cumpliendo su verdadera funcionalidad, de retornar un  número limitado de registros de forma eficiente.   
   
No es una regla escrita, pero si se va a recuperar entre el 1 y el 25% del  total de registros de una tabla (en este ejercicio funciono hasta el 27% del total), el índice será utilizado, sin necesidad de realizar un 'FULL SCAN' y tendremos a nuestro desarrollador contento.

En este punto se deben considerar factores como cardinalidad, selectividad y valor asignado al parámetro 'DB_FILE_MULTIBLOCK_READ_COUNT'

Para valores mayores, el manejador prefiere realizar un 'FULL SCAN' con lecturas 'multibloque' ignorando lecturas de índice de tipo 'INDEX SCAN' y 'RANGE SCAN' principalmente.
   
SQL> SELECT count(*) FROM test WHERE campo1 between 100000 AND 127000;

  COUNT(*)
----------
     27000

Execution Plan
----------------------------------------------------------
Plan hash value: 3196887443
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     5 |    62   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TEST1 | 27000 |   131K|    62   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Conclusiones

Previo a la generación de un índice, es necesario considerar distintos factores como son el número de registros que va a retornar, cardinalidad y selectividad entre otros. Si un query va a retornar un aproximado del 30% o más de registros y generamos un índice, es probable que este no sea invocado para búsquedas puntuales de tipo 'INDEX SCAN' y 'RANGE SCAN' principalmente.  La tabla del sistema 'DBA_INDEXES' nos muestra información básica del índice. Si requerimos información adicional de un índice, la podremos obtener de la vista del sistema 'INDEX_STATS'

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com


5532439143 Ciudad de México