El Blog de Leonardo Horikian

Eliminar una consulta de la Shared Pool

2010-07-06T13:41:00.007-03:00

A partir de Oracle 10.2.0.4 en adelante, el paquete DBMS_SHARED_POOL contiene nuevos procedimientos. Uno de esos procedimientos se llama PURGE y es el encargado de eliminar determinados objetos de la Shared Pool. Estos objetos pueden ser: cursores (consultas SQL), paquetes, procedimientos, funciones, triggers, secuencias y tipos.
En versiones anterior, esto no podíamos hacerlo y lo que debíamos hacer era eliminar todas las consultas de la Shared Pool mediante la sentencia "ALTER SYSTEM FLUSH SHARED_POOL".

La creación del paquete DBMS_SHARED_POOL podemos encontrarla en $ORACLE_HOME/rdbms/admin/dbmspool.sql

En Oracle 10.2.0.4 existe el bug 5614566 que provoca el mal funcionamiento del procedimiento PURGE. Pero, tenemos un manera de evitar este bug en esa versión.

Comencemos viendo un ejemplo en 11gR1:


SQL> desc DBMS_SHARED_POOL

PROCEDURE ABORTED_REQUEST_THRESHOLD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THRESHOLD_SIZE                 NUMBER                  IN
PROCEDURE KEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT
PROCEDURE PURGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT
 HEAPS                          NUMBER                  IN     DEFAULT
PROCEDURE SIZES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MINSIZE                        NUMBER                  IN
PROCEDURE UNKEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT

Para ejecutar el procedimiento PURGE, debemos pasar como valor en el parámetro NAME el ADDRESS junto con el HASH_VALUE separados por una coma(,).
Estos valores podemos encontrarlos en la vista V$SQLAREA.


SQL> SELECT /* prueba */ 1 FROM DUAL;

         1
----------
         1

SQL> SELECT address||','||hash_value name 
     FROM v$sqlarea 
     WHERE sql_text = 'SELECT /* prueba */ 1 FROM DUAL';

NAME
-------------------
2E10E868,4198164882

Como podemos ver, lo que hicimos fue ejecutar una consulta para que sea parseada por primera vez y almacenada en la Shared Pool. Luego buscamos en la vista V$SQLAREA los valores que debo pasar en el parámetro NAME del procedimiento PURGE.

Ahora vamos a eliminar la consulta de la Shared Pool y a verificar que se haya eliminado.


SQL> exec dbms_shared_pool.purge('2E10E868,4198164882','C',1);

PL/SQL procedure successfully completed.

SQL> SELECT address||','||hash_value name 
     FROM v$sqlarea 
     WHERE sql_text = 'SELECT /* prueba */ 1 FROM DUAL';

no rows selected

La consulta fue eliminada con éxito!

Para hacer funcionar el procedimiento en 10.2.0.4, debemos realizar el siguiente alter en la sesión antes de ejecutar el procedimiento PURGE:


SQL> alter session set events '5614566 trace name context forever';

Pueden utilizar el siguiente script para hacer el purge de un determinado cursor de la Shared Pool:


DECLARE
 name    varchar2(50);
 version varchar2(3);
BEGIN
 select regexp_replace(version,'\..*') 
 into version 
 from v$instance;

 if version = '10' then
   execute immediate
   q'[alter session set events '5614566 trace name context forever']'; -- bug 5614566
 end if;

 select address||','||hash_value 
 into name
 from v$sqlarea
 where sql_id like '&sql_id';

 dbms_shared_pool.purge(name,'C',1);
END;
/

Diferencias entre BETWEEN y >= <=

2010-07-06T02:04:00.013-03:00

Muchas personas prefieren utilizar el BETWEEN en vez de >= <= y otras personas prefieren usar lo contrario porque suponen que hay alguna diferencia a nivel de performance. Esto es un mito muy antiguo. La realidad es que no existen diferencias.

BETWEEN es una "sinónimo" de >= <=

Voy a demostrar esto con una sencilla prueba:


SQL> CREATE TABLE test AS
  2  SELECT level id, 'nombre_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 100;

Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> DESC test

 Name          Null?    Type
 ------------- -------- --------------
 ID                     NUMBER
 NOMBRE                 VARCHAR2(47)

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      7 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"<=25 AND "ID">=20))

Como podemos ver en la sección "Predicate Information", el optimizador reemplazó la sentencia BETWEEN por "ID"<=25 AND "ID">=20.
También podemos ver, que leyó 4 bloques de datos para recuperar los 6 registros mediante un acceso FULL SCAN.

Ahora, probemos colocando el >= <= en la consulta ...


SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      7 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"<=25 AND "ID">=20))

Acá volvemos a ver en la a sección "Predicate Information" que el optimizador no realizó ningún reemplazo.
Seguimos leyendo 4 bloques de datos para recuperar los 6 registros mediante un acceso FULL SCAN.

Una cosa muy importante a destacar entre estas 2 consultas es que el hash value de ambas es el mismo (1357081020). Esto nos indica que la utilización de BETWEEN o >= <= es transparente para el optimizador ya que siempre va a utilizar >= <= para resolver la consulta.

Qué sucede si creamos un índice en la tabla? Obtendremos alguna diferencia?


SQL> CREATE UNIQUE INDEX test_uq ON test(id, nombre);

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1', cascade => true);

PL/SQL procedure successfully completed.

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1087767317

--------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| TEST_UQ |      1 |      7 |      6 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID">=20 AND "ID"<=25)

Vemos que se volvió a reemplazar el BETWEEN por >= <= y ahora como estamos accediendo por índice, leemos solamente 2 bloques de datos.

Qué sucederá si modificamos nuevamente la consulta?


SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

sys@orcl> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1087767317

--------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| TEST_UQ |      1 |      7 |      6 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID">=20 AND "ID"<=25)

Obtuvimos exactamente el mismo resultado que con el BETWEEN.
El hash value entre las 2 consultas sigue siendo el mismo (1087767317).

En esta prueba, colocamos el campo numérico ID en el WHERE pero qué sucedería si utilizamos un campo caracter?


SQL> DROP TABLE test;

Table dropped.

SQL> CREATE TABLE test AS
  2  SELECT to_char(level) id, 'nombre_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 100;

Table created.

SQL> DESC test

 Name          Null?    Type
 ------------- -------- --------------
 ID                     VARCHAR2(40)
 NOMBRE                 VARCHAR2(47)

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      1 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((TO_NUMBER("ID")>=20 AND TO_NUMBER("ID")<=25))
   
SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
-----------------------------------------------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      1 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((TO_NUMBER("ID")>=20 AND TO_NUMBER("ID")<=25))

Como observamos, ambas consultas siguen siendo idénticas.

Estas pruebas nos indican que no existe diferencia alguna entre la utilización del BETWEEN y el >= <=

Crear una base de datos de prueba en minutos!

2010-06-30T19:49:00.006-03:00

La tarea de crear una base de datos puede parecer bastante complicada para algunos o bastante agotadora para otros... pero la realidad es que podemos crear una base de datos de prueba en tan solo minutos sin utilizar entorno gráfico como el X Windows o la VNC. Tan solo ejecutando DBCA podemos crear por linea de comando y de manera no interactica una base de datos!!!

En este sencillo ejemplo creé el archivo testdb.dbt copiando uno de los archivos template que se encuentran en el directorio $ORACLE_HOME/assistants/dbca/templates/. Luego modifiqué este archivo colocando algunas configuraciones para la nueva base de datos que quiero crear. Por último, ejecuté el siguiente comando ...



$ dbca -Silent -CreateDatabase -gdbName testdb -sid testdb -templateName $ORACLE_HOME/assistants/dbca/templates/testdb.dbt -sysPassword oracle -systemPassword oracle -dbsnmpPassword oracle -sysmanPassword oracle



4% complete

Creating and starting Oracle instance

5% complete

6% complete

7% complete

12% complete

Creating database files

20% complete

Creating data dictionary views

22% complete

24% complete

27% complete

28% complete

29% complete

30% complete

31% complete

32% complete

33% complete

34% complete

39% complete

41% complete

44% complete

46% complete

Adding Oracle Application Express

47% complete

48% complete

49% complete

50% complete

51% complete

52% complete

53% complete

54% complete

55% complete

56% complete

57% complete

58% complete

59% complete

60% complete

62% complete

Adding Oracle Warehouse Builder

63% complete

64% complete

65% complete

66% complete

67% complete

75% complete

78% complete

Completing Database Creation

80% complete

83% complete

85% complete

92% complete

100% complete



Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/soldb/testdb.log" for further details.

Existen muchos parámetros opcionales con los que podemos ejecutar el DBCA para crear una base de datos. También, podemos ejecutar DBCA para realizar otras operaciones por linea de comando; como borrar una base de datos, crear scripts, etc.

Podemos ver todas las operaciones que se pueden ejecutar con el comando DBCA -H ...



$ dbca -h



dbca  [-silent | -progressOnly | -customCreate] {  }  | { [ [options] ] -responseFile   } [-continueOnNonFatalErrors ]

Please refer to the manual for details.

You can enter one of the following command:



Create a database by specifying the following parameters:

        -createDatabase

                -templateName 

                [-cloneTemplate]

                -gdbName 

                [-policyManaged | -adminManaged ]

                        [-createServerPool ]

                        [-force ]

                        -serverPoolName 

                        -[cardinality ]

                [-sid ]

                [-sysPassword ]

                [-systemPassword ]

                [-emConfiguration 

                        -dbsnmpPassword 

                        -sysmanPassword 

                        [-hostUserName 

                         -hostUserPassword 

                         -backupSchedule ]

                        [-smtpServer 

                         -emailAddress ]

                        [-centralAgent ]]

                [-disableSecurityConfiguration 

                [-datafileDestination  |  -datafileNames ]

                [-redoLogFileSize ]

                [-recoveryAreaDestination ]

                [-datafileJarLocation  ]

                [-storageType < CFS | ASM >

                        [-asmsnmpPassword     ]

                         -diskGroupName   

                         -recoveryGroupName       

                [-nodelist ]

                [-characterSet ]

                [-nationalCharacterSet  ]

                [-registerWithDirService 

                        -dirServiceUserName    

                        -dirServicePassword    

                        -walletPassword    ]

                [-listeners  ]

                [-variablesFile   ]]

                [-variables  ]

                [-initParams ]

                [-memoryPercentage ]

                [-automaticMemoryManagement ]

                [-totalMemory ]

                [-databaseType ]]



Configure a database by specifying the following parameters:

        -configureDatabase

                -sourceDB    

                [-sysDBAUserName     

                 -sysDBAPassword     ]

                [-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword 

                        -dirServiceUserName    

                        -dirServicePassword    

                        -walletPassword    ]

                [-disableSecurityConfiguration 

                [-enableSecurityConfiguration 

                [-emConfiguration 

                        -dbsnmpPassword 

                        -symanPassword 

                        [-hostUserName 

                         -hostUserPassword 

                         -backupSchedule ]

                        [-smtpServer 

                         -emailAddress ]

                        [-centralAgent ]]





Create a template from an existing database by specifying the following parameters:

        -createTemplateFromDB

                -sourceDB    ::>

                -templateName      

                -sysDBAUserName     

                -sysDBAPassword     

                [-maintainFileLocations ]





Create a clone template from an existing database by specifying the following parameters:

        -createCloneTemplate

                -sourceSID    

                -templateName      

                [-sysDBAUserName     

                 -sysDBAPassword     ]

                [-maintainFileLocations ]

                [-datafileJarLocation       ]



Generate scripts to create database by specifying the following parameters:

        -generateScripts

                -templateName 

                -gdbName 

                [-scriptDest       ]



Delete a database by specifying the following parameters:

        -deleteDatabase

                -sourceDB    

                [-sid    ]

                [-sysDBAUserName     

                 -sysDBAPassword     ]



Add an instance to a cluster database by specifying the following parameters:

        -addInstance

                -gdbName 

                -nodelist 

                [-instanceName ]

                [-sysDBAUserName     ]

                 -sysDBAPassword     

                [-updateDirService 

                        -dirServiceUserName    

                        -dirServicePassword    ]



Delete an instance from a cluster database by specifying the following parameters:

        -deleteInstance

                -gdbName 

                -instanceName 

                [-nodelist ]

                [-sysDBAUserName     ]

                 -sysDBAPassword     

                [-updateDirService 

                        -dirServiceUserName    

                        -dirServicePassword    ]



Query for help by specifying the following options: -h | -help

Simposio HOTSOS 2010

2010-02-17T19:02:00.004-03:00

Todos los años, se realiza un evento llamado Simposio HOTSOS. Este evento esta exclusivamente dedicado a Oracle Performance Tuning.

El Simposio se realiza todos los años en el mes de Marzo en la ciudad de Dallas, Texas (USA). Esta conferencia es única a nivel mundial! Reune a los mejores expertos de Oracle del mundo!

El año pasado, tuve la oportunidad de poder viajar y presenciar el Simposio. Definitivamente se lo recomiendo a todas las personas que utilizan Oracle y que se especializan o que se encuentran interesadas en lo que respecta a Performance Tuning.

Este año (días 7 al 11 de Marzo) volveré a presenciar el Simposio! Nos vemos ahí!!!

Para más información: AQUI

Necesitás ayuda sobre un tema en específico? Postealo AQUI !!!

2009-10-22T01:39:00.006-03:00

A pedido de mucha gente que visita el blog y que utiliza Oracle...

Este post es para que ustedes puedan sugerir los temas que tienen dudas y que les gustaría que se les explicara con más detalle en éste blog. Los temas pueden estar relacionados con Performance Tuning, Administración, Desarrollo, etc.

Los invito a todos a sugerir temas técnicos relacionados con Oracle que requieran ser explicados en detalle.

Saludos a todos!

Añadiendo columnas con valores por defecto y constraint NOT NULL en Oracle 11g

2009-10-20T23:16:00.007-03:00

Antes de la versión 11g, cuando agregabamos una columna a una tabla con valores por defecto y una constraint del tipo NOT NULL, Oracle realizaba un loqueo exclusivo en la tabla para insertar los valores en cada uno de los registros existentes en la misma.

A partir de la versión 11g release 1, ésta operación se optimizó para mejorar en gran medida la utilización de los recursos del sistema y el espacio de almacenamiento de los nuevos valores. Oracle logra ésta optimización, guardando el valor por defecto en el diccionario de datos en vez de modificar todos los registros de la tabla, haciendo la ejecución de ésta operacion de manera instantánea. Gracias a ésto, obtenemos un beneficio enorme a la hora de modificar tablas con millones de registros.

Por otro lado, las siguientes operaciones ADD COLUMN ahora pueden ejecutarse de manera concurrente junto con las operaciones DML:
- Agregar una columna NOT NULL con un valor por defecto.
- Agregar una columna NULL sin un valor por defecto.
- Agregar una columna virtual.

Veamos un ejemplo:

- versión 9i release 2


SQL_9iR2> CREATE TABLE test
  2  NOLOGGING
  3  AS
  4  SELECT level id
  5  FROM dual
  6  CONNECT BY level <= 1000000;

Table created.

SQL_9iR2> ALTER TABLE test ADD (nombre VARCHAR2(100) DEFAULT 'LEONARDO_HORIKIAN' NOT NULL);

Table altered.

Elapsed: 00:01:17.62

SQL_9iR2> DESC test

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NOMBRE                                    NOT NULL VARCHAR2(100)

SQL_9iR2> exec dbms_stats.gather_table_stats(USER, 'TEST');

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT count(*)
  2  FROM test
  3  WHERE nombre IS NULL;

  COUNT(*)
----------
         0

- versión 11g release 1


SQL_11gR1> CREATE TABLE test
  2  NOLOGGING
  3  AS
  4  SELECT level id
  5  FROM dual
  6  CONNECT BY level <= 1000000;

Table created.

SQL_11gR1> ALTER TABLE test ADD (nombre VARCHAR2(100) DEFAULT 'LEONARDO_HORIKIAN' NOT NULL);

Table altered.

Elapsed: 00:00:00.17

SQL_11gR1> DESC test

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NOMBRE                                    NOT NULL VARCHAR2(100)

SQL_11gR1> exec dbms_stats.gather_table_stats(USER, 'TEST');

PL/SQL procedure successfully completed.

SQL_11gR1> SELECT count(*)
  2  FROM test
  3  WHERE nombre IS NULL;

  COUNT(*)
----------
         0

Como podemos observar, creamos una tabla con 1 millón de registros y luego agregamos una columna con valores por defecto. En 9iR2, esa operación se ejecutó en 1 minuto 17 segundos; en cambio en 11gR1, se ejecutó de manera instantánea en tan solo 17 milisegundos!!!

IMPORTANTE: Tengan en cuenta que existen bugs en 11g (leer nota Metalink 602327.1) relacionados con éste tipo de operación.

TKPROF y el parámetro SYS=Y

2009-10-15T12:18:00.006-03:00

Conozco muchas personas que al ejecutar la herramienta TKPROF, lo hacen con el parámetros SYS=N para que no se incluyan en el archivo de salida las consultas recursivas que realiza internamente la base de datos. Si bien en algunos casos se hace ésto para que el archivo de salida no contenga información en exceso, muchas veces cuando buscamos la causa de un determinado problema, si colocamos SYS=N lo único que estaremos logrando será "ocultar" el causante de ese problema.

Hace unos días, investigando un problema de performance en un proceso de un cliente, ejecuté la herramienta TKPROF con el parámetro SYS=Y (que es la opción por defecto) y noté que había una consulta recursiva que estaba leyendo millones de bloques de datos!

La consulta es la siguiente:


select min(bitmapped)
from
 ts$ where dflmaxext =:1 and bitand(flags,1024) =1024

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     7915      0.08       0.07          0          0          0           0
Execute   7915      0.17       0.14          0          0          0           0
Fetch    15830      4.24       4.23          0    3245150          0        7915
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    31660      4.50       4.45          0    3245150          0        7915

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=410 pr=0 pw=0 time=691 us)
      2   TABLE ACCESS FULL TS$ (cr=410 pr=0 pw=0 time=659 us)

Esta consulta es interna de la base de datos. Debido a que el proceso se ejecuta con el usuario APPS y debido a que a éste usuario durante su creación se lo asignó dentro de un grupo de tablespaces temporales (new feature en 10g) llamado TEMP (que contienen los tablespaces TEMP1 y TEMP2), Oracle necesita ejecutar ésta consulta para determinar cuál de los dos tablespaces es el mejor para realizar la operación de sort que está requiriendo el proceso. Como pueden ver, esta consulta es ALTAMENTE costosa y consume muchos recursos del sistema ya que la consulta lee 3,245,150 bloques (410 * 7915) y el proceso en total lee 4,452,813 bloques! Por lo cual, ésta consulta está leyendo más del 70% del total de todos los bloques de datos del proceso!!!

Para solucionar éste problema, lo que hice fue modificar el usuario APPS, sacarlo del grupo de tablespaces temporales TEMP y asignarle directamente el tablespace TEMP2 que tiene un tamaño de 39 GB a comparación del TEMP1 que tiene sólo 6 GB. Al hacer éste cambio, lo que logramos fue que Oracle deje de ejecutar esa consulta debido a que ya no tiene necesidad de determinar cual de los dos tablespaces es el mejor para ejecutar una determinada operación de sort.

Al ejecutar nuevamente el proceso, podemos ver el resultado de la solución implementada:


select min(bitmapped)
from
 ts$ where dflmaxext =:1 and bitand(flags,1024) =1024

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        410          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        410          0           1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=410 pr=0 pw=0 time=3045 us)
      2   TABLE ACCESS FULL TS$ (cr=410 pr=0 pw=0 time=2952 us)

Luego que se realizó éste cambio, se notó una mejora muy importante en cuanto a la performance y se notó una disminución drástica de la utilización de los recursos del sistema.

Tengan siempre presente la importancia del parámetro SYS=Y al momento de ejecutar la herramienta TKPROF.

Por cierto, esta consulta es un Bug! Hay que aplicar el Parche 5455880 para solucionar éste grave problema de performance.

Oracle OpenWorld

2009-09-02T23:28:00.003-03:00

adiós a los RAW-devices en 12g...

2009-07-06T16:18:00.010-03:00

Según la nota de Metalink 578455.1 en 12g NO se seguirán soportando RAW-devices para los archivos físicos de la base de datos (data files, redo logs, control files), OCR (Oracle Cluster Registry) o discos Voting.

IMPORTANTE!!! Este anuncio NO afecta a ASM.

En 12g, ya NO será posible ejecutar la siguiente sentencia porque intenta utilizar RAW-devices directamente...


SQL> create tablespace ABC DATAFILE '/dev/raw/ABC1.dbf' size 2GB;

Lo que tendremos que hacer, es ejecutar alguna de las siguientes sentencias para crear un diskgroup...


SQL> alter diskgroup MYDG add disk '/dev/raw/ABC1.dbf';


SQL> create diskgroup MYDB EXTERNAL REDUNDANCY disk 'dev/raw/ABC1.dbf'

Luego de crear el diskgroup, podemos ejecutar la sentencia que acostumbramos...


SQL> create tablespace ABC DATAFILE '+MYDG' size 2GB;

Para los que tengan que migrar desde RAW-devices, las opciones existentes incluyen ASM (Automatic Storage Management), OCFS (Oracle Cluster File System) y otros sistemas de archivos clúster.

Muchos estarán leyendo ésta nota y diciendo... OUCHHHH!!!!!! Y bueno... tendremos que irnos aconstumbrando a ésta clase de cambios ya que éste es sólo uno de los grandes cambios que veremos en 12g...

Stress Test

2009-06-25T10:03:00.009-03:00

El "Stress Test" es una prueba que mide el comportamiento de nuestro sistema bajo una cierta demanda concurrente de conexiones. Esta es una de las pruebas claves que se DEBEN realizar durante el ciclo de vida del software para garantizar que nuestro sistema va a cumplir con las expectativas previstas cuando sea implementado en producción.

Quiero mostrarles una simple prueba de Stress que hice para que vean qué tan importante es realizarla y qué tan mortal puede ser evitarla.

Ejemplo:

Vamos a crear una tabla llamada TEST que vamos a utilizar en el Stress para cargar datos.


SQL_9iR2> CREATE TABLE test (id NUMBER);

Tabla creada.

Bien, yo tengo creados unos Shell Scripts propios para realizar el Stress Test. Los más importantes son los siguientes:
- iniciar_stress.sh --> Iniciamos el Stress Test.
- reporte.sh --> Genera el reporte con el detalle sobre el Stress Test.

Lo que vamos a realizar es un Stress Test simulando una concurrencia de 100 conexiones simultáneas ejecutando el siguiente bloque PL/SQL anónimo:


BEGIN
  FOR x IN 1 .. 100 LOOP
    INSERT INTO test VALUES (x);
  END LOOP;
END;
/

Como pueden observar, el INSERT que vamos a realizar es muy sencillo y debería ejecutarse rápido, cierto? Veamos cuánto demora si lo ejecuto sin concurrencia:


SQL_9iR2> BEGIN
  2    FOR x IN 1 .. 100 LOOP
  3      INSERT INTO test VALUES (x);
  4    END LOOP;
  5  END;
  6  /

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:01.25

Bien, vemos que demoró 1.25 segundos. El bloque se ejecutó bastante rápido y esperamos que se comporte de la misma manera cuando realicemos el Stress Test... asi que hagamos la prueba para comprobarlo!

Para comenzar el Stress Test, vamos a ejecutar el Shell Script llamado iniciar_stress.sh


$ ./iniciar_stress.sh

=================================================================
====================== STRESS TEST ==============================
=================================================================

Autor: Leonardo Horikian - Oracle Argentina

Descripción: Programa que inicia un StressTest


Apretar ENTER para continuar...

=================================================================

Ingresar la password del usuario STRESS_TEST:


TNS alias (para local dejar en blanco):


Esta instancia se encuentra configurada para soportar 769 conexiones concurrentes!
Cuántas conexiones concurrentes querés ejecutar [1-769]?

100

=================================================================

StressTest listo para ser ejecutado.

Para comenzar, apretar ENTER

=================================================================

nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'
nohup: appending output to `nohup.out'

Como pueden ver, se iniciaron 100 conexiones a la base de datos. Las mismas se ejecutan con el comando nohup realizando la conexión a la instancia vía SQL*PLUS.
Ahora vamos a ejecutar el Shell Script llamado reporte.sh para generar el reporte con la información resultante de la prueba.


$ ./reporte.sh

=================================================================
====================== STRESS TEST ==============================
=================================================================

Autor: Leonardo Horikian - Oracle Argentina

Descripción: Programa que genera el reporte con la información
    sobre la ejecución del StressTest
    

Apretar ENTER para continuar...

=================================================================

Ingresar la password del usuario STRESS_TEST:


TNS alias (para local dejar en blanco):


NOTA: Este script generará un archivo con la información del reporte en el directorio /reportes/.


Ingresar el nombre del archivo de salida:

stress_test.txt


Elegir el TEST_ID con el cual se quiere generar el reporte...


   TEST_ID TEST_DATE                      COMMENTS
********** ****************************** ****************************************
        37 06/25/2009 08:46               100 conexiones concurrentes.

Ingresá el TEST_ID: 37


El archivo fue generado satisfactoriamente!

Abrimos el reporte y vemos lo siguiente...


-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------- INFORMACION SOBRE EL STRESS_TEST -------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
ID: 37 - FECHA: 06/25/2009 08:46 - COMENTARIOS: 100 conexiones concurrentes.
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------- RESULTADO INDIVIDUAL DEL STRESS_TEST (para cada una de las sesiones) -------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 248 - SPID: 11065
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 1.51 - TOTAL_TIME_IN_MIN: .03 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: buffer busy waits                        --> WAITS: 47  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 1.3
EVENT: library cache pin                        --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .52
EVENT: enqueue                                  --> WAITS: 8   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .31
EVENT: latch free                               --> WAITS: 6   - TIMEOUTS: 2   - TIME_WAITED_IN_SEC: .03
EVENT: row cache lock                           --> WAITS: 11  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .03
EVENT: SQL*Net message from client              --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .01
EVENT: control file sequential read             --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: SQL*Net message to client                --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: db file sequential read                  --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 808 - SPID: 11143
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 1.5 - TOTAL_TIME_IN_MIN: .03 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: buffer busy waits                        --> WAITS: 47  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 1.3
EVENT: enqueue                                  --> WAITS: 7   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .3 
EVENT: library cache pin                        --> WAITS: 3   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .16
EVENT: latch free                               --> WAITS: 1   - TIMEOUTS: 1   - TIME_WAITED_IN_SEC: .01
EVENT: SQL*Net message from client              --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .01
EVENT: control file sequential read             --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: SQL*Net message to client                --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 714 - SPID: 11116
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 1.48 - TOTAL_TIME_IN_MIN: .02 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: buffer busy waits                        --> WAITS: 35  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 1.3
EVENT: library cache pin                        --> WAITS: 3   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .31
EVENT: enqueue                                  --> WAITS: 2   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .24
EVENT: SQL*Net message from client              --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .01
EVENT: latch free                               --> WAITS: 2   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: control file sequential read             --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: SQL*Net message to client                --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 204 - SPID: 11110
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 1.48 - TOTAL_TIME_IN_MIN: .02 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: buffer busy waits                        --> WAITS: 38  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 1.2
EVENT: library cache pin                        --> WAITS: 3   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .36
EVENT: enqueue                                  --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .3 
EVENT: db file sequential read                  --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .09
EVENT: row cache lock                           --> WAITS: 7   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .03
EVENT: SQL*Net message from client              --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .02
EVENT: latch free                               --> WAITS: 2   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: SQL*Net message to client                --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: control file sequential read             --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
...
...
CONTINUA ...
...
...

Como podemos observar, el comportamiento para cada sesión fue el mismo que cuando ejecutamos la prueba sin concurrencia. El bloque se ejecutó en un promedio de 1.5 segundos por sesión.

Qué sucede si repetimos la prueba con 300 usuarios concurrentes? El resultado seguirá siendo el mismo? Veamos el reporte de la ejecución...


-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------- INFORMACION SOBRE EL STRESS_TEST -------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
ID: 38 - FECHA: 06/25/2009 09:07 - COMENTARIOS: 300 conexiones concurrentes.
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------- RESULTADO INDIVIDUAL DEL STRESS_TEST (para cada una de las sesiones) -------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 607 - SPID: 15310
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 5.47 - TOTAL_TIME_IN_MIN: .09 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: enqueue                                  --> WAITS: 12  - TIMEOUTS: 1   - TIME_WAITED_IN_SEC: 4.8
EVENT: row cache lock                           --> WAITS: 12  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 2.9
EVENT: latch free                               --> WAITS: 15  - TIMEOUTS: 1   - TIME_WAITED_IN_SEC: 1.2
EVENT: buffer busy waits                        --> WAITS: 11  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .77
EVENT: SQL*Net message from client              --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .06
EVENT: db file sequential read                  --> WAITS: 3   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .04
EVENT: log file sync                            --> WAITS: 1   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .03
EVENT: control file sequential read             --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: SQL*Net message to client                --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 286 - SPID: 15362
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 5.19 - TOTAL_TIME_IN_MIN: .09 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: enqueue                                  --> WAITS: 13  - TIMEOUTS: 1   - TIME_WAITED_IN_SEC: 4.9
EVENT: row cache lock                           --> WAITS: 11  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 2.9
EVENT: latch free                               --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 1.1
EVENT: buffer busy waits                        --> WAITS: 5   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .45
EVENT: SQL*Net message from client              --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .12
EVENT: db file sequential read                  --> WAITS: 3   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .02
EVENT: control file sequential read             --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: SQL*Net message to client                --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 435 - SPID: 15329
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 4.97 - TOTAL_TIME_IN_MIN: .08 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: enqueue                                  --> WAITS: 9   - TIMEOUTS: 1   - TIME_WAITED_IN_SEC: 3.5
EVENT: row cache lock                           --> WAITS: 8   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 2.7
EVENT: latch free                               --> WAITS: 37  - TIMEOUTS: 1   - TIME_WAITED_IN_SEC: 2.5
EVENT: buffer busy waits                        --> WAITS: 7   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .58
EVENT: db file sequential read                  --> WAITS: 17  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .18
EVENT: SQL*Net message from client              --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .07
EVENT: library cache pin                        --> WAITS: 1   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .01
EVENT: control file sequential read             --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: SQL*Net message to client                --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
SID: 202 - SPID: 15746
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
TOTAL_TIME_IN_SEC: 4.63 - TOTAL_TIME_IN_MIN: .08 - TOTAL_TIME_IN_HO: 0
-------------------------------------------------------------------------------------------------------------
- EVENTOS ---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
EVENT: enqueue                                  --> WAITS: 76  - TIMEOUTS: 1   - TIME_WAITED_IN_SEC: 5.5
EVENT: row cache lock                           --> WAITS: 181 - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 2.1
EVENT: latch free                               --> WAITS: 47  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 2.0
EVENT: buffer busy waits                        --> WAITS: 5   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .16
EVENT: SQL*Net message from client              --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .07
EVENT: wait list latch free                     --> WAITS: 1   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: .01
EVENT: control file sequential read             --> WAITS: 4   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: SQL*Net message to client                --> WAITS: 19  - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
EVENT: db file sequential read                  --> WAITS: 2   - TIMEOUTS: 0   - TIME_WAITED_IN_SEC: 0  
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
...
...
CONTINUA ...
...
...

Como observamos, cuando incrementamos la cantidad de conexiones concurrentes, aumentó casi un 400 porciento el tiempo de ejecución de cada conexión!!! Este es un ejemplo muy claro del porqué es TAN importante hacer ésta clase de pruebas de Stress para evitar problemas futuros cuando pasemos nuestro sistema a producción!

Costo super alto!

2009-05-19T17:20:00.011-03:00

En Febrero del 2008, publiqué un post llamado "¿Tunear en base al COSTO del plan de ejecución?". En ese post, les decía que NO hay que tunear en base al costo del plan de ejecución, sino al trabajo que realiza la consulta en la base de datos.

Voy a mostrarles una consulta en el que el costo es super alto pero el trabajo que realiza en la base de datos no lo es:


SQL_10gR2> explain plan for
2  SELECT (t5.column_value).getstringval() t5
3  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
4       TABLE(xmlsequence(t1.column_value))t2,
5       TABLE(xmlsequence(t2.column_value))t3,
6       TABLE(xmlsequence(t3.column_value))t4,
7       TABLE(xmlsequence(t4.column_value))t5;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 4104774429

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |    18E|    15E|    98P  (2)|999:59:59 |
|   1 |  NESTED LOOPS                         |                        |    18E|    15E|    98P  (2)|999:59:59 |
|   2 |   NESTED LOOPS                        |                        |  4451T|    31P|    12T  (2)|999:59:59 |
|   3 |    NESTED LOOPS                       |                        |   544G|  3045G|  1481M  (2)|999:59:59 |
|   4 |     NESTED LOOPS                      |                        |    66M|   254M|   181K  (2)| 00:36:18 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   6 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   7 |     COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   8 |    COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   9 |   COLLECTION ITERATOR PICKLER FETCH   | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E270DE78

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]
5 - VALUE(A0)[40]
6 - VALUE(A0)[40]
7 - VALUE(A0)[40]
8 - VALUE(A0)[40]
9 - VALUE(A0)[40]

Wow!!! Y esos números??? Extremadamente altos no??? Bueno, según el plan de ejecución, esa consulta retorna 18 cuatrillones (18E) de registros, 15 exabytes (15E), tiene un costo de 1.8E19 (98P) y el tiempo de ejecución es de aproximadamente 1 mes (999:59:59)!!!

Veamos qué sucede cuando ejecutamos la consulta y obtenemos las estadísticas de la misma:


SQL_10gR2> SELECT (t5.column_value).getstringval() t5
2  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
3       TABLE(xmlsequence(t1.column_value))t2,
4       TABLE(xmlsequence(t2.column_value))t3,
5       TABLE(xmlsequence(t3.column_value))t4,
6       TABLE(xmlsequence(t4.column_value))t5;

T5
----------------------------------------------------------------
< x/ >

1 fila seleccionada.

Transcurrido: 00:00:00.04

Estadísticas
----------------------------------------------------------
    0  recursive calls
    0  db block gets
     3  consistent gets
    0  physical reads
    0  redo size
  425  bytes sent via SQL*Net to client
  381  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
    1  rows processed

Como podemos observar, la consulta retorna un sólo registro y se ejecuta en tan solo 4 milisegundos, consumiendo sólo 3 bloques de datos.

Este ejemplo es otra prueba de cómo el optimizador puede calcular valores totalmente erróneos en el plan de ejecución. Es por eso, que hay que tener especial cuidado al ver esos números cuando obtenemos un plan de ejecución y al tratar de optimizar la consulta en base a esos números.

Para ésta consulta en particular, el problema es que el optimizador desconoce las estadísticas de las tablas (que en éste caso no son tablas, son llamadas a funciones). Fácilmente, podemos "mejorar" el plan de ejecución, agregando el hint CARDINALITY para decirle al optimizador cuántos registros va a retornar esa función... que en éste caso, es un sólo registro:


SQL_10gR2> explain plan for
2  SELECT /*+ cardinality(t1 1) cardinality(t2 1) cardinality(t3 1) cardinality(t4 1) cardinality(t5 1) */
3        (t5.column_value).getstringval() t5
4  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
5       TABLE(xmlsequence(t1.column_value))t2,
6       TABLE(xmlsequence(t2.column_value))t3,
7       TABLE(xmlsequence(t3.column_value))t4,
8       TABLE(xmlsequence(t4.column_value))t5;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 4104774429

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |     1 |    10 |   122   (2)| 00:00:02 |
|   1 |  NESTED LOOPS                         |                        |     1 |    10 |   122   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                        |                        |     1 |     8 |    97   (2)| 00:00:02 |
|   3 |    NESTED LOOPS                       |                        |     1 |     6 |    73   (2)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                        |     1 |     4 |    49   (3)| 00:00:01 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   6 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   7 |     COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   8 |    COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   9 |   COLLECTION ITERATOR PICKLER FETCH   | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E270DE78

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]
5 - VALUE(A0)[40]
6 - VALUE(A0)[40]
7 - VALUE(A0)[40]
8 - VALUE(A0)[40]
9 - VALUE(A0)[40]

Recuerden siempre el acrónimo GIGO: Garbage In ... Garbage Out :)

DML Error Logging

2009-05-13T13:58:00.007-03:00

Alguna vez trataron de actualizar 10 millones de registros? Que sucedía cuando uno de los registros fallaba? Oracle realizaba un rollback automático de los cambios... y qué sucede si el registro que falló era uno de los últimos en actualizarse? Y bueno... seguramente desperdiciamos todos el tiempo de ejecución de ese proceso ya que los cambios se perdieron tan solo porque un registro falló! A muchos les habrá pasado lo mismo al ejecutar una sentencia del tipo INSERT AS SELECT cierto? En donde uno de los registros no pudo insertarse por X motivo y por consecuencia toda la sentencia falló!

Generalmente, sabemos que la manera más rápida de realizar un DML es en una sola sentencia. Cuando tenemos una sentencia en donde pueden haber registros que terminen en error, se solía armar un procedimiento que recorra los datos que se quieren insertar, updatear, eliminar, etc. e ir ejecutando la senetencia de a un/o varios registros a la vez, y los registros que fallaban, se insertaban en una tabla de errores. Este procedimiento suele ser muy lento, ya que no estamos realizando un DML en una sola sentencia y en una sola vez para todos los registros; sino que estamos recorriendo los datos proceduralmente y realizando el DML de a X cantidad de registros a la vez con el fin de loguear los registros que terminaron en error e ir comiteando, a medida que se va ejecutando la sentencia DML, los registros que terminan satisfactoriamente.

En Oracle 10g Release 2, existe una nueva funcionalidad llamada "DML Error Logging" que nos permite ejecutar una sentencia DML de "principio a fin"... y si en el transcurso de ejecución de esa sentencia, uno o más registros fallaran, esos registros se loguean en una tabla de errores para que luego podramos corregirlos, sin necesidad, de volver a insertar todos los registros nuevamente ya que sólo tendremos que volver a insertar los que terminaron en error.
Por ejemplo: Si intentamos insertar 1 millón de registros, y sólo un registro falla, se insertarán en la tabla final 999,999 de registros, y el registro que terminó en error, se loguea en otra tabla para que luego podamos corregirlo y volverlo a insertar. Acaso no es sensacional ésto???

Veamos un ejemplo muy simple:

Creamos una tabla llamada TEST, en donde vamos a insertar 1 millón de registros.


SQL_10gR2> CREATE TABLE test
2  (
3  ID     NUMBER,
4  NOMBRE VARCHAR2(7)
5  );

Tabla creada.

Creamos una tabla llamada ERROR_LOG_TEST que apunta a la tabla TEST. Esta tabla va a contener todos los registros que terminen en error cuando queramos realizar un DML en la tabla TEST.


SQL_10gR2> EXEC dbms_errlog.create_error_log('TEST','ERROR_LOG_TEST') ;

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> DESC error_log_test

Nombre                                                Nulo?    Tipo
----------------------------------------------------- -------- ------------------------------------
ORA_ERR_NUMBER$                                                NUMBER
ORA_ERR_MESG$                                                  VARCHAR2(2000)
ORA_ERR_ROWID$                                                 ROWID
ORA_ERR_OPTYP$                                                 VARCHAR2(2)
ORA_ERR_TAG$                                                   VARCHAR2(2000)
ID                                                             VARCHAR2(4000)
NOMBRE                                                         VARCHAR2(4000)

Bien, ahora vamos a insertar los registros en la tabla TEST.


SQL_10gR2> INSERT INTO test
2  SELECT level, 'nom_'||level
3  FROM dual
4  CONNECT BY level <= 1000000   
5  LOG ERRORS INTO ERROR_LOG_TEST REJECT LIMIT UNLIMITED;  

999 filas creadas.

WAW! De 1 millón de registros, sólo 999 registros fueron insertados satisfactoriamente, el resto de los registros se insertaron en la tabla de logueo de errores.

Como notarán, use la cláusula LOG ERRORS INTO ... REJECT LIMIT UNLIMITED. Esta cláusula nos permite decirle a Oracle, que queremos utilizar "DML Error Logging" para nuestra sentencia. Fijense que coloqué UNLIMITED como parámetro de REJECT LIMIT. Esto le dice a Oracle, que no se fije en la cantidad de registros que terminan en error, que simplemente me inserte todos esos registros en la tabla de logueo de errores y que continúe con la ejecución de la sentencia hasta que termine. En vez de UNLIMITED, podría haber puesto 100, 1000, etc... que denota el máximo número de registros que quiero que Oracle loguee. Si hay más registros que terminan en error, Oracle simplemente aborta la ejecución de la consulta y devuelve un error por pantalla.


SQL_10gR2> SELECT count(*) FROM error_log_test;

COUNT(*)
----------
999001

1 fila seleccionada.

Como verán, no perdimos ningún registros. Los registros que se insertaron satisfactoriamente están en la tabla final TEST y el resto en la tabla de logueo de errores. Tener los registros en la tabla de logueo, nos permite corregirlos y tratar de insertarlos nuevamente en la tabla TEST.

Veamos porqué falló la inserción de la mayoría de los registros...


SQL_10gR2> SELECT count(*), ora_err_mesg$ FROM error_log_test GROUP BY ora_err_mesg$;

COUNT(*) ORA_ERR_MESG$
---------- ----------------------------------------------------------------------------------------------------
90000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 9, máximo: 7)
 9000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 8, máximo: 7)
    1 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 11, máximo: 7)
900000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 10, máximo: 7)

4 filas seleccionadas.

Como podemos ver, los registros fallaron porque el campo NOMBRE tiene un tamaño de 7 caracteres, y nosotros estamos intentando insertar valores más grandes. Solucionar éste problema es muy sencillo, simplemente, tenemos que agrandar el campo NOMBRE.


SQL_10gR2> ALTER TABLE test MODIFY nombre VARCHAR2(100);

Tabla modificada.

Ahora vamos a tratar de insertar nuevamente los registros en la tabla TEST desde la tabla de logueo de errores.


SQL_10gR2> INSERT INTO test
2  SELECT id, nombre
3  FROM error_log_test;

999001 filas creadas.

SQL_10gR2> SELECT count(*) FROM test;

COUNT(*)
----------
1000000

1 fila seleccionada.

Excelenete!!! Ya tenemos todos los registros en la tabla TEST! Ahora sólo resta truncar la tabla de logueo de errores o simplemente borrarla en caso de que no la vayamos a utilizar nunca más.


SQL_10gR2> TRUNCATE TABLE error_log_test;

Tabla truncada.

CONCLUSIÓN:

Esta nueva funcionalidad está acotada para ser utilizada sólo para algunos casos en particulares, pero espero que en lo posible, todos puedan comenzar a hacer uso de ésta funcionalidad ya que es muy simple de utilizar y muy eficiente a la hora de realizar carga masiva de datos y de logueo de errores de manera simultánea en una sola sentencia DML sin necesidad de recurrir a procedimientos costosos en cuanto a performance, desarrollo, mantenimiento y debuging.

Oracle adquiere Sun Microsystems

2009-05-08T19:54:00.005-03:00

En momentos de crisis... pueden pasar cosas increíbles...

unbreakable Linux... y ahora? unbreakable Solaris??? =)

Para más información... AQUI

Cuántos registros hay en cada bloque de mi tabla?

2009-05-03T00:12:00.010-03:00

En el día de hoy, me llegó una e-mail de una persona preguntándome lo siguiente: "Me podrías decir cómo hago para saber cuántos registros hay en cada bloque de mi tabla?". Bueno, la verdad es que es muy fácil ver cuántos registros caben en cada bloque y también es muy fácil comprobarlo.

Veamos un ejemplo:

En la base de datos de prueba en la que estoy actualmente, tengo bloques de 8 KB.


SQL_10gR2> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

Vamos a crear una tabla llamada TEST con 1.000 registros.


SQL_10gR2> CREATE TABLE test AS
2  SELECT level id, 'nom_'||level nombre
3  FROM dual
4  CONNECT BY level <= 1000;      

Tabla creada.

Para ver la cantidad de bloques que necesité para almacenar los 1.000 registros y la cantidad de registros que hay en cada uno de esos bloques, podemos ejecutar la siguiente consulta.


SQL_10gR2> SELECT dbms_rowid.rowid_block_number(rowid) "Número de Bloque", count(*)
2  FROM test
3  GROUP BY dbms_rowid.rowid_block_number(rowid)
4  ORDER BY dbms_rowid.rowid_block_number(rowid) ASC;

Número de Bloque   COUNT(*)
---------------- ----------
           46196        438
           46197        425
           46198        137

3 filas seleccionadas.

Por lo que podemos observar, en el bloque 46196 tengo 438 registros, en el bloque 46197 tengo 425 registros y en el bloque 46198 tengo 137 registros. Pero cómo hacemos para comprobar que realmente es cierto? Cómo hacemos para verificar que el resultado de la consulta es verdadero? Bueno, lo que vamos a hacer, es realizar un Dump de los 3 bloques y ver la información del Trace que se genera automáticamente. Para ejecutar un Dump, primero necesitamos obtener el número del DataFile donde se encuentra almacenada nuestra tabla (segmento). Para ésto, primero vamos a obtener ésta información y luego a realizar el Dump de los bloques. Veamos...

SQL_10gR2> SELECT header_file FROM dba_segments WHERE segment_name = 'TEST';

HEADER_FILE
-----------
          4

1 fila seleccionada.


SQL_10gR2>  alter system dump datafile 4 block min 46196 block max 46198;

Sistema modificado.

SQL_10gR2> select spid
2  from v$session s, v$process p
3  where p.addr = s.paddr
4  and s.audsid = sys_context('userenv','sessionid')
5  /

SPID
------------
4360

1 fila seleccionada.

Ya se generó el Trace en el directorio especificado en el parámetro user_dump_dest. El nombre con el que se generó es test_ora_4360.trc (el número es el SPID... "System Process Identifier" que obtuvimos). Veamos las partes que más nos interesan del archivo de Trace...


data_block_dump,data header at 0x4f06a7c
===============
tsiz: 0x1f80
hsiz: 0x37e
pbl: 0x04f06a7c
bdba: 0x0100b474
76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46196.
nrow=438  <-- número de registros contenidos dentro del bloque 46196. 
frre=-1
fsbo=0x37e  <-- comienzo del espacio libre del bloque 46196.
fseo=0x6a9  <-- fin del espacio libre del bloque 46196.
avsp=0x32b  <-- espacio disponible del bloque 46196.
tosp=0x32b  <-- espacio total del bloque 46196. 
0xe:pti[0]    nrow=438    offs=0  <-- hay 438 registros en el bloque 46196 comenzando desde el registro número 0.

data_block_dump,data header at 0xa206a7c
===============
tsiz: 0x1f80
hsiz: 0x364
pbl: 0x0a206a7c
bdba: 0x0100b475
 76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46197.
nrow=425  <-- número de registros contenidos dentro del bloque 46197.
frre=-1
fsbo=0x364
fseo=0x69d
avsp=0x339
tosp=0x339
0xe:pti[0] nrow=425 offs=0  <-- hay 425 registros en el bloque 46197 comenzando desde el registro número 0.

data_block_dump,data header at 0xa206a7c
===============
tsiz: 0x1f80
hsiz: 0x124
pbl: 0x0a206a7c
bdba: 0x0100b476
 76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46198. 
nrow=137  <-- número de registros contenidos dentro del bloque 46198. 
frre=-1
fsbo=0x124
fseo=0x177a
avsp=0x1656
tosp=0x1656
0xe:pti[0] nrow=137 offs=0  <-- hay 137 registros en el bloque 46198 comenzando desde el registro número 0.

Bien, con éste ejemplo pudimos comprobar y verificar el resultado de nuestra primer consulta.

Encriptando columnas con TDE (Transparent Data Encryption) en tablas con millones de registros

2009-05-02T01:13:00.019-03:00

Para los que no conocen TDE, es una nueva funcionalidad de 10g R2 que permite proteger datos sensibles de las columnas de nuestras tablas encriptando los datos al almacenarlos en los respectivos Data Files en el sistema operativo. Para protegernos de personas malintencionadas que quieran desencriptar los datos sin autorización, guarda las claves de encriptamiento en un módulo seguro externo a la base de datos.

Mi intención en éste post no es mostrar el paso a paso de cómo implementar TDE, sino mostrarles cómo encriptar columnas con TDE en tablas con millones de datos de la manera más eficiente y con el menor impacto posible en cuanto a la performance.

En el momento en el que encriptamos columnas en una tabla, sólo podemos acceder a la tabla en modo lectura... NO están permitidas las operaciones DML hasta que el encriptamiento termine. En tablas chicas, con muy pocos datos, ésto no suele perjudicarnos demasiado. Pero qué sucede en tablas con millones de registros? En este caso, el encriptamiento puede durar varias horas!!!

La estrategia que vamos a utilizar para encriptar columnas en tablas con millones de registros, es utilizando el paquete DBMS_REDEFINITION.

Veamos un ejemplo:

Tengo una tabla llamada TEST_TDE con 1 millón de registros. Esta tabla tiene 3 columnas: ID (primary key), NOMBRE, NUM_TARJETA. La columna que vamos a encriptar es NUM_TARJETA ya que tiene los números de tarjetas, y como para mi es información sensible, quiero encriptarla y protegerla con TDE.


SQL_10gR2> SELECT count(*) FROM test_tde;

COUNT(*)
----------
1000000

1 fila seleccionada.

SQL_10gR2> desc TEST_TDE

Nombre                                     Nulo?   Tipo
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
NOMBRE                                             VARCHAR2(44)
NUM_TARJETA                                        NUMBER

SQL_10gR2> SELECT index_name, column_name FROM dba_ind_columns WHERE table_name = 'TEST_TDE';

INDEX_NAME                               COLUMN_NAME
---------------------------------------- ----------------------------------------
TEST_TDE_ID_PK                           ID

1 fila seleccionada.

SQL_10gR2> SELECT constraint_name, constraint_type FROM dba_constraints WHERE table_name = 'TEST_TDE';

CONSTRAINT_NAME                C
------------------------------ -
TEST_TDE_ID_PK                 P

1 fila seleccionada.

Bien, ahora vamos a encriptar la columna NUM_TARJETA de la manera tradicional y sin utilizar ninguna técnica. En la SESIÓN 1, voy a encriptar la columna, en la SESIÓN 2, voy a modificar el valor de uno de los registros de la tabla mientras se está realizando el procedimiento de encriptado... veamos qué sucede!

SESIÓN 1:


SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta ENCRYPT);

Tabla modificada.

Transcurrido: 00:01:20.11

SESIÓN 2:

SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;

1 fila actualizada.

Transcurrido: 00:01:17.64

Como podemos observar, el encriptamiento de 1 millón de registros demoró 1 minuto 20 segundos. Mientras que se encriptaban los datos, ejecuté un UPDATE en otra sesión y demoró 1 minuto 17 segundos. Esto es debido a que la tabla quedó loqueda en la SESIÓN 1 por el proceso de encriptamiento y la SESIÓN 2 tuvo que esperar que termine de encriptar los datos para poder modificarlos. Imaginen los problemas de performance que tendrían en sus aplicaciones si tienen varios usuarios concurrentes realizando operaciones DML sobre esa tabla mientras se está ejecutando el procedimiento de encriptación!!!

Si ejecutamos el mismo UPDATE luego de que los datos ya se encuentran encriptados, podemos observar que no demoró nada en ejecutarse la operación DML.


SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;

1 fila actualizada.

Transcurrido: 00:00:00.00

Ahora vamos a ver la técnica que les mencionaba con el paquete DBMS_REDEFINITION. Lo primero que vamos a hacer, es ejecutar el procedimiento CAN_REDEF_TABLE de éste paquete para corroborar que la tabla puede redefinirse online.


SQL_10gR2> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST', 'TEST_TDE', dbms_redefinition.cons_use_pk);

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.31

Como el procedimiento terminó sin errores, quiere decir que la tabla puede ser redefinida online.
Ahora vamos a comenzar el proceso de redefinir la tabla. Para ésto, primero tenemos que crear una tabla intermedia (TEST_TDE_TEMP) con la estructura de la tabla TEST_TDE en donde Oracle va a colocar los datos de la tabla original de manera temporal.


SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta DECRYPT);

Tabla modificada.

Transcurrido: 00:01:04.53

SQL_10gR2> SET long 400000000

SQL_10gR2> SELECT dbms_metadata.get_ddl('TABLE', 'TEST_TDE', 'TEST') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','TEST_TDE','TEST')
--------------------------------------------------------------------------------

CREATE TABLE "TEST"."TEST_TDE"
(    "ID" NUMBER,
"NOMBRE" VARCHAR2(44),
"NUM_TARJETA" NUMBER,
CONSTRAINT "TEST_TDE_ID_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

1 fila seleccionada.

SQL_10gR2> CREATE TABLE TEST_TDE_TEMP
2  (
3  ID          NUMBER,
4  NOMBRE      VARCHAR2(44),
5  NUM_TARJETA NUMBER ENCRYPT,
6  CONSTRAINT TEST_TDE_TEMP_ID_PK PRIMARY KEY (ID)
7  );

Tabla creada.

Como pueden notar, lo primero que hice fue desencriptar la columna NUM_TARJETA para poder volver a encriptarla con ésta técnica. Luego utilicé el paquete DBMS_METADATA para ver la estructura de la tabla TEST_TDE y poder crear una similar con el nombre TEST_TDE_TEMP.

Ahora vamos a redefinir la tabla con el procedimiento START_REDEF_TABLE.


SQL_10gR2> exec dbms_redefinition.start_redef_table('TEST', 'TEST_TDE', 'TEST_TDE_TEMP', 'id, nombre, num_tarjeta', DBMS_REDEFINITION.CONS_USE_PK);

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:41.18

SQL_10gR2> SELECT count(*) FROM test_tde_temp;

COUNT(*)
----------
1000000

1 fila seleccionada.

SQL_10gR2> desc TEST_TDE_TEMP

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER ENCRYPT

SQL_10gR2> SELECT master, log_table FROM dba_mview_logs WHERE log_table LIKE '%TEST%';

MASTER                         LOG_TABLE
------------------------------ ------------------------------
TEST_TDE                       MLOG$_TEST_TDE

1 fila seleccionada.

Fijense, que al redefinir la tabla, Oracle copió los datos de la tabla TEST_TDE y los insertó en la tabla transitoria TEST_TDE_TEMP con la columna NUM_TARJETA encriptada. A su vez, Oracle creó una vista materializada para registrar cualquier cambio que se realice sobre la tabla TEST_TDE para luego, en el momento de hacer la sincronización de datos entre las 2 tablas, pueda impactar cualquier cambio realizado.

Antes de realizar el proceso de sincronización entre las 2 tablas, voy a agregar algunos registros a la tabla TEST_TDE para ver si luego de la sincronización, los últimos cambios que realicé fueron impactados. Recuerden, que todos éstos pasos los estamos haciendo online, y que por el momento, los usuarios siguen trabajando sobre la tabla TEST_TDE sin ningún tipo de impacto en cuanto a la performance.


SQL_10gR2> INSERT INTO test_tde
2  SELECT * FROM
3  (
4  SELECT level id, 'nom_'||level, round(dbms_random.value(100000000000,900000000000))
5  FROM dual
6  CONNECT BY level <= 1000100  
7  )  
8  WHERE id > 1000000;

100 filas creadas.

Transcurrido: 00:00:06.93

SQL_10gR2> COMMIT;

Confirmación terminada.

Ahora vamos a sincronizar con el procedimiento SYNC_INTERIM_TABLE, las 2 tablas para aplicar cualquier cambio realizado en la tabla TEST_TDE a la tabla TEST_TDE_TEMP.
En éste paso en adelante, necesitamos una ventana de tiempo muy chica para ejecutar los 2 procedimientos que restan. En esta ventana de tiempo, los usuarios no deben estar modificando la tabla TEST_TDE.


SQL_10gR2> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'TEST_TDE', 'TEST_TDE_TEMP');

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.29

SQL_10gR2> SELECT count(*) FROM test_tde_temp;

COUNT(*)
----------
1000100

1 fila seleccionada.

Transcurrido: 00:00:06.92

SQL_10gR2> desc TEST_TDE

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER

Los 100 registros que habiamos agregado a la tabla TEST_TDE fueron impactados con éxito en la tabla TEST_TDE_TEMP! Para finalizar, lo único que resta es ejecutar el procedimiento FINISH_REDEF_TABLE para aplicar en la tabla TEST_TDE todos los cambios de la tabla TEST_TDE_TEMP (incluyendo la columna encriptada).

SQL_10gR2> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST', 'TEST_TDE', 'TEST_TDE_TEMP');

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.37

SQL_10gR2> desc TEST_TDE

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER ENCRYPT

SQL_10gR2> DROP TABLE test_tde_temp;

Tabla borrada.

Con el mas mínimo impacto en la performance, pudimos encriptar la columna NUM_TARJETA de la tabla TEST_TDE (que contiene 1 millón de registros) y no afectar las operaciones de los usuarios online sobre esa tabla! Sólo tuvimos que necesitar una ventana de tiempo de 1 minuto para colocar la tabla TEST_TDE nuevamente disponible para todos los usuarios.

En lo posible, evitá utilizar el comando "EXPLAIN PLAN FOR" !!!

2009-04-30T15:11:00.006-03:00

"EXPLAIN PLAN FOR" tiene problemas...

1er. PROBLEMA: Trata todas las Bind Variables como VARCHAR2.

Veamos un ejemplo:


SQL_10gR2> DESC emp

Nombre                  Nulo?    Tipo
----------------------- -------- -------------
 EMPNO                   NOT NULL NUMBER(4)
ENAME                            VARCHAR2(10)
JOB                              VARCHAR2(9)
GENDER                           VARCHAR2(1)
MGR                              NUMBER(4)
HIREDATE                         DATE
SAL                              NUMBER(7,2)
COMM                             NUMBER(7,2)
DEPTNO                           NUMBER(2)

SQL_10gR2> VAR bind NUMBER;

SQL_10gR2> EXECUTE :bind := 7900

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> explain plan for
2  SELECT * FROM emp WHERE empno = :bind;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    39 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    39 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMPNO_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:BIND))

Como podemos ver, creé una variable del tipo NUMBER pero al ejecutar el comando "EXPLAIN PLAN FOR" utilizando esa variable, Oracle la convirtió en una variable del tipo VARCHAR2 para luego aplicarle la función de conversión TO_NUMBER y volver a convertirla en tipo NUMBER.

2do. PROBLEMA: Puede NO mostrarte el plan de ejecución real que será utilizado en el ejecución de tu consulta.


SQL_10gR2> DESC dept
Nombre                  Nulo?    Tipo
----------------------- -------- ----------------
 DEPTNO                  NOT NULL VARCHAR2(10)
DNAME                            VARCHAR2(14)
LOC                              VARCHAR2(13)

SQL_10gR2> SELECT * FROM dept;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON

4 filas seleccionadas.

SQL_10gR2> VAR bind NUMBER;

SQL_10gR2> EXECUTE :bind := 20

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> explain plan for
2  SELECT * FROM dept WHERE deptno = :bind;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT           |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_DEPTNO_PK |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:BIND)

SQL_10gR2> SELECT * FROM dept WHERE deptno = :bind;

DEPTNO     DNAME          LOC
---------- -------------- -------------
20         RESEARCH       DALLAS

1 fila seleccionada.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DEPTNO")=:BIND)

Como pueden observar, la primera vez que obtuve el plan de ejecución de la consulta, lo hice con el comando "EXPLAIN PLAN FOR" y vemos que estamos utilizando un índice para acceder a los datos. La segunda vez, optamos por ejecutar la consulta y luego obtener el plan de ejecución REAL (utilizando dbms_xplan.display_cursor). Qué observamos? Oracle está realizando un acceso a datos através de un FULL SCAN de la tabla. Esto sucedió porque el tipo de dato VARCHAR2 es siempre elegido para ser convertido en una comparación de tipos de datos distintos. En este caso, como la columna DEPTNO es del tipo VARCHAR2, y como estoy comparando esa columna con una variable del tipo NUMBER, Oracle tuvo que aplicar la función TO_NUMBER a la columna DEPTNO y como ya sabemos, si aplicamos una función a una columna indexada, Oracle no puede utilizar el índice en esa columna ya que la función lo deshabilita. Cuando obtuve el plan de ejecución de la primer consulta no hubo ningún tipo de conversión de datos (es por eso que accedimos por índice) ya que estoy comparando una columna del tipo VARCHAR2 (deptno) con una variable NUMBER... pero que en realidad esa variable NUMBER es un VARCHAR2 (ya que como dijimos anteriormente, si ejecutamos el comando "EXPLAIN PLAN FOR", Oracle trata todas las Bind Variables como VARCHAR2).

Siempre es bueno tener en mente cuál es la diferencia entre lo IDEAL y lo REAL. Lo IDEAL para éste caso sería acceder por índice a los datos, lo REAL es que Oracle está realizando lo contrario.

Diferencias entre COUNT(1) y COUNT(*) - Parte 2

2009-04-29T18:03:00.005-03:00

Hace unas semanas, se me acercaron y me hicieron la siguiente pregunta:

"Me dijeron que si en una consulta se coloca el COUNT(*), por cada registro que leamos en un acceso por índice, vamos a tener que acceder también a la tabla ya que el símbolo * significa que estoy colocando todas las columnas de la tabla en la consulta, y si no tengo todas las columnas de la tabla en el índice, entonces Oracle tiene que acceder a la tabla a buscar el resto de las columnas. Es cierto?".

Bueno, no... no es cierto. Para validar el porque digo ésto, primero veamos un ejemplo:


SQL_10gR2> CREATE TABLE test AS
2  SELECT level id, 'texto_'||level texto
3  FROM dual
4  CONNECT BY level <= 100000 ; 

Tabla creada. 

SQL_10gR2> CREATE UNIQUE INDEX test_id_uq ON test(id) ;

Índice creado.

SQL_10gR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',CASCADE=>TRUE) ;

Procedimiento PL/SQL terminado correctamente.

Bien, veamos el plan de ejecución de la siguiente consulta:

SQL_10gR2> SELECT COUNT(*)
2  FROM test
3  WHERE id = 100;

COUNT(*)
----------
      1

1 fila seleccionada.

SQL_10gR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  d6urw3zfuxz32, child number 0
-------------------------------------
SELECT COUNT(*) FROM test WHERE id = 100

Plan hash value: 4041652814

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN| TEST_ID_UQ |      1 |      1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)

Como podemos observar, coloqué el COUNT con el símbolo * y accedí al índice con el ID 100; pero luego de acceder al índice NO accedí a la tabla, simplemente accedí al índice (ya que tiene la columna que estoy utilizando en el predicado). Como anteriormente dije en el post "Diferencias entre COUNT(1) y COUNT(*) - Parte 1", no existe ninguna diferencia entre el COUNT(1) y COUNT(*), pero si hay diferencia si ejecutamos COUNT(*) y COUNT(nonbre_de_columna) ya que si colocamos una columna de la tabla en el COUNT, Oracle hace un conteo sólo de los valores de esa columna que NO tengan valores nulos.

Explain Plan Vs. Bind Variables

2008-09-29T11:19:00.003-03:00

Obtener el plan de ejecución de una consulta que contiene Bind Variables sin haberlas reemplazado??? NO!!! NO!!! NO!!!!!!!!!!

Ya hablamos en otras ocasiones del beneficio que obtenemos al utilizar Bind Variables y también explicamos qué son. Cuando ejecutamos una consulta con Bind Variables (sin haberlas reemplazado) para obtener el plan de ejecución, el optimizador de costos (CBO) no sabe el valor de la Bind Variable; y por lo tanto, calcula la selectividad del filtro utilizando reglas definidas por defecto. Que quiere decir ésto? Que el plan de ejecución que obtenemos puede ser MUY distinto al plan de ejecución real!!! Porqué muy distinto? Porque todo depende del valor con el que se reemplazará la Bind Variable y el tipo de dato de la misma.

Veamos un ejemplo:


SQL_10gR> CREATE TABLE test AS
  2 SELECT TO_CHAR(level) id, 'test'||level descripcion
  3 FROM dual
  4 CONNECT BY level <= 100000;

Table created.

SQL_10gR> DESC test

Name                    Null?    Type
----------------------- -------- ----------------
ID                               VARCHAR2(40)
DESCRIPCION                      VARCHAR2(44)

SQL_10gR> CREATE UNIQUE INDEX test_uq ON test(id, descripcion);

Index created.

SQL_10gR> EXEC dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) ;

PL/SQL procedure successfully completed.

Supongamos que detectamos un problema grave en la performance de una de nuestras aplicaciones. Al identificar la consulta que nos está causando problemas, obtenemos el plan de ejecución de la misma para ver si está accediendo correctamente...

Ejecutamos la consulta con Bind Variable:

SQL_10gR> EXPLAIN PLAN FOR
  2 SELECT descripcion
  3 FROM test
  4 WHERE id = :b1;

Explained.

SQL_10gR> @explains
Plan hash value: 1087767317

----------------------------------------------------------------------------
| Id | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time      |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT |         |     1 |    15 |       2 (0)|  00:00:01 |
|* 1 | INDEX RANGE SCAN | TEST_UQ |     1 |    15 |       2 (0)|  00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------
1 - access("ID"=:B1)

Por lo que vemos en el plan de ejecución, si filtramos la columna ID con un valor del mismo tipo de dato, el optimizador eligirá acceder por índice en vez de realizar un full scan de la tabla. Esto suena lógico sabiendo que los valores de la columna ID son únicos y que por cada valor con el que filtremos, a lo sumo obtendremos una ocurrencia del mismo valor en la tabla.

Ejecutamos una consulta sin Bind Variable:

SQL_10gR> EXPLAIN PLAN FOR
  2 SELECT descripcion
  3 FROM test
  4 WHERE id = 10000;

Explained.

SQL_10gR> @explains
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time        |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    1 |    15 |      54 (4)|    00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |    1 |    15 |      54 (4)|    00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------
1 - filter(TO_NUMBER("ID")=10000)

Pero qué sucede si filtramos la columna ID con un valor de distinto tipo de dato? Como en éste caso estamos realizando una conversión implícita, el optimizador no puede utilizar el índice que tenemos creado en la tabla y por lo tanto se ve forzado a realizar un full scan de la misma.

Si en nuestra aplicación el problema es justamente éste (que estamos realizando una conversión implícita), si no reemplazamos las Bind Variables con valores reales, estaremos pensando que el optimizador está accediendo de la manera correcta... cuando en realidad ésto no es cierto.

Recuerden lo siguiente: Siempre que obtengan el plan de ejecución de una consulta... reemplacen las Bind Variables con valores reales!!! En caso contrario... no deberíamos fiarnos demasiado con el plan de ejecución obtenido.

¿Tunear en base al COSTO del plan de ejecución?

2008-02-26T15:39:00.008-02:00

Todos los días observo que alguien está queriendo tunear una consulta en base al costo del plan de ejecución. Pero... ¿Qué es el COSTO? ¿Qué representa? La respuesta es simple: El costo representa unidades de trabajo o recursos utilizados. El optimizador usa I/O a disco, CPU y memoria como unidades de trabajo. Entonces, el costo para una determinada consulta representa una estimación de la cantidad de I/O a disco, de CPU y memoria que se utilizará para la ejecución de la consulta.

Bien, con ésto ya dicho, porqué hay personas que tratan de tunear una consulta en base al costo??? El costo es simplemente un número que le asigna el optimizador de costos (CBO) a la consulta para saber qué plan de ejecución elegir entre todos los planes que genera en el momento de la optimización (el plan de ejecución que se genera con el menor costo es el que Oracle utiliza para ejecutar nuestra consulta), pero no existe un "mejor número" que debemos tener en mente para deducir si una consulta es óptima o no.

NO debemos tunear en base al costo. SI debemos tunear en base a los I/O lógicos (LIO's).

Veamos un ejemplo:


SQL_9iR2> CREATE TABLE test AS
2  SELECT level id, 'nombre_'||level nom
3  FROM dual
4  CONNECT BY level <= 100000 ; 

Table created. 

SQL_9iR2> EXEC dbms_stats.gather_table_stats(user,'TEST') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT nom
2  FROM test
3  WHERE id = 50000 ;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=1 Bytes=17)
1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=1 Bytes=17)

Statistics
----------------------------------------------------------
     5  recursive calls
     0  db block gets
   323  consistent gets
     0  physical reads
     0  redo size
   335  bytes sent via SQL*Net to client
   495  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     2  sorts (memory)
     0  sorts (disk)
     1  rows processed

Como podemos observar, el costo de la consulta es de 49. Si yo tuneo en base al costo no puedo saber si el plan de ejecución que se está utilizando para ésta consulta es óptimo o no porque no sé que costo sería el ideal para saberlo. Es por eso que NO debemos tunear en base al costo. Por otro lado, podríamos tunear en base a la cantidad de I/O lógicos que se estén realizando. En éste ejemplo, se realizan 323 LIO's. Si observamos la consulta, estoy seleccionando la columna NOM que corresponde al ID 50000 . Como creé la tabla de forma tal que todos los ID's sean únicos, ésta consulta me debería traer un solo registro. Si nos ponemos a pensar, hacer 323 LIO's para traer sólo un registro es demasiado. En éste momento es en donde nos damos cuenta que tenemos un problema de performance porque estamos haciendo un Full Scan de una tabla de 100.000 registros para buscar solamente el ID 50000 que nos devuelve un sólo registro.

Veamos qué sucede si creamos un índice único por la columna ID,NOM...


SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq ON test(id,nom) ;

Index created.

SQL_9iR2> EXEC dbms_stats.gather_index_stats(user,'TEST_ID_NOM_UQ') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT nom
2  FROM test
3  WHERE id = 50000 ;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1    0   INDEX (RANGE SCAN) OF 'TEST_ID_NOM_UQ' (UNIQUE) (Cost=2 Card=1 Bytes=17)

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
     3  consistent gets
     0  physical reads
     0  redo size
   335  bytes sent via SQL*Net to client
   495  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     1  rows processed

Luego de crear un índice único por la columna ID,NOM, ejecutando nuevamente la consulta, notamos que ahora sólo estamos realizando 3 LIO's y, por consiguiente, el costo se decrementó ya que estamos utilizando menos recursos que el caso anterior.

¿Cuándo utilizar HINTS?

2008-02-26T09:45:00.005-02:00

Aunque el optimizador es increiblemente exacto a la hora de elegir el correcto plan de ejecución para las consultas, no es perfecto! Oracle nos provee de Hints que podemos utilizar en una determinada consulta para influenciar al optimizador a que elija otro determinado plan de ejecución, con la esperanza de que obtengamos mejor performance para la consulta.

Una pregunta que me hacen a menudo es: ¿Cuándo utilizar HINTS? Mi respuesta es...

Los Hints se utilizan como ULTIMO recurso. Nunca como el primero ni tampoco como un estándar a seguir.

Son útiles si estamos utilizando el optimizador por reglas (RBO), pero con el optimizador por costos (CBO) deberíamos dejar que el optimizador tome las decisiones sobre qué plan de ejecución sería el más conveniente para nuestra consulta. Si utilizamos el CBO, DEBEMOS obtener estadísticas para los objetos que utilizamos para lograr que el CBO pueda decidir correctamente el plan de ejecución que se utilizará.

Suele suceder que muchas personas utilizan los Hints porque analizan un plan de ejecución y piensan que el CBO está tomando una mala decisión al hacer un Full Scan de una tabla, y entonces, colocan un Hint para que acceda por índice. Otras personas utilizan los Hints porque piensan que el CBO tomó una mala decisión a la hora de elegir el plan de ejecución correcto para nuestra consulta. Si ésto es verdad, porque el CBO eligió mal? Obtuvimos correctamente las estadísticas para nuestras tablas e índices? Sería conveniente obtener Hitogramas también? Nos está faltando crear un índice? Todas estas cuestiones deberíamos investigarlas en detalle cuando vemos que el CBO no se comporta como nosotros esperamos.

El CBO es sólo un software, no tiene una mente propia. Toma sus decisiones en base a la información (estadísticas) que nosotros le damos. Por tal motivo, si le damos información correcta y actual, podremos obtener un plan de ejecución óptimo para la ejecución de nuestra consulta.

DB_FILE_MULTIBLOCK_READ_COUNT (MBRC)

2007-10-16T16:19:00.000-03:00

El parámetro DB_FILE_MULTIBLOCK_READ_COUNT especifica la cantidad de bloques que van a ser leídos en cada I/O a través de un Full Scan.

En Oracle 10g Release 2, el valor por default de éste parámetro, es el valor que corresponde a la cantidad máxima de I/O que se puede realizar de forma más eficiente.

En ambientes OLTP o Batch, éste parámetro suele setearse en valores entre 4 y 16 bloques. En ambientes DSS o Data Warehouse, éste parámetro suele setearse en un valor mayor.

Este parámetro afecta al Optimizador de Costos (CBO) ya que si seteamos un valor alto, el CBO puede ser influenciado en elegir realizar un Full Scan en vez de acceder por índice. Como en los ambientes DSS o Data Warehouse suelen utilizarse planes de ejecución que incluyen Full Scan, aumentar el valor de éste parámetro puede ser beneficioso.

El máximo valor que podemos setear depende del sistema operativo.
La fórmula que se utiliza para buscar el valor de éste parámetro es:


DB_FILE_MULTIBLOCK_READ_COUNT = ( (MAX I/O SIZE) / DB_BLOCK_SIZE )

Si elegimos un valor mayor al máximo soportado, Oracle simplemente elige el valor máximo que puede utilizar.

Veamos un ejemplo de cómo buscar el valor máximo del parámetro DB_FILE_MULTIBLOCK_READ_COUNT sin utilizar la fórmula:

Primero, veamos el valor actual del MBRC:


SQL_9iR2> show parameter multiblock

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
db_file_multiblock_read_count  integer     16

Observemos el Explain Plan de una de las tablas:


SQL_9iR2> explain plan for
  2  SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
  3  FROM test ;

Explained.

SQL_9iR2> @explains
Plan hash value: 3740828345

---------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |     1 | 46709   (1)| 00:10:55 |
|   1 |  SORT AGGREGATE       |                       |     1 |            |          |
|   2 |   TEST ACCESS FULL    | TEST                  |    20M| 46709   (1)| 00:10:55 |
---------------------------------------------------------------------------------------

El costo de la consulta es 46709.
Veamos qué sucede si seteo el valor del parámetro muy alto:


SQL_9iR2> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 50000 ;

Session altered.

SQL_9iR2> show parameter multiblock

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
db_file_multiblock_read_count  integer     64

Bien, como podemos observar, seteamos el parámetro en 50.000 bloques; pero como excedemos el máximo permitido, Oracle setea el máximo valor que puede alcanzar.

Ahora veamos si Oracle realmente está utilizando el nuevo MBRC que acabamos de setear:


SQL_9iR2> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ;

Session altered.

SQL_9iR2> SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
  2  FROM test ;

  COUNT(*)
----------
  52673028

1 row selected.

SQL_9iR2> ALTER SESSION SET EVENTS '10046 trace name context off' ;

Session altered.

SQL_9iR2> @trace_file_name

TRACE_FILE_NAME
-----------------------------
testdb_ora_11379.trc

1 row selected.

[test@linux_test udump]$ cat testdb_ora_11379.trc | grep "scattered read" | awk '{ split ($11,listado,"="); print listado[2];}' | sort -n | tail -1

64

[test@linux_test udump]$ more testdb_ora_11379.trc | grep scattered

...
...
WAIT #1: nam='db file scattered read' ela= 1411 file#=115 block#=241927 blocks=64 obj#=70390 tim=1164040832168738
WAIT #1: nam='db file scattered read' ela= 1394 file#=115 block#=241991 blocks=64 obj#=70390 tim=1164040832171544
WAIT #1: nam='db file scattered read' ela= 1426 file#=115 block#=242055 blocks=64 obj#=70390 tim=1164040832174390
WAIT #1: nam='db file scattered read' ela= 1399 file#=115 block#=242119 blocks=64 obj#=70390 tim=1164040832177182
WAIT #1: nam='db file scattered read' ela= 1391 file#=115 block#=242183 blocks=64 obj#=70390 tim=1164040832179966
...
...

Bien. Vemos que efectivamente estamos leyendo 64 bloques en cada I/O que realizamos.

Ahora ejecutamos nuevamente nuestra consulta y observamos el Explain Plan:


SQL_9iR2> explain plan for
  2  SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
  3  FROM test ;

Explained.

SQL_9iR2> @explains
Plan hash value: 3740828345

---------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |     1 | 41994   (1)| 00:09:49 |
|   1 |  SORT AGGREGATE       |                       |     1 |            |          |
|   2 |   TEST ACCESS FULL    | TEST                  |    20M| 41994   (1)| 00:09:49 |
---------------------------------------------------------------------------------------

Ahora el costo de la consulta pasa a ser 41994.

Hay que tener mucho cuidado si decidimos modificar éste parámetro ya que no siempre obtenemos un beneficio. Recuerden que modificando el MBRC por default puede producir que nuestro sistema funcione peor, igual o mejor. Por lo general, el MBRC por default suele ser el correcto.

Mi consejo es que no modifiquen el MBRC si no es necesario.

Problemas utilizando Analytic Functions junto con PL/SQL en 8i

2007-09-29T17:14:00.000-03:00

La idea de éste post no es explicar el funcionamiento de las Analytic Functions, sino el error PLS-00103 al utilizar Analytic Functions.
La mejor manera de explicar éste problema, es realizando un ejemplo.

La tabla de prueba que vamos a utilizar en nuestro ejemplo, contiene los siguientes registros:


SQL> SELECT * FROM test ;

        ID      NIVEL    SALARIO
---------- ---------- ----------
        10          1       2500
        20          2       3000
        30          1       3500
        40          2       4000
        50          1       4500
        60          2       5000
        70          1       5500
        80          2       6000
        90          1       6500
       100          2       7000

10 rows selected.

Primero vamos a realizar el ejemplo en una base de datos 8.1.7.4.0


SQL_8i> SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY salario DESC) AS rank
  2      FROM test ;

        ID      NIVEL    SALARIO       RANK
---------- ---------- ---------- ----------
        90          1       6500          1
        70          1       5500          2
        50          1       4500          3
        30          1       3500          4
        10          1       2500          5
       100          2       7000          1
        80          2       6000          2
        60          2       5000          3
        40          2       4000          4
        20          2       3000          5

10 rows selected.

Como podemos observar, si ejecutamos esa consulta con Analytic Functions en SQL*Plus, funciona a la perfección. Veamos qué sucede si intentamos ejecutar la consulta dentro de PL/SQL:


SQL_8i> CREATE PROCEDURE pr_test
  2  IS
  3  BEGIN
  4      --
  5      FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
 nivel ORDER BY salario DESC) AS rank
  6                 FROM test ) LOOP
  7          --
  8          dbms_output.put_line('ID: '||cur.id||
  9                               ' - NIVEL: '||cur.nivel||
 10                               ' - SALARIO: '||cur.salario||
 11                               ' - RANK: '||cur.rank) ;
 12          --
 13      END LOOP ;
 14      --
 15  END pr_test ;
 16  /

Warning: Procedure created with compilation errors.

SQL_8i> show errors

Errors for PROCEDURE PR_TEST:

LINE/COL     ERROR
-----------  ------------------------------------------------------------------------
5/63         PLS-00103: Encountered the symbol "(" when expecting one of the following:
             , from

Este error se debe a que según la Nota 147808.1, no podemos utilizar Analytic Functions dentro de PL/SQL en versiones anteriores a 9i. Podemos utilizar éste tipo de funciones en SQL, pero no en PL/SQL. Desde las versiones de la 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.

Hay 2 formas de solucionar éste problema:
- Crear vistas utilizando Analytic Function y luego hacer referencia a esas vistas dentro de PL/SQL.
- Usar Dynamic SQL.

Veamos la implementación de esas 2 soluciones:

Utilizando una vista...


SQL_8i> CREATE VIEW pr_test_view AS
  2  SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY s
alario DESC) AS rank
  3  FROM test ;

View created.

SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
  2  IS
  3  BEGIN
  4      --
  5      FOR cur IN ( SELECT id, nivel, salario, rank
  6                   FROM pr_test_view ) LOOP
  7          --
  8          dbms_output.put_line('ID: '||cur.id||
  9                               ' - NIVEL: '||cur.nivel||
 10                               ' - SALARIO: '||cur.salario||
 11                               ' - RANK: '||cur.rank) ;
 12          --
 13      END LOOP ;
 14      --
 15  END pr_test ;
 16  /

Procedure created.

SQL_8i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Utilizando Dynamic SQL...


SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
  2  IS
  3  --
  4  TYPE   mi_cursor IS REF CURSOR ;
  5  cur    mi_cursor ;
  6  --
  7  l_consulta  VARCHAR2(1000) ;
  8  l_id        test.id%TYPE ;
  9  l_nivel     test.nivel%TYPE ;
 10  l_salario   test.salario%TYPE ;
 11  l_rank      NUMBER ;
 12  --
 13  BEGIN
 14      --
 15      l_consulta := 'SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION
BY nivel ORDER BY salario DESC) AS rank FROM test' ;
 16      --
 17      OPEN cur FOR l_consulta ;
 18      --
 19      LOOP
 20          --
 21          FETCH cur INTO l_id, l_nivel, l_salario, l_rank ;
 22          --
 23          EXIT WHEN cur%NOTFOUND ;
 24          --
 25          dbms_output.put_line('ID: '||l_id||
 26                               ' - NIVEL: '||l_nivel||
 27                               ' - SALARIO: '||l_salario||
 28                               ' - RANK: '||l_rank) ;
 29          --
 30      END LOOP ;
 31      --
 32      CLOSE cur ;
 33      --
 34  END pr_test ;
 35  /

Procedure created.

SQL_8i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Ahora vamos a realizar el ejemplo anterior pero en una base de datos 9.2.0.8.0


SQL_9i> CREATE PROCEDURE pr_test
  2  IS
  3  BEGIN
  4      --
  5      FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
 nivel ORDER BY salario DESC) AS rank
  6               FROM test ) LOOP
  7          --
  8          dbms_output.put_line('ID: '||cur.id||
  9                               ' - NIVEL: '||cur.nivel||
 10                               ' - SALARIO: '||cur.salario||
 11                               ' - RANK: '||cur.rank) ;
 12          --
 13      END LOOP ;
 14      --
 15  END pr_test ;
 16  /

Procedure created.

SQL_9i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Realizamos el ejemplo anterior en una base de datos 10.1.0.2.0, veremos el mismo resultado que en 9i...


SQL_10g> CREATE PROCEDURE pr_test
  2  IS
  3  BEGIN
  4      --
  5      FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
 nivel ORDER BY salario DESC) AS rank
  6                   FROM test ) LOOP
  7          --
  8          dbms_output.put_line('ID: '||cur.id||
  9                               ' - NIVEL: '||cur.nivel||
 10                               ' - SALARIO: '||cur.salario||
 11                               ' - RANK: '||cur.rank) ;
 12          --
 13      END LOOP ;
 14      --
 15  END pr_test ;
 16  /

Procedure created.

SQL_10g> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Como pudimos observar, de la versión 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.

Problemas en Vistas Materializadas - Parte 2

2007-09-24T11:31:00.000-03:00

Como pudimos ver en el post "Problemas en Vistas Materializadas - Parte 1", podemos identificar con el paquete DBMS_MVIEW (procedimiento EXPLAIN_MVIEW), los problemas que pueden haber en nuestra Vista Materializada e implementar las soluciones necesarias. La contra de ese procedimiento es que nos muestra los errores que tenemos en la Vista Materializada, pero no nos dice cómo solucionarlos.

En Oracle 10g se introduce el paquete DBMS_ADVISOR (procedimiento TUNE_MVIEW) que nos dice qué cambios debemos implementar en nuestra Vista Materializada para soportar las capacidades que necesitamos.

Veamos un ejemplo:


SQL_10gR2> CREATE TABLE test1 AS
  2  SELECT level id, level*level total
  3  FROM dual
  4  CONNECT BY level <= 10 ;

Table created.

SQL_10gR2> CREATE TABLE test2 AS
  2  SELECT level id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 20 ;

Table created.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;

Table altered.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;

PL/SQL procedure successfully completed.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT test2.id,
  2         test2.nom,
  3         SUM(test1.total) sum_total
  4  FROM test1,
  5       test2
  6  WHERE test1.id = test2.id(+)
  7  GROUP BY test2.id,
  8           test2.nom
  9  ORDER BY id ;

        ID NOM         SUM_TOTAL
---------- ---------- ----------
         1 nom_1               1
         2 nom_2               4
         3 nom_3               9
         4 nom_4              16
         5 nom_5              25
         6 nom_6              36
         7 nom_7              49
         8 nom_8              64
         9 nom_9              81
        10 nom_10            100

10 rows selected.

Bien, ya creé mi ambiente de prueba. Ahora voy a crear las tablas de Log necesarias para la capacidad de REFRESH FAST. Pero las voy a crear de forma errónea para demostrar el poder que nos brinda el paquete DBMS_ADVISOR.


SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
  2  WITH ROWID, SEQUENCE (id) ;

Materialized view log created.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2
  2  WITH ROWID, SEQUENCE (id,nom) ;

Materialized view log created.

Procedemos a crear la Vista Materializada:


SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
  2  NOCOMPRESS
  3  LOGGING
  4  BUILD IMMEDIATE
  5  USING INDEX
  6  REFRESH FAST ON DEMAND
  7  USING DEFAULT LOCAL ROLLBACK SEGMENT
  8  DISABLE QUERY REWRITE
  9  AS
 10  SELECT test2.id,
 11         test2.nom,
 12         SUM(test1.total) sum_total,
 13         COUNT(*) cnt,
 14         COUNT(test1.total) cnt_sum
 15  FROM test1,
 16       test2
 17  WHERE test1.id = test2.id(+)
 18  GROUP BY test2.id,
 19           test2.nom
 20  ORDER BY id ;
FROM test1,
     *
ERROR at line 15:
ORA-32401: materialized view log on "CDW"."TEST2" does not have new values

Efectivamente como supusimos, tenemos errores en las tablas de Log que creamos.
Ahora vamos a ejecutar DBMS_ADVISOR.TUNE_MVIEW y ver los resultados en la tabla USER_TUNE_MVIEW...


SQL_10gR2> DECLARE
  2  l_nombre  VARCHAR2(100) := 'test_1' ;
  3  BEGIN
  4  DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
  5  NOCOMPRESS
  6  LOGGING
  7  BUILD IMMEDIATE
  8  USING INDEX
  9  REFRESH FAST ON DEMAND
 10  USING DEFAULT LOCAL ROLLBACK SEGMENT
 11  DISABLE QUERY REWRITE
 12  AS
 13  SELECT test2.id,
 14         test2.nom,
 15         SUM(test1.total) sum_total,
 16         COUNT(*) cnt,
 17         COUNT(test1.total) cnt_sum
 18  FROM test1,
 19       test2
 20  WHERE test1.id = test2.id(+)
 21  GROUP BY test2.id,
 22           test2.nom
 23  ORDER BY id') ;
 24  END ;
 25  /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT *
  2  FROM user_tune_mview
  3  WHERE task_name = 'test_1' ;

TASK_NAME        ACTION_ID SCRIPT_TYPE    STATEMENT
--------------- ---------- -------------- -----------------------------------
test_1                   1 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
                                          N "CDW"."TEST2" ADD ROWID, SEQUENCE
                                           ("ID","NOM")  INCLUDING NEW VALUES

test_1                   2 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
                                          N "CDW"."TEST1" ADD ROWID, SEQUENCE
                                           ("ID","TOTAL")  INCLUDING NEW VALU
                                          ES

test_1                   3 IMPLEMENTATION CREATE MATERIALIZED VIEW CDW.TEST_M
                                          V NOCOMPRESS
                                          LOGGING
                                          BUILD IMMEDIATE
                                          USING INDEX
                                           REFRESH FAST WITH ROWID DISABLE QU
                                          ERY REWRITE AS SELECT test2.id,
                                                 test2.nom,
                                                 SUM(test1.total) sum_total,
                                                 COUNT(*) cnt,
                                                 COUNT(test1.total) cnt_sum
                                          FROM test1,
                                               test2
                                          WHERE test1.id = test2.id(+)
                                          GROUP BY test2.id,
                                                   test2.nom
                                          ORDER BY id

test_1                   4 UNDO           DROP MATERIALIZED VIEW CDW.TEST_MV

4 rows selected.

La columna SCRIPT_TYPE nos muestra el tipo de recomendación. Deberíamos ver las columnas con el valor 'IMPLEMENTED' (la recomendación ya está implementada); pero en cambio, estamos viendo que aparecen como 'IMPLEMENTATION' (la recomendación NO está implementada).
El próximo paso es ejecutar los script recomendados...


SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST2"
  2  ADD ROWID, SEQUENCE("ID","NOM")  INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST1"
  2  ADD ROWID, SEQUENCE("ID","TOTAL")  INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
  2  NOCOMPRESS
  3  LOGGING
  4  BUILD IMMEDIATE
  5  USING INDEX
  6  REFRESH FAST ON DEMAND
  7  USING DEFAULT LOCAL ROLLBACK SEGMENT
  8  DISABLE QUERY REWRITE
  9  AS
 10  SELECT test2.id,
 11         test2.nom,
 12         SUM(test1.total) sum_total,
 13         COUNT(*) cnt,
 14         COUNT(test1.total) cnt_sum
 15  FROM test1,
 16       test2
 17  WHERE test1.id = test2.id(+)
 18  GROUP BY test2.id,
 19           test2.nom
 20  ORDER BY id ;

Materialized view created.

Ejecutando las recomendaciones de la tabla, pudimos solucionar rápidamente el problema que teníamos en la Vista Materializada.

Qué sucede si ejecutamos el DBMS_ADVISOR.TUNE_MVIEW nuevamente?


SQL_10gR2> DECLARE
  2  l_nombre  VARCHAR2(100) := 'test_1' ;
  3  BEGIN
  4  DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
  5  NOCOMPRESS
  6  LOGGING
  7  BUILD IMMEDIATE
  8  USING INDEX
  9  REFRESH FAST ON DEMAND
 10  USING DEFAULT LOCAL ROLLBACK SEGMENT
 11  DISABLE QUERY REWRITE
 12  AS
 13  SELECT test2.id,
 14         test2.nom,
 15         SUM(test1.total) sum_total,
 16         COUNT(*) cnt,
 17         COUNT(test1.total) cnt_sum
 18  FROM test1,
 19       test2
 20  WHERE test1.id = test2.id(+)
 21  GROUP BY test2.id,
 22           test2.nom
 23  ORDER BY id') ;
 24  END ;
 25  /
DECLARE
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1042
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 4

[TEST@TEST_10GR2 ~]$ oerr qsm 03116
03116, 00000, "The materialized view is already optimal and cannot be tuned any further"
// *Cause:   The materialized view has the capabilities that are specified in
//           the statement.
// *Action:  none

Oracle nos está diciendo que el script de creación de la Vista Materializada que nosotros ejecutamos, ya cuenta con todas las capacidades necesarias para su creación, y por lo tanto, ya se encuentra óptima y no se puede seguir tuneando.

Exchange Partition

2007-09-21T15:56:00.000-03:00

Exchange Partition permite cargar en tablas particionadas datos en forma rápida y con muy poco impacto para los usuarios que se encuentran activos.
En resumen, lo que hace la sentencia Exchange Partition es modificar el diccionario de datos y simular que los datos que ya tenemos cargados en una tabla, corresponden a una partición determinada de otra tabla.

Veamos un ejemplo muy sencillo para entender mejor éste tema:


SQL_10gR2> CREATE TABLE datos_1 AS
  2  SELECT level id, timestamp'2000-11-02 09:00:00' fecha
  3  FROM dual
  4  CONNECT BY level <= 100000 ;

Table created.

Elapsed: 00:00:01.06

SQL_10gR2> CREATE TABLE datos_2 AS
  2  SELECT level id, timestamp'2001-09-10 13:00:00' fecha
  3  FROM dual
  4  CONNECT BY level <= 100000 ;

Table created.

Lo que hicimos fue crear 2 tablas con distintas fechas en cada una de ellas.

Ahora creamos solamente la estructura de la tabla particionada en donde vamos a cargar los datos:


SQL_10gR2> CREATE TABLE test
  2  ( id, fecha )
  3  PARTITION BY RANGE ( fecha )
  4  (
  5      PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
  6      PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
  7  )
  8  AS
  9  SELECT 1, timestamp'2000-11-02 09:00:00'
 10  FROM dual
 11  WHERE 1 = 0 ;

Vamos a realizar un alter para modificar el diccionario de datos y relacionar cada una de las 2 tablas que creamos con la respectiva partición de la tabla TEST...


SQL_10gR2> ALTER TABLE test
  2  EXCHANGE PARTITION year_2000
  3  WITH table datos_1
  4  WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.03

SQL_10gR2> ALTER TABLE test
  2  EXCHANGE PARTITION year_2001
  3  WITH table datos_2
  4  WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.02

SQL_10gR2> SELECT count(*)
  2  FROM test ;

  COUNT(*)
----------
    200000

1 row selected.

SQL_10gR2> SELECT count(*)
  2  FROM datos_1 ;

  COUNT(*)
----------
         0

1 row selected.

SQL_10gR2> SELECT count(*)
  2  FROM datos_2 ;

  COUNT(*)
----------
         0

1 row selected.

Como podemos ver, con el Exchange Partition no tardamos casi nada en cargar los datos en la tabla particionada ya que en realidad no estamos cargando los datos, simplemente se modifica el diccionario de datos.

Pueden notar que agregué la sentencia WITHOUT VALIDATION. Que es ésto? WITHOUT VALIDATION suele ser una operación rápida porque sólo realiza modificaciones en el diccionario de datos. Si la tabla o tabla particionada que colocamos en el Exchange Partition tiene una primary key o unique constraint habilitado, entonces el Exchange Partition se realiza como WITH VALIDATION para mantener la integridad de las constraints.

Vamos a ejecutar nuevamente los 2 alter anteriores sin la sentencia WITHOUT VALIDATION...


SQL_10gR2> ALTER TABLE test
  2  EXCHANGE PARTITION year_2000
  3  WITH table datos_1 ;

Table altered.

Elapsed: 00:00:01.00

SQL_10gR2> ALTER TABLE test
  2  EXCHANGE PARTITION year_2001
  3  WITH table datos_2 ;

Table altered.

Elapsed: 00:00:01.05

Si hubiera ejecutado esos alter con un Trace, el reporte del Trace me mostraría, entre otras sentencias, las siguientes...


select 1
from
 "DATOS_1" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          0           0
Fetch        1      0.04       0.04          0         65          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0         66          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL DATOS_1 (cr=65 pr=0 pw=0 time=44582 us)


select 1
from
 "DATOS_2" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          0           0
Fetch        1      0.04       0.04          0         65          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0         66          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL DATOS_2 (cr=65 pr=0 pw=0 time=46957 us)

Antes que nada, notamos que los alter se ejecutaron en mayor tiempo, cierto? De las consultas que observamos del Trace, vemos que se está realizando un FULL SCAN de las tablas y que se está ejecutando una función en el WHERE de cada consulta. Imagínense si tenemos que realizar ésta clase de procesos en ambientes con gran volumen de datos y en donde el sistema se encuentra saturado por el I/O a disco.

Qué sucede si no tenemos los datos separados por año en distintas tablas, y en cambio, tenemos todos los datos en una misma tabla? Bueno, tomando como ejemplo la tabla TEST que acabamos de cargar, podríamos realizar lo siguiente...


SQL_10gR2> CREATE TABLE test_2
  2  ( id, fecha )
  3  PARTITION BY RANGE ( fecha )
  4  (
  5      PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
  6      PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
  7  )
  8  AS
  9  SELECT *
 10  FROM test ;

Table created.

Elapsed: 00:00:05.04

SQL_10gR2> DROP TABLE test ;

Table dropped.

SQL_10gR2> ALTER TABLE test_2 RENAME TO test ;

Table altered.

SQL_10gR2> SELECT count(*)
  2  FROM test ;

  COUNT(*)
----------
    200000

1 row selected.

Buscar valores NULL en forma eficiente

2007-09-20T10:56:00.000-03:00

Un problema que veo seguidamente, se relaciona con la escritura de consultas que buscan valores NULL en una tabla. Este es un problema muy común.
A continuación vamos a ver algunas soluciones que podemos implementar para buscar los valores NULL en forma eficiente y evitar lecturas innecesarias de bloques de datos.

Veamos algunos ejemplos:


SQL_9iR2> CREATE TABLE test AS
  2  SELECT decode(mod(rownum,100),0,null,level) id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

Veamos la cantidad de valores NULL que tiene la tabla que creamos:


SQL_9iR2> SELECT count(*)
  2  FROM test
  3  WHERE id IS NULL ;

  COUNT(*)
----------
     10000

1 row selected.

Veamos cómo se ejecuta internamente esa consulta:


SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     7 |   304 |
|   1 |  SORT AGGREGATE      |             |     1 |     7 |       |
|*  2 |   TABLE ACCESS FULL  | TEST        | 10000 | 70000 |   304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST"."ID" IS NULL)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.16       0.16       3144       3150          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.17       0.16       3144       3150          0           1

Observamos que obviamente está realizando un Full Scan. Qué sucede si creamos un índice B*Tree?


SQL_9iR2> CREATE INDEX test_id_idx ON test(id) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     7 |   304 |
|   1 |  SORT AGGREGATE      |             |     1 |     7 |       |
|*  2 |   TABLE ACCESS FULL  | TEST        | 10000 | 70000 |   304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST"."ID" IS NULL)

Porqué nuestra consulta no está accediendo a través del índice? por la simple razón de que los índices B*Tree no indexan los valores NULL. Como estamos realizando un COUNT buscando la cantidad de valores NULL de la tabla, el CBO sabe que si accede por índice, la consulta nos va a retornar un resultado erróneo ya que no existen valores NULL en el índice. Es por eso, que en vez de acceder por índice, realiza un Full Scan de la tabla. Esto tiene como consecuencia una lectura innecesaria de bloques por no haber accedido a través de un índice.

Una solución para éste problema sería crear un FBI (Function Based-Index) para indexar sólo los valores NULL de la columna:


SQL_9iR2> CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo')) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE NVL(id,'nulo') = 'nulo' ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     1 |     7 |    27 |
|   1 |  SORT AGGREGATE      |                   |     1 |     7 |       |
|*  2 |   INDEX RANGE SCAN   | TEST_ID_NULO_IDX  | 10001 | 70007 |    27 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NVL("TEST"."ID",'nulo')='nulo')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1         26          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          1         26          0           1

Veamos qué sucede si creamos un índice Bitmap:


SQL_9iR2> DROP INDEX test_id_idx ;

Index dropped.

SQL_9iR2> CREATE BITMAP INDEX test_id_bitmap ON test(id) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------------
| Id  | Operation                   |  Name            | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |     7 |    44 |
|   1 |  SORT AGGREGATE             |                  |     1 |     7 |       |
|   2 |   BITMAP CONVERSION COUNT   |                  |       |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE| TEST_ID_BITMAP   |       |       |       |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TEST"."ID" IS NULL)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          1          5          0           1

Observamos que también podemos utilizar índices Bitmap para buscar valores NULL ya que ésta clase de índices SI indexan éstos valores.
Lo importante a tener en cuenta si utilizamos éste índice es que cada modificación que se realiza sobre el índice, requiere un gran trabajo del sistema que si utilizamos índices B*tree. Por otro lado, si a ésto le sumamos las modificaciones concurrentes que se realizan sobre la columna indexada, puede llegar a ser mortal para el sistema.

Problemas en Vistas Materializadas - Parte 1

2007-09-19T10:19:00.000-03:00

Suele pasarnos que cuando queremos crear Vistas Materializadas, suelen haber errores y no logramos crearlas. O bien, logramos crearlas pero no suelen estar optimizadas para las capacidades que nosotros queremos. Para analizar y optimizar nuestras vistas materializadas, vamos a utilizar el paquete DBMS_MVIEW.EXPLAIN_MVIEW y la tabla MV_CAPABILITIES_TABLE.

Veamos un ejemplo:

Primero vamos a crear las tablas con los datos que vamos a utilizar para crear nuestra vista materializada:


SQL_10gR2> CREATE TABLE test1 AS
  2  SELECT level id, level*level total
  3  FROM dual
  4  CONNECT BY level <= 10 ;

Table created.

SQL_10gR2> CREATE TABLE test2 AS
  2  SELECT level id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 19 ;

Table created.

SQL_10gR2> INSERT INTO test2 VALUES(19,'nom_19') ;

1 row created.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;

PL/SQL procedure successfully completed.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;

PL/SQL procedure successfully completed.

Los datos quedan de la siguiente manera en las tablas:


SQL_10gR2> SELECT *
  2  FROM test1 ;

        ID      TOTAL
---------- ----------
         1          1
         2          4
         3          9
         4         16
         5         25
         6         36
         7         49
         8         64
         9         81
        10        100

10 rows selected.

SQL_10gR2> SELECT *
  2  FROM test2 ;

        ID NOM
---------- ----------------
         1 nom_1
         2 nom_2
         3 nom_3
         4 nom_4
         5 nom_5
         6 nom_6
         7 nom_7
         8 nom_8
         9 nom_9
        10 nom_10
        11 nom_11
        12 nom_12
        13 nom_13
        14 nom_14
        15 nom_15
        16 nom_16
        17 nom_17
        18 nom_18
        19 nom_19
        19 nom_19

20 rows selected.

Bien, supongamos que queremos crear una vista materializada con la opción de REFRESH FAST ON DEMAND sobre la siguiente consulta:


SQL_10gR2> SELECT test2.id,
  2         test2.nom,
  3         SUM(test1.total) sum_total
  4  FROM test1,
  5       test2
  6  WHERE test1.id = test2.id(+)
  7  GROUP BY test2.id,
  8           test2.nom
  9  ORDER BY id ;

        ID NOM         SUM_TOTAL
---------- ---------- ----------
         1 nom_1               1
         2 nom_2               4
         3 nom_3               9
         4 nom_4              16
         5 nom_5              25
         6 nom_6              36
         7 nom_7              49
         8 nom_8              64
         9 nom_9              81
        10 nom_10            100

10 rows selected.

Antes de crear la vista materializada, vamos a crear las tablas de Log para el refresh fast:


SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
  2  WITH ROWID, SEQUENCE (total)
  3  INCLUDING NEW VALUES ;

Materialized view log created.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2
  2  WITH ROWID, SEQUENCE (id,nom)
  3  INCLUDING NEW VALUES ;

Materialized view log created.

Ahora creamos la vista materializada:


SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
  2  NOCOMPRESS
  3  LOGGING
  4  BUILD IMMEDIATE
  5  USING INDEX
  6  REFRESH FAST ON DEMAND
  7  USING DEFAULT LOCAL ROLLBACK SEGMENT
  8  DISABLE QUERY REWRITE
  9  AS
 10  SELECT test2.id,
 11         test2.nom,
 12         SUM(test1.total) sum_total
 13  FROM test1,
 14       test2
 15  WHERE test1.id = test2.id(+)
 16  GROUP BY test2.id,
 17           test2.nom
 18  ORDER BY id ;
FROM test1,
     *
ERROR at line 13:
ORA-12015: cannot create a fast refresh materialized view from a complex query

Observamos que no podemos crear la vista materializada usando fast refresh sobre nuestra consulta. Pero cómo identificamos cuales son los errores por los cuales no podemos crear la vista materializada?
Lo que vamos a utilizar es el procedimiento EXPLAIN_MVIEW del paquete DBMS_MVIEW. Lo ejecutamos colocando como parámetro del procedimiento la consulta que vamos a utilizar en la vista materializada:


SQL_10gR2> BEGIN
  2
  3  DBMS_MVIEW.EXPLAIN_MVIEW
  4  (
  5  'SELECT test2.id,
  6         test2.nom,
  7         SUM(test1.total) sum_total
  8  FROM test1,
  9       test2
 10  WHERE test1.id = test2.id(+)
 11  GROUP BY test2.id,
 12           test2.nom
 13  ORDER BY id '
 14  ) ;
 15
 16  END ;
 17  /
BEGIN
*
ERROR at line 1:
ORA-30377: table SQL_10GR2.MV_CAPABILITIES_TABLE not found
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XRWMV", line 23
ORA-06512: at "SYS.DBMS_XRWMV", line 43
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3007

El error que estamos viendo se debe a que no tenemos que creada en nuestro esquema la tabla MV_CAPABILITIES_TABLE que se encuentra en $ORACLE_HOME/rdbms/admin (el script se llama utlxmv.sql) que utiliza el procedimiento para guardar la información resultante de la ejecución.

Creamos la tabla MV_CAPABILITIES_TABLE:


SQL_10gR2> @$ORACLE_HOME/rdbms/admin/utlxmv.sql

Table created.

Ejecutamos nuevamente el procedimiento:


SQL_10gR2> BEGIN
  2
  3  DBMS_MVIEW.EXPLAIN_MVIEW
  4  (
  5  'SELECT test2.id,
  6         test2.nom,
  7         SUM(test1.total) sum_total
  8  FROM test1,
  9       test2
 10  WHERE test1.id = test2.id(+)
 11  GROUP BY test2.id,
 12           test2.nom
 13  ORDER BY id '
 14  ) ;
 15
 16  END ;
 17  /

PL/SQL procedure successfully completed.

Ahora consultamos la tabla:


SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
  2  FROM mv_capabilities_table ;

CAPABILITY_NAME                P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REWRITE                        Y
PCT_TABLE                      N TEST1      relation is not a partitioned
                                            table

PCT_TABLE                      N TEST2      relation is not a partitioned
                                            table

REFRESH_FAST_AFTER_INSERT      N            outer join in mv
REFRESH_FAST_AFTER_ONETAB_DML  N            see the reason why REFRESH_FAS
                                            T_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N            see the reason why REFRESH_FAS
                                            T_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N            PCT is not possible on any of
                                            the detail tables in the mater
                                            ialized view

REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N            general rewrite is not possibl
                                            e or PCT is not possible on an
                                            y of the detail tables

PCT_TABLE_REWRITE              N TEST1      relation is not a partitioned
                                            table

PCT_TABLE_REWRITE              N TEST2      relation is not a partitioned
                                            table


16 rows selected.

Lo que nos muestra esta consulta es lo siguiente:
- Un listado de todas las capacidades de la vista materializada (columna CAPABILITY_NAME).
- Por cada capacidad, nos muestra si es posible o no (columna POSSIBLE con valores 'Y' o 'N').
- Si la capacidad no es posible, nos muestra porque no es posible (columna MSGTXT).

Como nosotros queremos crear la vista materializada con la opción de REFRESH FAST ON DEMAND, vemos la columna CAPABILITY_NAME y observamos los valores de la columna MSGTXT para lo que tiene que ver con REFRESH_FAST_%.
Podemos ver que en REFRESH_FAST_AFTER_INSERT vemos que dice "outer join in mv". Como nosotros tenemos un outer join en la vista materializada, no podemos realizar refresh fast porque existen ciertas restricciones que debemos cumplir y que en éste ejemplo no estamos cumpliendo. Una de esas condiciones dice que las columnas que contienen el outer join en la condición de join de la consulta, tienen que tener creadas constraints primary key o índices unique key.
En la TABLA2 de nuestro ejemplo, tenemos un valor duplicado (el ID 19). Veamos qué sucede si eliminamos uno de los ID 19 y creamos una primary key en la tabla TEST2 por el campo ID:


SQL_10gR2> DELETE FROM test2
  2  WHERE id = 19
  3    AND rownum = 1 ;

1 row deleted.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;

Table altered.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT *
  2  FROM test2 ;

        ID NOM
---------- ----------
         1 nom_1
         2 nom_2
         3 nom_3
         4 nom_4
         5 nom_5
         6 nom_6
         7 nom_7
         8 nom_8
         9 nom_9
        10 nom_10
        11 nom_11
        12 nom_12
        13 nom_13
        14 nom_14
        15 nom_15
        16 nom_16
        17 nom_17
        18 nom_18
        19 nom_19

19 rows selected.

Bien, ejecutamos nuevamente el procedimiento EXPLAIN_MVIEW:


SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;

Table truncated.

SQL_10gR2> BEGIN
  2
  3  DBMS_MVIEW.EXPLAIN_MVIEW
  4  (
  5  'SELECT test2.id,
  6         test2.nom,
  7         SUM(test1.total) sum_total
  8  FROM test1,
  9       test2
 10  WHERE test1.id = test2.id(+)
 11  GROUP BY test2.id,
 12           test2.nom
 13  ORDER BY id '
 14  ) ;
 15
 16  END ;
 17  /

PL/SQL procedure successfully completed.

Consultamos nuevamente la tabla:


SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
  2  FROM mv_capabilities_table ;

CAPABILITY_NAME                P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REWRITE                        Y
PCT_TABLE                      N TEST1      relation is not a partitioned
                                            table

PCT_TABLE                      N TEST2      relation is not a partitioned
                                            table

REFRESH_FAST_AFTER_INSERT      N SQL_10GR2. mv log does not have all neces
                                 TEST1      sary columns

REFRESH_FAST_AFTER_ONETAB_DML  N SUM_TOTAL  SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML  N            see the reason why REFRESH_FAS
                                            T_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ONETAB_DML  N            COUNT(*) is not present in the
                                             select list

REFRESH_FAST_AFTER_ONETAB_DML  N            SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML     N            see the reason why REFRESH_FAS
                                            T_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N            PCT is not possible on any of
                                            the detail tables in the mater
                                            ialized view

REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N            general rewrite is not possibl
                                            e or PCT is not possible on an
                                            y of the detail tables

PCT_TABLE_REWRITE              N TEST1      relation is not a partitioned
                                            table

PCT_TABLE_REWRITE              N TEST2      relation is not a partitioned
                                            table


19 rows selected.

Podemos observar que el error anterior ya no aparece más. En REFRESH_FAST_AFTER_ONETAB_DML vemos el mensaje "COUNT(*) is not present in the select list". Esto nos está diciendo que debemos agregar a la consulta un COUNT(*).
Vamos a agregar el COUNT(*) en la consulta y realizar el mismo procedimiento de antes:


SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;

Table truncated.

SQL_10gR2> BEGIN
  2
  3  DBMS_MVIEW.EXPLAIN_MVIEW
  4  (
  5  'SELECT test2.id,
  6         test2.nom,
  7         SUM(test1.total) sum_total,
  8         COUNT(*) cnt
  9  FROM test1,
 10       test2
 11  WHERE test1.id = test2.id(+)
 12  GROUP BY test2.id,
 13           test2.nom
 14  ORDER BY id '
 15  ) ;
 16
 17  END ;
 18  /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
  2  FROM mv_capabilities_table ;

CAPABILITY_NAME                P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REWRITE                        Y
PCT_TABLE                      N TEST1      relation is not a partitioned
                                            table

PCT_TABLE                      N TEST2      relation is not a partitioned
                                            table

REFRESH_FAST_AFTER_INSERT      N SQL_10GR2. mv log does not have all neces
                                 TEST1      sary columns

REFRESH_FAST_AFTER_ONETAB_DML  N SUM_TOTAL  SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML  N            see the reason why REFRESH_FAS
                                            T_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ONETAB_DML  N            SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML     N            see the reason why REFRESH_FAS
                                            T_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N            PCT is not possible on any of
                                            the detail tables in the mater
                                            ialized view

REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N            general rewrite is not possibl
                                            e or PCT is not possible on an
                                            y of the detail tables

PCT_TABLE_REWRITE              N TEST1      relation is not a partitioned
                                            table

PCT_TABLE_REWRITE              N TEST2      relation is not a partitioned
                                            table


18 rows selected.

Podemos observar que el error anterior ya no aparece más. Ahora en REFRESH_FAST_AFTER_ONETAB_DML vemos "SUM(expr) without COUNT(expr)". Como estamos utilizando la función SUM, debemos agregar un COUNT(expr) a la consulta:


SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;

Table truncated.

SQL_10gR2> BEGIN
  2
  3  DBMS_MVIEW.EXPLAIN_MVIEW
  4  (
  5  'SELECT test2.id,
  6         test2.nom,
  7         SUM(test1.total) sum_total,
  8         COUNT(*) cnt,
  9         COUNT(test1.total) cnt_sum
 10  FROM test1,
 11       test2
 12  WHERE test1.id = test2.id(+)
 13  GROUP BY test2.id,
 14           test2.nom
 15  ORDER BY id'
 16  ) ;
 17
 18  END ;
 19  /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
  2  FROM mv_capabilities_table ;

CAPABILITY_NAME                P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REWRITE                        Y
PCT_TABLE                      N TEST1      relation is not a partitioned
                                            table

PCT_TABLE                      N TEST2      relation is not a partitioned
                                            table

REFRESH_FAST_AFTER_INSERT      N SQL_10GR2. mv log does not have all neces
                                 TEST1      sary columns

REFRESH_FAST_AFTER_ONETAB_DML  N            see the reason why REFRESH_FAS
                                            T_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N            see the reason why REFRESH_FAS
                                            T_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N            PCT is not possible on any of
                                            the detail tables in the mater
                                            ialized view

REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N            general rewrite is not possibl
                                            e or PCT is not possible on an
                                            y of the detail tables

PCT_TABLE_REWRITE              N TEST1      relation is not a partitioned
                                            table

PCT_TABLE_REWRITE              N TEST2      relation is not a partitioned
                                            table


16 rows selected.

Podemos observar que el error anterior ya no aparece más. El último error que nos falta solucionar para poder crear la vista materializada con refresh fast es el que aparece en REFRESH_FAST_AFTER_INSERT con el mensaje "mv log does not have all necessary columns". Al parecer creamos de forma incorrecta la tabla de Logs de la tabla TEST1 y nos está diciendo que tenemos que agregar a la tabla de Log todas las columnas que aparecen en nuestra consulta.
Lo que vamos a hacer es dropear la tabla de Log que habíamos creado y recrearla agregando una columna:


SQL_10gR2> DROP MATERIALIZED VIEW LOG on test1 ;

Materialized view log dropped.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
  2  WITH ROWID, SEQUENCE (id,total)
  3  INCLUDING NEW VALUES ;

Materialized view log created.

SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;

Table truncated.

SQL_10gR2> BEGIN
  2
  3  DBMS_MVIEW.EXPLAIN_MVIEW
  4  (
  5  'SELECT test2.id,
  6         test2.nom,
  7         SUM(test1.total) sum_total,
  8         COUNT(*) cnt,
  9         COUNT(test1.total) cnt_sum
 10  FROM test1,
 11       test2
 12  WHERE test1.id = test2.id(+)
 13  GROUP BY test2.id,
 14           test2.nom
 15  ORDER BY id'
 16  ) ;
 17
 18  END ;
 19  /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
  2  FROM mv_capabilities_table ;

CAPABILITY_NAME                P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REWRITE                        Y
PCT_TABLE                      N TEST1      relation is not a partitioned
                                            table

PCT_TABLE                      N TEST2      relation is not a partitioned
                                            table

REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_AFTER_ANY_DML     Y
REFRESH_FAST_PCT               N            PCT is not possible on any of
                                            the detail tables in the mater
                                            ialized view

REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N            general rewrite is not possibl
                                            e or PCT is not possible on an
                                            y of the detail tables

PCT_TABLE_REWRITE              N TEST1      relation is not a partitioned
                                            table

PCT_TABLE_REWRITE              N TEST2      relation is not a partitioned
                                            table


16 rows selected.

Buenísimo! Al parecer ya estamos en condiciones de crear la vista materializada utilizando la opción de REFRESH FAST ON DEMAND. Veamos...


ihubowner@ihub1t> CREATE MATERIALIZED VIEW TEST_MV
  2  NOCOMPRESS
  3  LOGGING
  4  BUILD IMMEDIATE
  5  USING INDEX
  6  REFRESH FAST ON DEMAND
  7  USING DEFAULT LOCAL ROLLBACK SEGMENT
  8  DISABLE QUERY REWRITE
  9  AS
 10  SELECT test2.id,
 11         test2.nom,
 12         SUM(test1.total) sum_total,
 13         COUNT(*) cnt,
 14         COUNT(test1.total) cnt_sum
 15  FROM test1,
 16       test2
 17  WHERE test1.id = test2.id(+)
 18  GROUP BY test2.id,
 19           test2.nom
 20  ORDER BY id ;

Materialized view created.

Oracle DBA Toolbar

2007-09-17T17:13:00.000-03:00

Pueden ingresar AQUI e instalar la "Oracle DBA Toolbar".

Disponible para Internet Explorer y Mozilla Firefox.

Estadísticas en tablas temporales

2007-09-17T10:58:00.000-03:00

Lo ideal es tener estadísticas "representativas" en las tablas temporales. Suele suceder que muchas veces no sabemos la cantidad de registros que se van a cargar en las tablas temporales, pero podemos tener estadísticas que representen la cantidad de registros que en general suelen cargarse en las tablas. Por otro lado, hay ocasiones en que no sabemos ni eso. No sabemos que cantidad de registros promedio se van a cargar en las tablas. Cuando sucede ésto podemos pensar en algunas alternativas.

Por default, Oracle asume que las tablas temporales van a contener X cantidad de registros (donde X representa a 8,168 de registros en las bases de datos que contiene bloques de 8K).
Como éste valor por default suele ser incierto en las ejecuciones de nuestros procesos utilizando tablas temporales, tenemos que ayudar al CBO en recolectar estadísticas reales de las tablas temporales.

Antes que nada, veamos las estadísticas por default que utiliza el CBO:


SQL_9iR2> SHOW PARAMETER DB_BLOCK_SIZE

db_block_size          integer          8192

SQL_9iR2> CREATE GLOBAL TEMPORARY TABLE test_temp ( id NUMBER ) ;

Table created.

SQL_9iR2> EXPLAIN PLAN FOR
  2  SELECT /*+ ALL_ROWS */ id
  3  FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  8168 |   103K|    11 |
|   1 |  TABLE ACCESS FULL   | TEST_TEMP   |  8168 |   103K|    11 |
--------------------------------------------------------------------

Observamos lo que explicamos anteriormente. El CBO estima las estadísticas en base al parámetro DB_BLOCK_SIZE seteado en la base de datos.

Las 3 soluciones disponibles en lo que concierne a las estadísticas en las tablas temporales son:
1.- Usar el hint DYNAMIC_SAMPLING
2.- Usar el hint CARDINALITY
3.- Usar DBMS_STATS.SET_TABLE_STATS

Veamos cada una de éstas soluciones:

1.- Usar el hint DYNAMIC_SAMPLING

Dynamic Sampling le dice a Oracle que realice rápidamente un "escaneo completo" de la tabla para obtener estadísticas que representen la realidad.


SQL_9iR2> EXPLAIN PLAN FOR
  2  SELECT /*+ DYNAMIC_SAMPLING(2) */ id
  3  FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    13 |    11 |
|   1 |  TABLE ACCESS FULL   | TEST_TEMP   |     1 |    13 |    11 |
--------------------------------------------------------------------

Utilizando Dynamic Sampling (nivel 2), podemos ver que las estadísticas son más representativas de la realidad.

Veamos qué sucede si insertamos 15,000 registros a la tabla temporal:


SQL_9iR2> INSERT INTO test_temp
  2  SELECT level
  3  FROM dual
  4  CONNECT BY level <= 15000 ;

15000 rows created.

SQL_9iR2> EXPLAIN PLAN FOR
  2  SELECT /*+ DYNAMIC_SAMPLING(2) */ id
  3  FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 15000 |   190K|    11 |
|   1 |  TABLE ACCESS FULL   | TEST_TEMP   | 15000 |   190K|    11 |
--------------------------------------------------------------------

En 10g, el CBO obtiene por default, estadísticas utilizando Dynamic Sampling (nivel 2) sobre las tablas temporales.


SQL_10gR2> CREATE GLOBAL TEMPORARY TABLE test_temp ( id NUMBER ) ;

Table created.

SQL_10gR2> EXPLAIN PLAN FOR
  2  SELECT id
  3  FROM test_temp ;

Explained.

SQL_10gR2> @explains
Plan hash value: 1559088631

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST_TEMP |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

2.- Usar el hint CARDINALITY

Otra solución es utilizar el hint CARDINALITY, que le dice a Oracle la cantidad de registros que tiene la tabla. Pero éste valor debemos colocarlos nosotros. No se obtiene dinámicamente.


SQL_9iR2> EXPLAIN PLAN FOR
  2  SELECT /*+ CARDINALITY(test_temp 10000) */ id
  3  FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 10000 |   126K|    11 |
|   1 |  TABLE ACCESS FULL   | TEST_TEMP   | 10000 |   126K|    11 |
--------------------------------------------------------------------

3.- Usar DBMS_STATS.SET_TABLE_STATS

Por último, podemos utilizar el paquete DBMS_STATS, para cargar estadísticas representativas de la tabla temporal. El procedimiento SET_TABLE_STATS no analiza la tabla en cuestión, sino que setea las estadísticas de la tabla como nosotros queramos.
Podemos realizar ésto cuando creamos la tabla o luego de cargar datos en la tabla.
Si sabemos la cantidad de registros que puede llegar a tener la tabla temporal, podemos ejecutar éste procedimiento y setear las estadísticas a un valor representativo en base a los registros de la tabla.
Algo muy importante a tener en cuenta es que la ejecución de éste procedimiento implica un COMMIT implícito; por lo que tenemos que tener cuidado en el momento en el cual lo ejecutemos. Podrían ejecutarlo dentro de un procedimiento utilizando AUTONOMOUS_TRANSACTION para que no cree ningún tipo de conflictos.


SQL_9iR2> EXEC dbms_stats.set_table_stats( USER, 'TEST_TEMP', numrows => 10000 ) ;

PL/SQL procedure successfully completed.

SQL_9iR2> EXPLAIN PLAN FOR
  2  SELECT id
  3  FROM test_temp ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 10000 |   126K|    11 |
|   1 |  TABLE ACCESS FULL   | TEST_TEMP   | 10000 |   126K|    11 |
--------------------------------------------------------------------

Las columnas más selectivas deberían ir al comienzo del índice?

2007-09-14T21:01:00.000-03:00

El mito dice lo siguiente: Cuando creamos un índice, las columnas más selectivas (las columnas con la mayor cantidad de valores distintos) deberían ir al comienzo del índice.
Nuestro sentido común nos dice que es lógico crear los índices de esa forma; pero lo que no nos dice nuestro sentido común, es si en realidad éste mito es verdadero o falso.

La mejor manera de comprobar la veracidad de éste mito, es realizando un ejemplo:


SQL_9iR2> CREATE TABLE test
  2  (nombre, edad, sexo)
  3  AS
  4  SELECT 'nom_'||level,
  5         to_number(round(dbms_random.value(1,5))||round(dbms_random.value(1,5))),
  6         decode(mod(level,2),1,'M','F')
  7  FROM dual
  8  CONNECT BY level <= 10000 ;

Table created.

Veamos la cantidad de valores distintos de cada columna:


SQL_9iR2> SELECT COUNT(DISTINCT nombre) nombre,
  2              COUNT(DISTINCT edad) edad,
  3              COUNT(DISTINCT sexo) sexo
  4  FROM test ;

    NOMBRE       EDAD       SEXO
---------- ---------- ----------
     10000         25          2

1 row selected.

Con los valores que estamos viendo, nuestro sentido común nos diría que si tenemos que crear un índice sobre esas 3 columnas, las coloquemos en orden de selectividad (de las columnas con mayor cantidad de valores distintos a las de menor cantidad).


SQL_9iR2> CREATE INDEX test_selectivo_idx ON test(nombre,edad,sexo) ;

Index created.

El índice "test_selectivo_idx" que acabamos de crear es el que la gente está más acostumbrada a crear. Colocando las columnas con mayor cantidad de valores distintos en la cabecera del índice hasta llegar a la columna con menor cantidad de valores distintos.
Pero no sólo vamos a crear ese índice. También vamos a crear otro índice invirtiendo el orden de las columnas del índice anterior:


SQL_9iR2> CREATE INDEX test_no_selectivo_idx ON test(sexo,edad,nombre) ;

Index created.

Ahora vamos a analizar la estructura de los 2 índices para ver si el espacio que ocupa cada uno es el mismo:


SQL_9iR2> ANALYZE INDEX test_selectivo_idx VALIDATE STRUCTURE ;

Index analyzed.

SQL_9iR2> exec print_table('SELECT * FROM index_stats') ;
-----------------
HEIGHT                        : 2
BLOCKS                        : 40
NAME                          : TEST_SELECTIVO_IDX
PARTITION_NAME                :
LF_ROWS                       : 10000
LF_BLKS                       : 35
LF_ROWS_LEN                   : 248894
LF_BLK_LEN                    : 7996
BR_ROWS                       : 34
BR_BLKS                       : 1
BR_ROWS_LEN                   : 543
BR_BLK_LEN                    : 8028
DEL_LF_ROWS                   : 0
DEL_LF_ROWS_LEN               : 0
DISTINCT_KEYS                 : 10000
MOST_REPEATED_KEY             : 1
BTREE_SPACE                   : 287888
USED_SPACE                    : 249437
PCT_USED                      : 87
ROWS_PER_KEY                  : 1
BLKS_GETS_PER_ACCESS          : 3
PRE_ROWS                      : 0
PRE_ROWS_LEN                  : 0
OPT_CMPR_COUNT                : 0
OPT_CMPR_PCTSAVE              : 0
-----------------

PL/SQL procedure successfully completed.

SQL_9iR2> ANALYZE INDEX test_no_selectivo_idx VALIDATE STRUCTURE ;

Index analyzed.

SQL_9iR2> exec print_table('SELECT * FROM index_stats') ;
-----------------
HEIGHT                        : 2
BLOCKS                        : 40
NAME                          : TEST_NO_SELECTIVO_IDX
PARTITION_NAME                :
LF_ROWS                       : 10000
LF_BLKS                       : 35
LF_ROWS_LEN                   : 248894
LF_BLK_LEN                    : 7996
BR_ROWS                       : 34
BR_BLKS                       : 1
BR_ROWS_LEN                   : 673
BR_BLK_LEN                    : 8028
DEL_LF_ROWS                   : 0
DEL_LF_ROWS_LEN               : 0
DISTINCT_KEYS                 : 10000
MOST_REPEATED_KEY             : 1
BTREE_SPACE                   : 287888
USED_SPACE                    : 249567
PCT_USED                      : 87
ROWS_PER_KEY                  : 1
BLKS_GETS_PER_ACCESS          : 3
PRE_ROWS                      : 0
PRE_ROWS_LEN                  : 0
OPT_CMPR_COUNT                : 2
OPT_CMPR_PCTSAVE              : 19
-----------------

PL/SQL procedure successfully completed.

Vemos que los índices usan la misma cantidad de espacio (hay alguna diferencia mínima en bytes, pero en general no hay diferencia en cuanto al espacio que ocupan). Por otro lado, el orden en el cual se encuentran las columnas en un índice, pueden ser o no, mejor candidatos para usar "Index Key Compression" (observando el valor OPT_CMPR_PCTSAVE de la vista index_stats).

Lo que vamos a realizar ahora es un TKPROF ejecutando las 2 consultas dentro de un bloque PL/SQL para ver la diferencia en cuanto a la performance de utilizar cada uno de los índices:


SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade => true) ;

PL/SQL procedure successfully completed.


SQL_9iR2> ALTER SESSION SET SQL_TRACE = TRUE ;

Session altered.

SQL_9iR2> DECLARE
  2  l_count  PLS_INTEGER ;
  3  BEGIN
  4      FOR i IN ( SELECT * FROM test ) LOOP
  5
  6          SELECT /*+ INDEX(test  test_selectivo_idx) */ COUNT(*)
  7          INTO l_count
  8          FROM test
  9          WHERE nombre = i.nombre
 10            AND edad = i.edad
 11            AND sexo = i.sexo ;
 12
 13          SELECT /*+ INDEX(test  test_no_selectivo_idx) */ COUNT(*)
 14          INTO l_count
 15          FROM test
 16          WHERE nombre = i.nombre
 17            AND edad = i.edad
 18            AND sexo = i.sexo ;
 19
 20      END LOOP ;
 21  END ;
 22  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.08

SQL_9iR2> ALTER SESSION SET SQL_TRACE = FALSE ;

Session altered.

Veamos el reporte del TKPROF que generamos:


SELECT /*+ INDEX(test  test_selectivo_idx) */ COUNT(*)
FROM
 TEST WHERE NOMBRE = :B3 AND EDAD = :B2 AND SEXO = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.17       0.12          0          0          0           0
Fetch    10000      0.13       0.09          0      20034          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      0.30       0.22          0      20034          0       10000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 137     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  SORT AGGREGATE
  10000   INDEX RANGE SCAN TEST_SELECTIVO_IDX (object id 82961)

SELECT /*+ INDEX(test  test_no_selectivo_idx) */ COUNT(*)
FROM
 TEST WHERE NOMBRE = :B3 AND EDAD = :B2 AND SEXO = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.19       0.12          0          0          0           0
Fetch    10000      0.12       0.09          0      20034          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      0.31       0.21          0      20034          0       10000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 137     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  SORT AGGREGATE
  10000   INDEX RANGE SCAN TEST_NO_SELECTIVO_IDX (object id 82962)

Como podemos ver en el reporte, leen exactamente la misma cantidad de bloques de datos y se ejecutan aproximadamente en el mismo tiempo de CPU y elapsed. En conclusión, los 2 índices son exactamente iguales.

Este ejemplo nos sirve como prueba de que el mito no es cierto. No hay diferencias en cuanto a performance en colocar las columnas menos selectivas en la cabecera del índice o las más selectivas.

La realidad es que tenemos que decidir en qué orden colocar las columnas en el índice en base a cómo vamos a acceder a esas columnas en nuestras consultas. Si tenemos el índice compuesto por (sexo,edad,nombre) y accedemos sólo por la columna sexo, no tiene sentido crear un índice compuesto B*Tree y quizás nos convendría crear un índice Bitmap sólo por la columna sexo.

Por ejemplo, si tenemos estas 2 consultas ...


SELECT d FROM test WHERE a = :a AND b = :b ;

SELECT d FROM test WHERE b = :b ;

... lo más razonable es crear un índice en el siguiente orden: (b,a). De ésta manera, ese índice puede ser usado en las 2 consultas.

Diferencias entre COUNT(1) y COUNT(*) - Parte 1

2007-09-14T17:51:00.001-03:00

Miles y miles y miles de veces veo que se arman consultas SQL que contiene el COUNT(1). Este es un viejo mito. Muchos piensan que colocar COUNT(1) en vez del COUNT(*) mejora la performance de la consulta.... pero en realidad mejora la performance?

Veamos...


SQL_9iR2> CREATE TABLE test AS
 2  SELECT level id, 'texto_'||level texto
 3  FROM dual
 4  CONNECT BY level <= 100000 ;  Table created.  SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

Bien, ahora ejecutemos las 2 consultas:


SQL_9iR2> ALTER SESSION SET SQL_TRACE = TRUE ;

Session altered.

SQL_9iR2> SELECT COUNT(1)
 2  FROM test ;

 COUNT(1)
----------
   100000

1 row selected.

SQL_9iR2> SELECT COUNT(*)
 2  FROM test ;

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

1 row selected.

SQL_9iR2> ALTER SESSION SET SQL_TRACE = FALSE ;

Session altered.

Veamos lo que nos muestra el TKPROF:


select count(1)
from
test

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

select count(*)
from
test

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

Observamos que en las 2 consultas tenemos la misma cantidad de lecturas de bloques (disk, query, current) y el mismo tiempo de elapsed y cpu.
Las 2 consultas son idénticas y no hay un incremento en la performance por utilizar el COUNT(1) en vez del COUNT(*).

Incluso podría poner cualquier cosa en el COUNT... obtendríamos los mismos resultados:


SELECT COUNT(2222222)
FROM
TEST

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

SELECT COUNT('EJEMPLO')
FROM
TEST

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

SELECT COUNT(12345)
FROM
TEST

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

Bien, por último veamos los siguiente. Qué sucede si realizo un COUNT de alguna de las columnas de la tabla? Veamos...


SQL_9iR2> SELECT COUNT(texto)
 2  FROM test ;

COUNT(TEXTO)
------------
     100000

1 row selected.

La consulta nos devolvió la cantidad de registros totales de la tabla. Ahora veamos qué me devuelve la consulta si la columna TEXTO tiene valores nulos.


SQL_9iR2> UPDATE test
 2  SET texto = NULL
 3  WHERE id > 50000 ;

50000 rows updated.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT COUNT(texto)
 2  FROM test ;

COUNT(TEXTO)
------------
      50000

1 row selected.

Como podemos observar, el COUNT sobre una columna sólo cuenta la cantidad de valores que no son nulos. Por lo tanto, tenemos que tener cuidado a la hora de realizar un COUNT y tener en cuenta estos pequeños detalles.

BULK COLLECT con miles de registros y la cláusula LIMIT

2007-09-14T11:06:00.000-03:00

Veo muchísimas veces, que cuando se utiliza BULK COLLECT en los códigos PL/SQL, no se coloca la cláusula LIMIT. Cuando no utilizamos ésta cláusula, lo único que ganamos es arruinar la memoria del proceso.
La cláusula LIMIT nos permite definir la cantidad de 'datos' que vamos a colocar en memoria. Cuando utilizamos LIMIT, lo ideal es definirlo en un valor entre 100 a 500. Personalmente, elijo el valor 100 porque en base a mi experiencia suele ser el mejor valor. Pero porque elijo un valor tan chico y no 1000 o 5000 por ejemplo? Bueno, por la simple razón que manejar gran cantidad de datos en memoria es más costoso que manejar poca cantidad.
Si elegimos un valor alto en el LIMIT, puede darse 3 casos:
- Que nuestro código se ejecute más rápidamente (improbable).
- Que nuestro código se ejecute en igual tiempo (improbable).
- Que nuestro código se ejecute más lentamente (probable).

Veamos un ejemplo para entender mejor las consecuencia de no utilizar la cláusula LIMIT.

Primero creamos una tabla TEST con 10.000 registros (para ver la diferencia del uso de la cláusula LIMIT, no hace falta realizar el ejemplo con millones de registros. Con unos miles de registros nos alcanza para entender el tema) y una tabla TEST_2 con la estructura de la tabla TEST pero sin registros:


SQL_9iR2> CREATE TABLE test AS
  2  SELECT level id , 'oracle_'||level texto
  3  FROM dual
  4  CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE TABLE test_2 AS
  2  SELECT *
  3  FROM test
  4  WHERE 1 = 2 ;

Table created.

Antes de ejecutar el primer código, voy a liberar la memoria que ya no se está utilizando en mi sesión actual para poder ver claramente la diferencia de las estadísticas tomadas en cada ejecución.


SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ejecutamos un código PL/SQL con Bulk Collect pero SIN la cláusula LIMIT:


SQL_9iR2> DECLARE
  2  TYPE t_array_number   IS TABLE OF NUMBER ;
  3  TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
  4  t_array_id     t_array_number ;
  5  t_array_texto  t_array_varchar2 ;
  6  CURSOR cur IS
  7      SELECT * FROM test ;
  8  BEGIN
  9      OPEN cur ;
 10      LOOP
 11
 12      FETCH cur BULK COLLECT INTO t_array_id , t_array_texto ;
 13
 14      FORALL i IN 1 .. t_array_id.COUNT
 15          INSERT INTO test_2
 16          VALUES (t_array_id(i) , t_array_texto(i)) ;
 17
 18      EXIT WHEN cur%NOTFOUND ;
 19
 20      END LOOP ;
 21      COMMIT ;
 22      CLOSE cur ;
 23  END ;
 24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Antes de ejecutar el segundo código, voy a liberar nuevamente la memoria que ya no se está utilizando en mi sesión actual.


SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ahora ejecutamos el mismo código PL/SQL con Bulk Collect pero CON la cláusula LIMIT:


SQL_9iR2> DECLARE
  2  TYPE t_array_number   IS TABLE OF NUMBER ;
  3  TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
  4  t_array_id     t_array_number ;
  5  t_array_texto  t_array_varchar2 ;
  6  CURSOR cur IS
  7      SELECT * FROM test ;
  8  BEGIN
  9      OPEN cur ;
 10      LOOP
 11
 12      FETCH cur BULK COLLECT INTO t_array_id , t_array_texto LIMIT 100 ;
 13
 14      FORALL i IN 1 .. t_array_id.COUNT
 15          INSERT INTO test_2
 16          VALUES (t_array_id(i) , t_array_texto(i)) ;
 17
 18      EXIT WHEN cur%NOTFOUND ;
 19
 20      END LOOP ;
 21      COMMIT ;
 22      CLOSE cur ;
 23  END ;
 24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Podemos ver que en la segunda ejecución, estoy cargando en memoria 100 registros en cada Fetch que realizo. A diferencia de la primer ejecución, que carga todos los datos de una vez.

Veamos las estadísticas obtenidas de las 2 ejecuciones anteriores:


Nombre                         Ejecución_1 Ejecución_2  Diferencia
------------------------------ ----------- ----------- -----------
LATCH.kwqit: protect wakeup ti           1           0          -1
LATCH.simulator lru latch                1           0          -1
LATCH.spilled msgs queues list           1           0          -1
LATCH.transaction allocation             3           0          -3
LATCH.session timer                      5           0          -5
LATCH.multiblock read objects            8           0          -8
LATCH.channel operations paren          11           0         -11
LATCH.child cursor hash table           20           8         -12
LATCH.Consistent RBA                    56           6         -50
LATCH.lgwr LWN SCN                      56           6         -50
LATCH.mostly latch-free SCN             56           6         -50
LATCH.active checkpoint queue           63           7         -56
LATCH.session idle bit                 183          45        -138
LATCH.enqueues                         219          49        -170
LATCH.redo writing                     241          25        -216
LATCH.SQL memory manager worka         337           0        -337
LATCH.messages                         427          42        -385
LATCH.simulator hash latch             844           4        -840
LATCH.dml lock allocation            1,428         154      -1,274
LATCH.shared pool                    1,586         271      -1,315
LATCH.row cache enqueue latch        1,648         194      -1,454
LATCH.cache buffers lru chain        1,521           5      -1,516
LATCH.library cache pin alloca       2,900         362      -2,538
LATCH.row cache objects              4,438         502      -3,936
LATCH.enqueue hash chains            4,841         508      -4,333
LATCH.checkpoint queue latch         6,296         521      -5,775
LATCH.session allocation            19,333       1,893     -17,440
LATCH.undo global data              30,208       2,945     -27,263
LATCH.redo allocation               30,716       3,231     -27,485
LATCH.sequence cache                42,506       4,124     -38,382
LATCH.library cache pin             60,580       6,241     -54,339
LATCH.library cache                 76,555       7,911     -68,644

LATCHES:

 Ejecución_1   Ejecución_2   Diferencia   Porcentaje
     670,187        67,521     -602,666      992.56%

Una de las cosas que me interesa mostrarles acerca de éstas ejecuciones son los LATCHES (loqueos). Si bien la ejecución de los 2 códigos PL/SQL demoraron exactamente lo mismo en ejecutarse (en éste ejemplo procesando solamente 10.000 registros), las estadísticas nos muestran que estamos empleados muchísimos más loqueos en la primer ejecución que en la segunda. Pero porqué sucede ésto? Recordemos que en la segunda ejecución, lo único que modificamos en el código fue el agregado de la cláusula LIMIT. Bien, como dijimos en el comienzo, manejar gran cantidad de memoria es más costoso que manejar poca cantidad, por lo que Oracle tiene que emplear mayor cantidad de loqueos para manejar los 10.000 registros que subimos a memoria en la primer ejecución, que manejar solamente 100 registros en la segunda ejecución.
Este ejemplo lo realizamos con un sólo usuario concurrente.... pero imagínese qué sucedería si tenemos muchos usuarios concurrentes realizando lo mismo que nosotros... y por lo tanto, generando gran cantidad de loqueos....

NOTA: Tenemos que evitar los loqueos a toda costa ya que los loqueos afectan la performance del sistema. Mientras mayor sea la cantidad de loqueos, nuestro sistema se vuelve cada vez menos escalable; y como consecuencia, cada vez soporta menor cantidad de usuarios concurrentes.

Modificar millones de registros de una tabla

2007-09-13T12:45:00.000-03:00

Muchas veces me hacen la siguiente pregunta: ¿ Cómo puedo modificar de manera eficiente los datos de una tabla que contiene millones de registros sin tener impacto en la performance del sistema ?

Suele ocurrir, que la solución que se utiliza es realizar un simple UPDATE. Si hablamos de performance, la ejecución de ésta sentencia puede tener un gran impacto en la base de datos. Porqué? porque principalmente, generamos muchísimos bytes de redo y undo.

Si tengo que modificar millones de datos de una tabla, optaría por NO modificarlos.
Implementaría la siguiente estrategia.

Veamos un ejemplo:

Supongamos que tenemos una tabla llamada TEST que contiene 5 millones de registros, una primary key y un índice único:


SQL_9iR2> CREATE TABLE test NOLOGGING PARALLEL 4 AS
  2  SELECT level id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 5000000 ;

Table created.

Elapsed: 00:00:16.06

SQL_9iR2> ALTER TABLE test ADD CONSTRAINT test_id_pk PRIMARY KEY (id) NOLOGGING PARALLEL 4 ;

Table altered.

Elapsed: 00:00:14.03

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq ON test(id,nom) NOLOGGING PARALLEL 4 ;

Index created.

SQL_9iR2> SELECT *
  2  FROM test
  3  WHERE rownum <= 10 ;

        ID NOM
---------- --------------
     37011 nom_37011
     37012 nom_37012
     37013 nom_37013
     37014 nom_37014
     37015 nom_37015
     37016 nom_37016
     37017 nom_37017
     37018 nom_37018
     37019 nom_37019
     37020 nom_37020

10 rows selected.

Si nosotros quisiéramos modificar los datos de la columna NOM, no vamos a utilizar la cláusula UPDATE, sino que vamos a realizar los siguientes pasos:

1) Lo primero que vamos a hacer es crear una tabla con las modificaciones que queremos realizar. En nuestro caso dijimos que vamos a modificar la columna NOM. Fijense que cuando creé la tabla, esa columna está en minúsculas. El cambio que vamos a realizar es colocar el contenido en mayúsculas:


SQL_9iR2> CREATE TABLE test_nueva NOLOGGING PARALLEL 4 AS
  2  SELECT id, UPPER(nom) nom
  3  FROM test ;

Table created.

Elapsed: 00:00:03.05

Como podemos observar, lo que hice fue colocar en el SELECT los cambios que quería realizar. En sólo 3 segundos tenemos nuestra tabla nueva creada y con los cambios realizados.

2)Agregamos las constraints a la nueva tabla y sus respectivos índices:


SQL_9iR2> ALTER TABLE test_nueva ADD CONSTRAINT test_id_pk_2 PRIMARY KEY(id) NOLOGGING PARALLEL 4 ;

Table altered.

Elapsed: 00:00:22.05

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq_2 ON test_nueva(id,nom) NOLOGGING PARALLEL 4 ;

Index created.

Elapsed: 00:00:20.03

3) Dropeamos la tabla TEST:


SQL_9iR2> DROP TABLE test ;

Table dropped.

Elapsed: 00:00:00.02

4) Modificamos el nombre de la tabla TEST_NUEVA, las constraints e índices:


SQL_9iR2> ALTER TABLE test_nueva RENAME TO test ;

Table altered.

Elapsed: 00:00:00.03

SQL_9iR2> ALTER INDEX test_id_nom_uq_2 RENAME TO test_id_nom_uq ;

Index altered.

Elapsed: 00:00:00.02

SQL_9iR2> ALTER TABLE test RENAME CONSTRAINT test_id_pk_2 TO test_id_pk ;

Table altered.

Elapsed: 00:00:00.02

5) Por último, granteamos los permisos que teníamos en la tabla vieja a la nueva tabla.

Para crear los objetos de éste ejemplo, utilicé las cláusulas NOLOGGING y PARALLEL. La cláusula NOLOGGING la utilicé para generar muy poco redo y nada de undo.
La cláusula PARALLEL la utilicé para paralelizar (a través de 4 CPU's en nuestro ejemplo) las operaciones que ejecutamos sobre los objetos.
En caso de que queramos volver a colocar la tabla en modo LOGGING y NOPARALLEL, ejecutaríamos...


SQL_9iR2> ALTER TABLE test LOGGING NOPARALLEL ;

Table altered.

Elapsed: 00:00:00.01

Cómo quedaron los datos de nuestra tabla? Veamos algunos registros...


SQL_9iR2> SELECT *
  2  FROM test
  3  WHERE rownum <= 10 ;

        ID NOM
---------- --------------
     37011 NOM_37011
     37012 NOM_37012
     37013 NOM_37013
     37014 NOM_37014
     37015 NOM_37015
     37016 NOM_37016
     37017 NOM_37017
     37018 NOM_37018
     37019 NOM_37019
     37020 NOM_37020

10 rows selected.

Como podemos observar, realizamos las modificaciones que necesitábamos sin tener un impacto en la performance del sistema.

Clustering Factor

2007-09-11T12:57:00.000-03:00

El Clustering Factor nos dice que tan ordenado se encuentran los registros de la tabla en base a los valores del índice.

Respecto al valor que obtenemos del Clustering Factor, podemos hacer 2 observaciones:

- Si el valor se acerca a la cantidad de bloques de la tabla, entonces se dice que la tabla está muy bien ordenada. En ese caso, el contendido del índice de un leaf block, tiende a apuntar a registros del mismo bloque de datos.
- Si el valor se acerca a la cantidad de registros de la tabla, entonces se dice que la tabla está muy mal ordenada. En ese caso, es improbable que el contendido del índice de un leaf block, tienda a apuntar a registros del mismo bloque de datos.

Bien, pero como afectaría el Clustering Factor en nuestra consulta?

Veamos un ejemplo:

Creamos una tabla llamada "ordenada":


SQL_9iR2> CREATE TABLE ordenada AS
  2  SELECT level x, rpad(dbms_random.random,100,'*') y
  3  FROM dual
  4  CONNECT BY level <= 100000 ;

Table created.

SQL_9iR2> ALTER TABLE ordenada
  2  ADD CONSTRAINT ord_pk
  3  PRIMARY KEY(x) ;

Table altered.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDENADA', cascade => true) ;

PL/SQL procedure successfully completed.

Creamos una tabla llamada "desordenada":


SQL_9iR2> CREATE TABLE desordenada AS
  2  SELECT x, y
  3  FROM ordenada
  4  ORDER BY y ;

Table created.

SQL_9iR2> ALTER TABLE desordenada
  2  ADD CONSTRAINT desord_pk
  3  PRIMARY KEY(x) ;

Table altered.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DESORDENADA', cascade => true) ;

PL/SQL procedure successfully completed.

Ya tenemos nuestras 2 tablas creadas, analizadas y con sus respectivos índices.

Veamos un poco los valores que hay en cada una de las tablas...


SQL_9iR2> SELECT x, y
  2  FROM ordenada
  3  WHERE rownum <= 5 ;

     X Y
------ --------------------------------------------------
     1 1546142627****************************************
       **************************************************

     2 -1607493826***************************************
       **************************************************

     3 -1823003438***************************************
       **************************************************

     4 -385107593****************************************
       **************************************************

     5 -478367392****************************************
       **************************************************

5 rows selected.

SQL_9iR2> SELECT x, y
  2  FROM desordenada
  3  WHERE rownum <= 5 ;

     X Y
------ --------------------------------------------------
 50230 -1000006552***************************************
       **************************************************

 37976 1000011102****************************************
       **************************************************

 71202 1000046989****************************************
       **************************************************

 54512 -1000054026***************************************
       **************************************************

 73252 -1000056784***************************************
       **************************************************

5 rows selected.

Pueden notar que la columna X (donde creamos las 2 primary key) está ordenada en la tabla "ordenada" y desordenada en la tabla "desordenada".

Veamos el Clustering Factor de los índices de cada tabla:


SQL_9iR2> SELECT a.index_name, b.num_rows, b.blocks, a.clustering_factor
  2  FROM dba_indexes a, dba_tables b
  3  WHERE a.index_name IN ('ORD_PK','DESORD_PK')
  4    AND a.table_name = b.table_name ;

INDEX_NAME                       NUM_ROWS     BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
DESORD_PK                          100000       1539             99932
ORD_PK                             100000       1539              1539

2 rows selected.

Para el índice DESORD_PK podemos ver que el valor del CLUSTERING_FACTOR se acerca al valor de NUM_ROWS, esto nos quiere decir que la tabla se encuentra muy mal ordenada y que es improbable que el contendido del leaf block de éste índice, tienda a apuntar a registros del mismo bloque de datos.

Para el índice ORD_PK podemos ver que el valor del CLUSTERING_FACTOR es igual al valor de BLOCKS, esto nos quiere decir que la tabla se encuentra muy bien ordenada y que el contendido del leaf block de éste índice, tiende a apuntar a registros del mismo bloque de datos.

Qué consecuencias puede tener el Clustering Factor en la ejecución de nuestra consulta?

Supongamos la siguiente consulta en la tabla "ordenada":


SQL_9iR2> explain plan for
  2  SELECT *
  3  FROM ordenada
  4  WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  5002 |   512K|    89 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDENADA    |  5002 |   512K|    89 |
|*  2 |   INDEX RANGE SCAN          | ORD_PK      |  5002 |       |    12 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDENADA"."X">=55000 AND "ORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
        757  consistent gets
          0  physical reads
          0  redo size
     562801  bytes sent via SQL*Net to client
       4159  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5001  rows processed

Vemos que el CBO optó por utilizar el índice ORD_PK para realizar un range scan. El CBO sabe, gracias al valor del Clustering Factor, que el índice de ésta tabla se encuentra ordenado; y como nosotros colocamos la condición "BETWEEN 55000 AND 60000", Oracle puede caminar en forma horizontal a través de los leaf blocks sin necesidad de realizar demasiadas lecturas de bloques ya que los valores se encuentran ordenados; y como se encuentran ordenados, el contenido de los leaf blocks tienden a apuntar a registros del mismo bloque de datos.

Ahora veamos que sucede si ejecutamos la misma consulta pero en la tabla "desordenada":


SQL_9iR2> explain plan for
  2  SELECT *
  3  FROM desordenada
  4  WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |  5002 |   512K|   150 |
|*  1 |  TABLE ACCESS FULL   | DESORDENADA  |  5002 |   512K|   150 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DESORDENADA"."X">=55000 AND "DESORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
       1876  consistent gets
       1539  physical reads
          0  redo size
     562801  bytes sent via SQL*Net to client
       4159  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5001  rows processed

En éste caso, el CBO sabe, gracias al valor del Clustering Factor, que el índice de ésta tabla se encuentra desordenado. Entonces, opta por realizar un full scan ya que si accede por el índice DESORD_PK, va a tener que leer mayor cantidad de bloques que si realiza un barrido completo de la tabla.

Hay personas fanáticas de los índices y tratan de evitar a toda costa el acceso por full scan. Bien, veamos que sucede si fuerzo al CBO a que utilice el índice de la tabla desordenada:


SQL_9iR2> explain plan for
  2  SELECT /*+ INDEX(desordenada desord_pk) */ *
  3  FROM desordenada
  4  WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

----------------------------------------------------------------------------
| Id  | Operation                   |  Name        | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  5002 |   512K|  5011 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DESORDENADA  |  5002 |   512K|  5011 |
|*  2 |   INDEX RANGE SCAN          | DESORD_PK    |  5002 |       |    12 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DESORDENADA"."X">=55000 AND "DESORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
       5344  consistent gets
       1495  physical reads
          0  redo size
     562801  bytes sent via SQL*Net to client
       4159  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5001  rows processed

Observamos que si utilizamos el índice de la tabla desordenada, leemos muchos más bloques de datos que si dejamos al CBO hacer lo que mejor sabe hacer; que en éste caso para la tabla desordenada, es realizar un full scan de la tabla.

Cantidad de CPU utilizada en un procedimiento

2007-09-10T16:39:00.000-03:00

Todos conocemos la función DBMS_UTILITY.GET_TIME() que se utiliza para tomar el tiempo entre dos puntos de un determinado proceso. Esta función es muy utilizada por los desarrolladores. La verdad es que no conozco un sólo desarrollador que jamás haya usado ésta función.
En 10g, Oracle agrega una nueva función, DBMS_UTILITY.GET_CPU_TIME() que sirve para saber la cantidad de CPU que es utilizada entre dos puntos de un determinado proceso.

En nuestro primer ejemplo vamos a ver la utilización de éstas 2 funciones sin acceder a disco, y luego, vamos a acceder a disco y vamos a ver la diferencia que se genera tanto en tiempo de CPU como de procesamiento:

Ejemplo SIN I/0 a disco:


SQL_10gR1> DECLARE
  2
  3  l_inicio_1  PLS_INTEGER ;
  4  l_inicio_2  PLS_INTEGER ;
  5  l_fin_1     PLS_INTEGER ;
  6  l_fin_2     PLS_INTEGER ;
  7
  8  BEGIN
  9
 10  l_inicio_1 := DBMS_UTILITY.GET_TIME() ;
 11  l_inicio_2 := DBMS_UTILITY.GET_CPU_TIME() ;
 12
 13      FOR i IN 1 .. 99999999 LOOP
 14          NULL ;
 15      END LOOP ;
 16
 17  l_fin_1 := DBMS_UTILITY.GET_TIME()     - l_inicio_1 ;
 18  l_fin_2 := DBMS_UTILITY.GET_CPU_TIME() - l_inicio_2 ;
 19
 20  DBMS_OUTPUT.PUT_LINE( 'GET_TIME = '     || l_fin_1 || ' hsecs.' ) ;
 21  DBMS_OUTPUT.PUT_LINE( 'GET_CPU_TIME = ' || l_fin_2 || ' hsecs.' ) ;
 22
 23  END ;
 24  /

GET_TIME = 1820 hsecs.
GET_CPU_TIME = 1692 hsecs.

PL/SQL procedure successfully completed.

Ejemplo CON I/0 a disco:


SQL_10gR1> CREATE TABLE test AS
  2  SELECT level id
  3  FROM dual
  4  CONNECT BY level <= 10000000 ;

Table created.

SQL_10gR1> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST') ;

PL/SQL procedure successfully completed.


SQL_10gR1> DECLARE
  2
  3  l_inicio_1  PLS_INTEGER ;
  4  l_inicio_2  PLS_INTEGER ;
  5  l_fin_1     PLS_INTEGER ;
  6  l_fin_2     PLS_INTEGER ;
  7
  8  BEGIN
  9
 10  l_inicio_1 := DBMS_UTILITY.GET_TIME() ;
 11  l_inicio_2 := DBMS_UTILITY.GET_CPU_TIME() ;
 12
 13      FOR i IN ( SELECT * FROM test ) LOOP
 14          NULL ;
 15      END LOOP ;
 16
 17  l_fin_1 := DBMS_UTILITY.GET_TIME()     - l_inicio_1 ;
 18  l_fin_2 := DBMS_UTILITY.GET_CPU_TIME() - l_inicio_2 ;
 19
 20  DBMS_OUTPUT.PUT_LINE( 'GET_TIME = '     || l_fin_1 || ' hsecs.' ) ;
 21  DBMS_OUTPUT.PUT_LINE( 'GET_CPU_TIME = ' || l_fin_2 || ' hsecs.' ) ;
 22
 23  END ;
 24  /

GET_TIME = 7884 hsecs.
GET_CPU_TIME = 7741 hsecs.

PL/SQL procedure successfully completed.

Comillas simples dentro de cadenas de texto en 10g

2007-09-10T16:04:00.001-03:00

En Oracle 10g se agrega una nueva funcionalidad que permite escribir literales en una cadena de texto con comillas simples, sin la necesidad de escribir los literales con dobles, triples, ... comillas. Esta funcionalidad es muy útil cuando los desarrolladores trabajan con sql dinámico, ya que muchas consultas escritas en sql dinámico pueden ser bastante complejas debido a las comillas.
En PL/SQL llamamos a esta funcionalidad de ésta manera q'[.....]'. Podemos reemplazar los [] con cualquiera de éstos caracteres:
[ ]
{ }
( )
< >

Creamos una tabla de ejemplo:


SQL_9iR2> CREATE TABLE emp AS
  2  SELECT level id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 100 ;

Table created.

En Oracle 9iR2 escribiríamos éste código PL/SQL:


SQL_9iR2> DECLARE
  2  l_query  VARCHAR2(100) ;
  3  BEGIN
  4      l_query := 'SELECT id FROM emp WHERE nom = ''nom_10''' ;
  5      EXECUTE IMMEDIATE l_query ;
  6      DBMS_OUTPUT.PUT_LINE(l_query) ;
  7  END ;
  8  /

SELECT id FROM emp WHERE nom = 'nom_10'

PL/SQL procedure successfully completed.

En 10gR1 podríamos transcribir ese mismo código de la siguiente manera:


SQL_10gR1> DECLARE
  2  l_query  VARCHAR2(100) ;
  3  BEGIN
  4      l_query := q'[SELECT id FROM emp WHERE nom = 'nom_10']' ;
  5      EXECUTE IMMEDIATE l_query ;
  6      DBMS_OUTPUT.PUT_LINE(l_query) ;
  7  END ;
  8  /

SELECT id FROM emp WHERE nom = 'nom_10'

PL/SQL procedure successfully completed.


SQL_10gR1> DECLARE
  2  l_query  VARCHAR2(100) ;
  3  BEGIN
  4      l_query := q'(SELECT id FROM emp WHERE nom = 'nom_10')' ;
  5      EXECUTE IMMEDIATE l_query ;
  6      DBMS_OUTPUT.PUT_LINE(l_query) ;
  7  END ;
  8  /

SELECT id FROM emp WHERE nom = 'nom_10'

PL/SQL procedure successfully completed.


SQL_10gR1> DECLARE
  2  l_query  VARCHAR2(100) ;
  3  BEGIN
  4      l_query := q'{SELECT id FROM emp WHERE nom = 'nom_10'}' ;
  5      EXECUTE IMMEDIATE l_query ;
  6      DBMS_OUTPUT.PUT_LINE(l_query) ;
  7  END ;
  8  /

SELECT id FROM emp WHERE nom = 'nom_10'

PL/SQL procedure successfully completed.

Pueden observar, que con ésta nueva funcionalidad, ya no hace falta preocuparnos por las comillas en las cadenas de texto.

Buscar el valor MAX o MIN de un tabla de forma eficiente

2007-09-10T12:14:00.000-03:00

Muchas veces tenemos que buscar el valor máximo o mínimo actual de cierta columna en una tabla, pero suele ser una consulta ineficiente. Generalmente se leen muchos bloques de datos para encontrar el valor MAX/MIN y ésto hace que cuando se ejecute la consulta en nuestro ambiente OLTP bajo gran cantidad de usuarios concurrentes, tengamos graves problemas de performance.
Como es sabido, más del 90% del trabajo de tuning es sobre la aplicación y no necesariamente sobre la base de datos. Este caso es un problema de la aplicación, particularmente cómo se escribe la consulta para obtener el MAX/MIN.

Supongamos el siguiente ejemplo:


SQL_9iR2> CREATE TABLE test AS
  2  SELECT level id, 'nom_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST') ;

PL/SQL procedure successfully completed.

Tenemos una tabla cargada con 1 millón de registros. Supongamos que queremos obtener el MAX de la columna ID de esta tabla. Escribiríamos la siguiente consulta...


SQL_9iR2> explain plan for
  2  SELECT MAX(id)
  3  FROM test ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     5 |   279 |
|   1 |  SORT AGGREGATE      |             |     1 |     5 |       |
|   2 |   TABLE ACCESS FULL  | TEST        |  1000K|  4882K|   279 |
--------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
       2889  consistent gets
        773  physical reads
          0  redo size
        329  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

En el Explain Plan vemos que se está realizando un Full Scan de la tabla TEST y en las estadísticas del Autotrace observamos que se leen 2889 bloques de datos desde memoria y 773 bloques desde disco.
Lo ideal es reducir lo mayor posible la cantidad de lecturas lógicas. Cómo podemos optimizar ésta consulta? Qué sucede si creamos un índice único sobre la columna ID?


SQL_9iR2> CREATE UNIQUE INDEX test_id_idx ON test(id) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT MAX(id)
  3  FROM test ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id  | Operation                  |  Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |              |     1 |     5 |   279 |
|   1 |  SORT AGGREGATE            |              |     1 |     5 |       |
|   2 |   INDEX FULL SCAN (MIN/MAX)| TEST_ID_IDX  |  1000K|  4882K|   279 |
---------------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        329  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL_9iR2> explain plan for
  2  SELECT MIN(id)
  3  FROM test ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id  | Operation                  |  Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |              |     1 |     5 |   279 |
|   1 |  SORT AGGREGATE            |              |     1 |     5 |       |
|   2 |   INDEX FULL SCAN (MIN/MAX)| TEST_ID_IDX  |  1000K|  4882K|   279 |
---------------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        329  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Vemos que tanto para sacar el MAX o el MIN, se están leyendo solamente 3 bloques de datos desde memoria y ninguno desde disco! Esto no es ninguna magia. Se debe a que se está utilizando el método de acceso INDEX FULL SCAN (MIN/MAX).
Cuando se utiliza el acceso INDEX FULL SCAN, Oracle no lee todos los bloques del índice (no lee todos los bloques que conforman la estructura del árbol del índice), sino que lee solamente los Leaf Blocks del índice (son los bloques del índice que contiene los datos. Son los bloques que se encuentran en la raíz del árbol del índice). Cuando se accede a través del INDEX FULL SCAN (MIN/MAX), Oracle solamente accede al primer Leaf Block en caso de que busquemos el MIN, o al último Leaf Block en caso de que busquemos el MAX. Es una manera muy eficiente de obtener el valor máximo o mínimo de un índice.

Veamos lo siguiente. El ejemplo que acabos de realizar fue creando un índice de forma ascendente (es el default). Qué sucede si realizamos el mismo ejemplo creando un índice de forma descendente?


SQL_9iR2> DROP INDEX test_id_idx ;

Index dropped.

Eliminamos el índice que habíamos creado y ejecutamos la consulta buscando el MAX:


SQL_9iR2> explain plan for
  2  SELECT MAX(id)
  3  FROM test ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     5 |   279 |
|   1 |  SORT AGGREGATE      |             |     1 |     5 |       |
|   2 |   TABLE ACCESS FULL  | TEST        |  1000K|  4882K|   279 |
--------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
       2889  consistent gets
        773  physical reads
          0  redo size
        329  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Ahora creamos un índice descendiente por la columna ID:


SQL_9iR2> CREATE UNIQUE INDEX test_id_idx ON test(id DESC) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', cascade => true) ;

PL/SQL procedure successfully completed.

Ejecutamos la consulta buscando el MAX y luego el MIN:


SQL_9iR2> explain plan for
  2  SELECT MAX(id)
  3  FROM test ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     5 |   279 |
|   1 |  SORT AGGREGATE      |             |     1 |     5 |       |
|   2 |   TABLE ACCESS FULL  | TEST        |  1000K|  4882K|   279 |
--------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
       2889  consistent gets
        773  physical reads
          0  redo size
        329  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL_9iR2> explain plan for
  2  SELECT MIN(id)
  3  FROM test ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     5 |   279 |
|   1 |  SORT AGGREGATE      |             |     1 |     5 |       |
|   2 |   TABLE ACCESS FULL  | TEST        |  1000K|  4882K|   279 |
--------------------------------------------------------------------

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
       2889  consistent gets
        773  physical reads
          0  redo size
        329  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Ouch!!! Que pasó??? El CBO no está utilizando nuestro índice! Pero porqué? Bueno, ésto es debido al Bug 1389479. EL CBO no puede utilizar el tipo de acceso INDEX FULL SCAN (MIN/MAX) cuando creamos un índice descendiente.

Utilizando Hint's sobre vistas

2007-09-10T09:14:00.000-03:00

Hay 2 preguntas que me hacen muy a menudo: Cuando consulto una vista ¿Cómo puedo hacer para influenciar al CBO con un Hint sin modificar el contenido de la vista? ¿Porqué el CBO no toma los Hint's que le coloco cuando consulto la vista?

Bien, generalmente veo que cuando se utilizan Hint's sobre las vistas... se hace de forma incorrecta. Lo importante es saber cómo escribir el Hint para que el CBO lo utilice. Para ésto, vamos a realizar un ejemplo.
La primer parte del ejemplo va a consistir en crear una vista y colocar un Hint para que utilice un índice. En la segunda parte vamos a crear otra vista más sobre la vista anterior, y vamos a colocar un Hint para que utilice un índice de la primer vista.

Comencemos creando las tablas, índice y estadísticas...


SQL_9iR2> CREATE TABLE a AS
  2  SELECT level id, 'desc_a_'||level desc_a
  3  FROM dual
  4  CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE TABLE b AS
  2  SELECT level id, 'desc_b_'||level desc_b
  3  FROM dual
  4  CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE UNIQUE INDEX a_id_idx ON a(id) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'A', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'B') ;

PL/SQL procedure successfully completed.

Creamos la primer vista:


SQL_9iR2> CREATE OR REPLACE VIEW view_a_b AS
  2  SELECT a.id, a.desc_a, b.desc_b
  3  FROM a, b
  4  WHERE a.id = b.id ;

View created.

Veamos el Explain Plan de la vista...


SQL_9iR2> explain plan for
  2  SELECT *
  3  FROM view_a_b ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 10000 |   292K|    12 |
|*  1 |  HASH JOIN           |             | 10000 |   292K|    12 |
|   2 |   TABLE ACCESS FULL  | A           | 10000 |   146K|     4 |
|   3 |   TABLE ACCESS FULL  | B           | 10000 |   146K|     4 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID"="B"."ID")

Lo que notamos es que se está realizando un Full Scan de las tabla A y B. Que sucede si a modo de ejemplo queremos hacer que la tabla A acceda por el índice que creamos antes? El CBO eligió el explain plan que vemos como el más óptimo (de hecho lo es), pero supongamos que para nosotros es más conveniente acceder por índice... tendríamos que utilizar un Hint para decirle al CBO que acceda por índice en vez de realizar un Full Scan. Pero como hacemos ésto a través de una vista??? Veamos...


SQL_9iR2> explain plan for
  2  SELECT /*+ INDEX(view_a_b.a a_id_idx) */ *
  3  FROM view_a_b ;

Explained.

SQL_9iR2> @explains

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             | 10000 |   292K|    58 |
|*  1 |  HASH JOIN                   |             | 10000 |   292K|    58 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A           | 10000 |   146K|    50 |
|   3 |    INDEX FULL SCAN           | A_ID_IDX    | 10000 |       |    21 |
|   4 |   TABLE ACCESS FULL          | B           | 10000 |   146K|     4 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID"="B"."ID")

Fijense que en el Hint estoy anteponiendo al nombre de la tabla A, el nombre de la vista. Porqué? Porque si coloco solamente en nombre de la tabla A en el Hint, el CBO no lo toma ya que la tabla A no existe en en nuestra consulta, si existe dentro de la vista VIEW_A_B, es por eso que tenemos que anteponer el nombre de la vista y luego hacer referencia a la tabla A.

Qué suecede si tenemos una vista de una vista y queremos hacer lo mismo que hicimos antes?

Creamos la segunda vista:


SQL_9iR2> CREATE OR REPLACE VIEW view_a_b_2 AS
  2  SELECT *
  3  FROM view_a_b
  4  WHERE id BETWEEN 1 AND 500 ;

View created.

Veamos el Explain Plan de la vista...


SQL_9iR2> explain plan for
  2  SELECT *
  3  FROM view_a_b_2 ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   499 | 14970 |     9 |
|*  1 |  HASH JOIN           |             |   499 | 14970 |     9 |
|*  2 |   TABLE ACCESS FULL  | A           |   500 |  7500 |     4 |
|*  3 |   TABLE ACCESS FULL  | B           |   500 |  7500 |     4 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID"="B"."ID")
   2 - filter("A"."ID">=1 AND "A"."ID"<=500)
   3 - filter("B"."ID">=1 AND "B"."ID"<=500)

Sucedió exactamente lo mismo que en el ejemplo anterior utilizando una única vista. El CBO está accediendo por Full Scan. Pero nosotros somos caprichosos y pensamos que con un índice la consulta sería más eficiente. Cómo le decimos al CBO a través de un Hint que acceda por índice?


SQL_9iR2> explain plan for
  2  SELECT /*+ INDEX(view_a_b_2.view_a_b.a a_id_idx) */ *
  3  FROM view_a_b_2 ;

Explained.

SQL_9iR2> @explains

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   499 | 14970 |    10 |
|*  1 |  HASH JOIN                   |             |   499 | 14970 |    10 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A           |   500 |  7500 |     5 |
|*  3 |    INDEX RANGE SCAN          | A_ID_IDX    |   500 |       |     3 |
|*  4 |   TABLE ACCESS FULL          | B           |   500 |  7500 |     4 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID"="B"."ID")
   3 - access("A"."ID">=1 AND "A"."ID"<=500)
   4 - filter("B"."ID">=1 AND "B"."ID"<=500)

Ahora no sólo estoy anteponiendo en el Hint el nombre de la vista, sino que también estoy anteponiendo el nombre de la segunda vista que creamos. Porqué? Por la misma razón que en el ejemplo anterior. Si hacemos referencia en el Hint sólo a la tabla A, el CBO no utiliza el Hint ya que la tabla A no existe en nuestra consulta, tampoco existe en la vista VIEW_A_B_2, pero si existe dentro de la vista VIEW_A_B; es por eso que tenemos que anteponer el nombre de las 2 vistas y luego hacer referencia a la tabla A.

Monitoreando el progreso de largas ejecuciones

2007-09-08T00:16:00.000-03:00

Para monitorear las largas ejecuciones que se producen en la base de datos, contamos con la vista del diccionario de datos llamada V$SESSION_LONGOPS. Esta vista nos permite monitorear tanto las sentencias DML como así también las sentencias DDL.
Pero cuáles son las sentencias que aparecen en esa vista?
Una ejecución es considerada una operación 'larga' y aparece en la vista V$SESSION_LONGOPS si la ejecución demora más de 6 segundos. Este no es el único criterio por el cual una sentencia aparece en la vista. También aparecen las operaciones en la vista si Oracle tiene que leer más de 10.000 bloques de la tabla.
Pero no todas las operaciones que cumplen estos criterios aparecen en esa vista.

Cada versión de Oracle agrega más tipos de operaciones a la vista V$SESSION_LONGOPS.
Algunas de esas operaciones son:
- Table Scan
- Index Fast Full Scan
- Hash Join
- Sort/Merge
- Sort Output
- Estadísticas
- Rollback

Veamos un ejemplo:


SQL_9iR2> CREATE TABLE test AS
  2  SELECT level a, level b, level c
  3  FROM dual
  4  CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

Bien, ya tenemos nuestra tabla creada, cargada con 1 millón de registros y analizada.
Lo que vamos a hacer ahora es ejecutar una consulta en una sesión, y en otra sesión vamos a consultar a la vista V$SESSION_LONGOPS mientras la consulta se ejecuta.

En la SESION_1 ejecuto la consulta (Esta consulta realiza un Full Scan sobre la tabla):


SQL_9iR2> SELECT a, b, c
  2  FROM test
  3  ORDER BY a ASC, b DESC, c ASC ;

En la SESION_2 ejecuto la consulta sobre la vista:


SQL_9iR2> SELECT sid, serial#, username, opname, sql_hash_value hash_value,
  2         TO_CHAR(start_time,'HH24:MI:SS') "INICIO",
  3         (sofar/totalwork)*100 "%"
  4  FROM v$session_longops
  5  WHERE (sofar/totalwork)*100 < 100 ;

no rows selected

Fijense, que mientras estoy corriendo la consulta de la SESION_1, cuando ejecuto la consulta de la SESION_2, no me devuelve ningún registro. Esto sucede porque aún Oracle no leyó 10.000 bloques de datos.
Qué sucede si intento ejecutar la consulta nuevamente?


SQL_9iR2> SELECT sid, serial#, username, opname, sql_hash_value hash_value,
  2         TO_CHAR(start_time,'HH24:MI:SS') "INICIO",
  3         (sofar/totalwork)*100 "%"
  4  FROM v$session_longops
  5  WHERE (sofar/totalwork)*100 < 100 ;

       SID    SERIAL# USERNAME   OPNAME          HASH_VALUE INICIO            %
---------- ---------- ---------- --------------- ---------- -------- ----------
        87       6852 LEO        Sort Output     3570479103 22:27:58 4.24448217

1 row selected.

Vemos que el porcentaje está en 4.24 y tenemos que llegar al 100 porciento para que la consulta termine de ejecutarse.
Ejecuto nuevamente la consulta:


SQL_9iR2> SELECT sid, serial#, username, opname, sql_hash_value hash_value,
  2         TO_CHAR(start_time,'HH24:MI:SS') "INICIO",
  3         (sofar/totalwork)*100 "%"
  4  FROM v$session_longops
  5  WHERE (sofar/totalwork)*100 < 100 ;

       SID    SERIAL# USERNAME   OPNAME          HASH_VALUE INICIO            %
---------- ---------- ---------- --------------- ---------- -------- ----------
        87       6852 LEO        Sort Output     3570479103 22:27:58 7.53820034

1 row selected.

El porcentaje se incrementó. Ahora se encuentra en 7.5 y aún falta mucho para que la consulta de la SESION_1 termine de ejecutarse.

Algo interesante para ver de la vista, es que tenemos una columna llamada SQL_HASH_VALUE en donde nos muestra el valor hash (recordemos que cuando Oracle tiene que buscar una consulta en la Shared Pool, busca esa consulta mediante un valor hash. Cuando ejecutamos una consulta, Oracle aplica una función sobre la consulta obteniendo el valor hash que vemos en ésta tabla en la columna HASH_VALUE) de la consulta que estamos ejecutando. Si tomamos ese valor y buscamos la consulta en la V$SQLTEXT...


SQL_9iR2> SELECT sql_text
  2  FROM v$sqltext
  3  WHERE hash_value = 3570479103
  4  ORDER BY piece ;

SQL_TEXT
------------------------------------------------------------
SELECT a, b, c FROM test ORDER BY a ASC, b DESC, c ASC

1 row selected.

Bien, ahora veamos lo siguiente. El paquete DBMS_APPLICATION_INFO, contiene un procedimiento llamado SET_SESSION_LONGOPS, que se utiliza para popular la vista V$SESSION_LONGOPS desde una aplicación.

Veamos un ejemplo:


SQL_9iR2> DECLARE
  2  l_rindex     BINARY_INTEGER := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT ;
  3  l_sofar      NUMBER := 0 ;
  4  l_slno       BINARY_INTEGER ;
  5  BEGIN
  6      FOR i IN ( SELECT a, b, c
  7                 FROM test
  8                 WHERE rownum <= 1000
  9                 ORDER BY a ASC, b DESC, c ASC ) LOOP
 10
 11          l_sofar := l_sofar + 1 ;
 12
 13          DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
 14                      (rindex      => l_rindex ,
 15                       slno        => l_slno ,
 16                       op_name     => 'TEST LEO' ,
 17                       target      => NULL ,
 18                       context     => 0 ,
 19                       sofar       => l_sofar ,
 20                       totalwork   => 1000 ,
 21                       target_desc => 'Completado') ;
 22
 23      END LOOP ;
 24  END ;
 25  /

PL/SQL procedure successfully completed.

Ese bloque PL/SQL anónimo, iteró 1000 veces y logueo la operación en la vista.
Si ahora queremos ver esa información, lo único que tenemos que hacer es consultar la V$SESSION_LONGOPS.


SQL_9iR2> SELECT sid, serial#, username, opname, sql_hash_value hash_value,
  2         TO_CHAR(start_time,'HH24:MI:SS') "INICIO",
  3         (sofar/totalwork)*100 "%"
  4  FROM v$session_longops ;

       SID    SERIAL# USERNAME   OPNAME          HASH_VALUE INICIO            %
---------- ---------- ---------- --------------- ---------- -------- ----------
        58      19110 LEO        TEST LEO        2061745150 23:13:00        100

Como pueden ver, este procedimiento es muy útil cuando queremos, por ejemplo, monitorear una consulta larga y poder identificarla en la vista de manera fácil como en éste último ejemplo.

Búsquedas Case-Insensitive

2007-09-07T11:03:00.000-03:00

Los tipos de búsquedas Case-Insensitive (nueva funcionalidad en Oracle 10gR2) nos permiten tratar por igual las letras mayúsculas y las letras minúsculas cuando realizamos una búsqueda.

Veamos...


SQL_10gR2> SELECT * FROM test WHERE texto = 'oracle' ;

TEXTO
----------
Oracle
ORACLE
oraCLE
oracle

4 rows selected.

Fijense que cuando busco las palabra 'oracle' en minúsculas, gracias a ésta nueva funcionalidad, la consulta me devuelve todas las palabras 'oracle' que encuentra.
Pero cómo logramos realizar ésto? Necesitamos setear algún parámetro?

Realicemos el ejemplo completo:


SQL_10gR2> CREATE TABLE test ( texto  VARCHAR2(10) ) ;

Table created.

SQL_10gR2> INSERT INTO test VALUES ( 'Oracle' ) ;

1 row created.

SQL_10gR2> INSERT INTO test VALUES ( 'ORACLE' ) ;

1 row created.

SQL_10gR2> INSERT INTO test VALUES ( 'oraCLE' ) ;

1 row created.

SQL_10gR2> INSERT INTO test VALUES ( 'oracle' ) ;

1 row created.

Ya tenemos nuestra tabla creada con sus respectivos datos cargados.
Ahora vamos a prestar atención a 2 parámetros: NLS_COMP y NLS_SORT.

En versiones anteriores a la 10gR2, si teníamos la tabla TEST (la tabla de nuestro ejemplo) con estos mismos datos cargados y buscabamos la palabra 'oracle' (con el parámetro NLS_SORT = BINARY que es el valor por default), veríamos lo siguiente:


SQL_10gR2> SELECT * FROM test WHERE texto = 'oracle' ;

TEXTO
----------
oracle

1 row selected.

Qué sucede si nuestra aplicación necesita realizar búsquedas que devuelvan tanto los valores que se encontraron en mayúsculas como los valores encontrados en minúsculas?


SQL_10gR2> ALTER SESSION SET NLS_COMP = ANSI ;

Session altered.

SQL_10gR2> ALTER SESSION SET NLS_SORT = BINARY_CI ;

Session altered.

SQL_10gR2> SELECT * FROM test WHERE texto = 'oracle' ;

TEXTO
----------
Oracle
ORACLE
oraCLE
oracle

4 rows selected.

Al setear el parámetro NLS_SORT = BINARY_CI, Oracle convierte nuestras búsquedas en case-insensitive.

Pero qué sucede si nuestra aplicación necesita hacer búsquedas del tipo LIKE '%ora%' porque no conoce la palabra completa que quiere buscar?


SQL_10gR2> SELECT * FROM test WHERE texto LIKE '%ora%' ;

TEXTO
----------
oraCLE
oracle

2 rows selected.

Al parecer con los parámetros que seteamos anteriormente no fue suficiente para satisfacer el resultado de ésta consulta.
Bien, es hora de setear correctamente el parámetro NLS_COMP. Si lo seteamos con el valor LINGUISTIC, le dice a Oracle que cada sort y comparación, use el orden lingüístico del parámetro NLS_SORT. Esto quiere decir, que si seteamos NLS_SORT = BINARY_CI, todos los sorts y comparaciones van a ser case-insensitive en esa sesión.


SQL_10gR2> ALTER SESSION SET NLS_COMP = LINGUISTIC ;

Session altered.

SQL_10gR2> ALTER SESSION SET NLS_SORT = BINARY_CI ;

Session altered.

SQL_10gR2> SELECT * FROM test WHERE texto LIKE '%ora%' ;

TEXTO
----------
Oracle
ORACLE
oraCLE
oracle

4 rows selected.

SQL_10gR2> SELECT * FROM test WHERE texto = 'oracle' ;

TEXTO
----------
Oracle
ORACLE
oraCLE
oracle

4 rows selected.

Fijense, que éste último seteo de parámetros me sirvió para satisfacer las 2 consultas que estuvimos realizando!

Veamos el explain plan de ésta última consulta:


SQL_10gR2> explain plan for
  2  SELECT * FROM test WHERE texto = 'oracle' ;

Explained.

SQL_10gR2> @explains
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NLSSORT("TEXTO",'nls_sort=''BINARY_CI''')=HEXTORAW('6F7261636C6500') )

Note
-----
   - dynamic sampling used for this statement
   - star transformation used for this statement

Vemos que el filter del explain plan nos muestra que se está utilizando case-insensitive.
Bien, ahora vamos a crear un índice con el contenido del filter que acabamos de ver:


SQL_10gR2> CREATE INDEX test_texto_idx ON test (NLSSORT(texto,'NLS_SORT=BINARY_CI')) ;

Index created.

Ejecutamos nuevamente la consulta:


SQL_10gR2> explain plan for
  2  SELECT * FROM test WHERE texto = 'oracle' ;

Explained.

SQL_10gR2> @explains
Plan hash value: 2134909805

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     4 |    28 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST           |     4 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_TEXTO_IDX |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("TEXTO",'nls_sort=''BINARY_CI''')=HEXTORAW('6F7261636C6500') )

Note
-----
   - dynamic sampling used for this statement
   - star transformation used for this statement

ANYDATA

2007-09-06T23:45:00.000-03:00

En Oracle 9i se agregó un nuevo tipo de dato: ANYDATA. Normalmente, creamos un campo DATE para guardar fechas, un campo VARCHAR2 para guardar texto, un campo NUMBER para guardar números... pero qué sucede si mi aplicación guarda datos genéricos? Es decir, qué sucede si mi aplicación guarda en un mismo campo valores de distinto tipo de dato? En éste tipo de casos nos convendría usar el tipo de dato ANYDATA.

Veamos un ejemplo:


SQL_9iR2> CREATE TABLE test ( algo  sys.anyData ) ;

Table created.

SQL_9iR2> INSERT INTO test VALUES ( sys.anyData.ConvertVarchar2('esto es
un ejemplo') ) ;

1 row created.

SQL_9iR2> INSERT INTO test VALUES ( sys.anyData.ConvertDate(sysdate) ) ;

1 row created.

SQL_9iR2> INSERT INTO test VALUES ( sys.anyData.ConvertNumber(1000) ) ;

1 row created.

Si consultamos la tabla (como lo hariamos de forma tradicional), no vemos los datos... simplemente vemos el tipo ANYDATA...


SQL_9iR2> SELECT *
  2  FROM test ;

ALGO()
-----------------
ANYDATA()
ANYDATA()
ANYDATA()

3 rows selected.

SQL_9iR2> DESC test
 Name                      Null?    Type
 ------------------------- -------- --------------
 ALGO                               SYS.ANYDATA

Antes de mostrarles cómo ver los datos reales de la tabla... Veamos los tipos de cada dato...


SQL_9iR2> SELECT anyData.gettypeName(algo) TipoDeDato
  2  FROM test ;

TIPODEDATO
-----------------
SYS.VARCHAR2
SYS.DATE
SYS.NUMBER

3 rows selected.

Lamentablemente, no es tan fácil ver los datos de la tabla. Pero porqué no es fácil? Porque está pensando para ser utilizado dentro de un procedimiento en donde se hace el fetch de los datos y luego se los convierte y procesa. Entonces veamos... Para ver los datos reales de la tabla, vamos a construir un package con 3 funciones. Cada una de esas funciones se va a encargar de convertir un tipo de dato y devolvernos el valor de la conversión:


SQL_9iR2> CREATE OR REPLACE PACKAGE pkg_convertir_anydata AS
  2
  3     FUNCTION convertir_varchar2 ( p_dato  IN  sys.anydata ) RETURN VARCHAR2 ;
  4     FUNCTION convertir_date     ( p_dato  IN  sys.anydata ) RETURN DATE ;
  5     FUNCTION convertir_number   ( p_dato  IN  sys.anydata ) RETURN NUMBER ;
  6
  7  END pkg_convertir_anydata ;
  8  /

Package created.

SQL_9iR2> CREATE OR REPLACE PACKAGE BODY pkg_convertir_anydata AS
  2
  3     FUNCTION convertir_varchar2 ( p_dato  IN  sys.anydata ) RETURN VARCHAR2 IS
  4        nada       NUMBER ;
  5        l_varchar2 VARCHAR2(4000) ;
  6     BEGIN
  7        nada := p_dato.GetVarchar2( l_varchar2 ) ;
  8        RETURN ( l_varchar2 ) ;
  9     END ;
 10
 11     FUNCTION convertir_date ( p_dato  IN  sys.anydata ) RETURN DATE IS
 12        nada       NUMBER ;
 13        l_date     DATE ;
 14     BEGIN
 15        nada := p_dato.GetDate( l_date ) ;
 16        RETURN ( l_date ) ;
 17     END ;
 18
 19     FUNCTION convertir_number ( p_dato  IN  sys.anydata ) RETURN NUMBER IS
 20        nada       NUMBER ;
 21        l_number   NUMBER ;
 22     BEGIN
 23        nada := p_dato.GetNumber ( l_number ) ;
 24        RETURN ( l_number ) ;
 25     END ;
 26
 27  END pkg_convertir_anydata ;
 28  /

Package body created.

Lo único que nos resta por hacer es construir la consulta para convertir y mostrar nuestros datos:


SQL_9iR2> WITH Datos AS
  2  ( SELECT algo , sys.anydata.gettypename(algo) TipoDeDato FROM test )
  3  SELECT CASE
  4             WHEN TipoDeDato = 'SYS.VARCHAR2' THEN
  5                  TO_CHAR(pkg_convertir_anydata.convertir_Varchar2(algo))
  6             WHEN TipoDeDato = 'SYS.DATE' THEN
  7                  TO_CHAR(pkg_convertir_anydata.convertir_Date(algo),'dd-mon-
rrrr hh24:mi:ss')
  8             WHEN TipoDeDato = 'SYS.NUMBER' THEN
  9                  TO_CHAR(pkg_convertir_anydata.convertir_Number(algo))
 10         END algo
 11  FROM Datos ;

ALGO
--------------------------
esto es un ejemplo
06-sep-2007 21:49:37
1000

3 rows selected.

Foreign Keys no indexadas

2007-09-05T20:08:00.000-03:00

Suelo encontrarme en los diseños de las aplicaciones, foreign keys que no se encuentran indexadas. No indexar las foreign keys puede ser un gran problema de performance.

Podemos encontrar 2 tipos de problemas:
1.- Se produce un loqueo de la tabla si modificamos (muy inusual) o eliminamos algún registro de la primary key (tabla padre) y las foreign keys (tabla hija) no se encuentran indexadas.
2.- Si tenemos la cláusula ON DELETE CASCADE y no tenemos índices creados en la foreign keys, si eliminamos algún registro de la primary key, como no tenemos indexadas las foreign keys, se producirá un full scan de la tabla que contiene las foreign keys. Esto puede ser el factor de un grave problema de performance ya que si eliminamos varios registros de la tabla padre, se realizará un full scan de la tabla hija por cada registro eliminado de la tabla padre.

Veamos un ejemplo:


SQL_9iR2> CREATE TABLE tabla_padre AS
  2  SELECT level id
  3  FROM dual
  4  CONNECT BY level <= 9 ;

Table created.

SQL_9iR2> CREATE TABLE tabla_hija AS
  2  SELECT decode(mod(level,9),0,9,mod(level,9)) id , 'detalle_'||level detalle
  3  FROM dual
  4  CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> ALTER TABLE tabla_padre
  2  ADD CONSTRAINT tabla_padre_pk
  3  PRIMARY KEY (id) ;

Table altered.

SQL_9iR2> ALTER TABLE tabla_hija
  2  ADD CONSTRAINT tabla_padre_hija_fk
  3  FOREIGN KEY (id) REFERENCES tabla_padre(id)
  4  ON DELETE CASCADE ;

Table altered.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TABLA_PADRE', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TABLA_HIJA', cascade => true) ;

PL/SQL procedure successfully completed.

Bien, ya tenemos nuestras tablas de ejemplo con sus respectivos datos, constraints y estadísticas.

Ejecutamos un insert en la tabla hija:


SQL_9iR2> INSERT INTO tabla_hija VALUES(5,'detalle'||5) ;

1 row created.

Ahora ejecutamos un delete en la tabla padre:


SQL_9iR2> DECLARE
  2  PRAGMA AUTONOMOUS_TRANSACTION ;
  3  BEGIN
  4      DELETE FROM tabla_padre
  5      WHERE id = 2 ;
  6
  7      COMMIT ;
  8  END ;
  9  /
DECLARE
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

Como podemos ver, al no tener un índice en la foreign key se produce un Deadlock.
Veamos qué sucede si creamos un índice sobre la foreign key y repetimos el procedimiento.


SQL_9iR2> CREATE INDEX tabla_hija_id_idx ON tabla_hija(id) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TABLA_HIJA', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> INSERT INTO tabla_hija VALUES(5,'detalle'||5) ;

1 row created.

SQL_9iR2> DECLARE
  2  PRAGMA AUTONOMOUS_TRANSACTION ;
  3  BEGIN
  4      DELETE FROM tabla_padre
  5      WHERE id = 2 ;
  6
  7      COMMIT ;
  8  END ;
  9  /

PL/SQL procedure successfully completed.

Mi consejo es el siguiente: Si realizamos deletes en la tabla padre y/o updates en la primary key de la tabla padre, entonces debemos crear índices en las foreign keys de la tabla hija.

Deadlocks --> ORA-00060

2007-09-05T12:10:00.000-03:00

Qué es un Deadlock? Un Deadlock (abrazo mortal) es cuando 2 o más usuarios están esperando algún dato que está siendo loqueado por alguna sesión. Si ésto sucede, los usuarios involucrados en el Deadlock deben esperar y no pueden continuar con el procesamiento.

Cuando Oracle detecta que se produjo un Deadlock, lo que hace es cortar la ejecución del procedimiento y mostrar el siguiente mensaje de error: ORA-00060: deadlock detected while waiting for resource. Tengamos en cuenta que cuando se produce éste error, Oracle genera un archivo de trace en el directorio UDUMP con información acerca del error.

Generalmente éste problema se produce por un mal diseño de la aplicación. Veamos un ejemplo...


SQL_9iR2> CREATE TABLE sesion_1 AS
  2  SELECT level id, 'nom_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 10 ;

Table created.

SQL_9iR2> CREATE TABLE sesion_2 AS
  2  SELECT level id, 'nom_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 10 ;

Table created.

En la SESION 1 loqueo un registro de la tabla SESION_1 correspondiente al ID 1.


SQL_9iR2> UPDATE sesion_1
  2  SET nombre = 'nom_'||id*2
  3  WHERE id = 1 ;

1 row updated.

En la SESION 2 loqueo un registro de la tabla SESION_2 correspondiente al ID 1.


SQL_9iR2> UPDATE sesion_2
  2  SET id = id+10
  3  WHERE id = 1 ;

1 row updated.

En la SESION 1 modifico un registro de la tabla SESION_2 correspondiente al ID 1. Vemos que esta sesión se 'colgó' debido al loqueo y no nos devuelve el control. Todavía no se produjo el Deadlock... sólo se produjo un loqueo.


SQL_9iR2> UPDATE sesion_2
  2  SET id = id+10
  3  WHERE id = 1 ;

En la SESION 2 modifico un registro de la tabla SESION_1 correspondiente al ID 1.
Vemos que esta sesión se 'colgó' debido al loqueo y no nos devuelve el control.


SQL_9iR2> UPDATE sesion_1
  2  SET nombre = 'nom_'||id*2
  3  WHERE id = 1 ;

Esto va a producir un Deadlock y luego de unos segundos aparece un mensaje de error en la SESION 1:


SQL_9iR2> UPDATE sesion_2
  2  SET id = id+10
  3  WHERE id = 1 ;
UPDATE sesion_2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

La SESION 2 sigue colgada esperando que la SESION 1 termine la transacción que comenzó. Entonces en la SESION 1 ejecutamos...


SQL_9iR2> rollback ;

Rollback complete.

En la SESION 2 se libera automáticamente el loqueo...


SQL_9iR2> UPDATE sesion_1
  2  SET nombre = 'nom_'||id*2
  3  WHERE id = 1 ;

1 row updated.

Estadísticas en paralelo

2007-09-04T15:34:00.000-03:00

Cuando necesitamos analizar objetos de muy grandes dimensiones, podemos obtener estadísticas en paralelo con el paquete DBMS_STATS (por esto, y por muchas cosas más, es el porque Oracle recomienda en la documentación utilizar éste paquete para obtener estadísticas en vez del comando ANALYZE).

Veamos un ejemplo:


SQL_9iR2> CREATE TABLE t
  2  NOLOGGING
  3  AS
  4  SELECT level a, mod(level,3) b, level c
  5  FROM dual
  6  CONNECT BY level <= 10000000 ;

Table created.

Elapsed: 00:01:00.08

SQL_9iR2> CREATE UNIQUE INDEX t_abc_ASC_uq ON t ( a ASC, b ASC, c ASC ) NOLOGGING ;

Index created.

Elapsed: 00:00:31.05

SQL_9iR2> CREATE UNIQUE INDEX t_abc_DESC_uq ON t ( a DESC, b DESC, c DESC ) NOLOGGING ;

Index created.

SQL_9iR2> EXEC dbms_stats.gather_table_stats( ownname => USER, 
tabname => 'T', cascade => TRUE ) ;

PL/SQL procedure successfully completed.

Elapsed: 00:02:59.03

Vemos que si obtenemos las estadísticas computadas en serial, tardamos 3 minutos. Veamos que sucede si realizamos lo mismo pero en paralelo...


SQL_9iR2> EXEC dbms_stats.gather_table_stats( ownname => USER, 
tabname => 'T', cascade => TRUE , degree => 4 ) ;

Mientras se ejecuta éste procedimiento, podemos consultar la vista V$PX_PROCESS (en otra sesión) para ver si efectivamente estamos realizando una ejecución en paralelo...


SQL_9iR2> select * from v$px_process ;

SERV STATUS           PID SPID                SID    SERIAL#
---- --------- ---------- ------------ ---------- ----------
P000 IN USE            48 19360                74       1093
P001 IN USE            60 19362                64        773
P002 IN USE            62 19364                12       4904
P003 AVAILABLE         70 19366
P004 IN USE            74 19368                86        447

5 rows selected.

Observamos que hay 4 procesos que se están utilizando (IN USE) para obtener las estadísticas en paralelo. Veamos que sucede cuando termina de ejecutarse el procedimiento y volvemos a consultar la vista.


PL/SQL procedure successfully completed.

Elapsed: 00:01:21.04

SQL_9iR2> select * from v$px_process ;

SERV STATUS           PID SPID                SID    SERIAL#
---- --------- ---------- ------------ ---------- ----------
P000 AVAILABLE         48 19360
P001 AVAILABLE         60 19362
P002 AVAILABLE         62 19364
P003 AVAILABLE         70 19366
P004 AVAILABLE         74 19368

5 rows selected.

Los procesos que estaban en estado IN USE pasaron a estar AVAILABLE nuevamente y obtuvimos las estadísticas en 1 minuto 20 segundos!!!

Explain Plan vs. Autotrace Explain Plan

2007-09-03T14:06:00.001-03:00

Hace unos meses me hicieron una pregunta que hoy en día veo que muchas personas no saben la respuesta. La pregunta era: ¿ Cuál es la diferencia de ejecutar el Explain Plan en SQL*Plus de ejecutar el Explain Plan desde la herramienta Autotrace ? Bueno, para contestar esta pregunta vamos a crear un muy sencillo ejemplo:


SQL_9iR2> CREATE TABLE estudiantes
  2  (
  3  legajo     NUMBER(5),
  4  nombre     VARCHAR2(30),
  5  ingreso    DATE
  6  )
  7  PARTITION BY RANGE(ingreso)
  8  (
  9  PARTITION ingreso_2006 VALUES LESS THAN(TO_DATE('01/01/2007','DD/MM/YYYY'))
,
 10  PARTITION ingreso_2007 VALUES LESS THAN(TO_DATE('01/01/2008','DD/MM/YYYY'))

 11  ) ;

Table created.

SQL_9iR2> INSERT INTO estudiantes
  2  SELECT level, 'nom_'||level, DECODE(MOD(level,2),0,sysdate,1,sysdate-500)
  3  FROM dual
  4  CONNECT BY level <= 1000 ;

1000 rows created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(user,'ESTUDIANTES') ;

PL/SQL procedure successfully completed.

Ya creamos nuestro ambiente de prueba. Veamos que sucede si vemos el Explain Plan desde el Autotrace:


SQL_9iR2> SET AUTOTRACE TRACEONLY EXPLAIN

SQL_9iR2> SELECT legajo, nombre
  2  FROM estudiantes
  3  WHERE ingreso = to_date('03/09/2007 12:11:27','dd/mm/yyyy hh24:mi:ss') ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=500 Bytes=9500)
   1    0   TABLE ACCESS (FULL) OF 'ESTUDIANTES' (Cost=2 Card=500 Bytes=9500)

Vemos que estamos haciendo un full scan de la tabla, pero si observamos bien, podemos notar que en realidad no estamos leyendo la tabla entera (que tiene 1.000 registros), sino que sólo estamos leyendo 500 registros. Esto nos esta indicando que no estamos leyendo las 2 particiones que creamos, sólo estamos leyendo 1 de ellas... pero cuál?

Veamos que sucede si ejecutamos el Explain Plan:


SQL_9iR2> EXPLAIN PLAN FOR
  2  SELECT legajo, nombre
  3  FROM estudiantes
  4  WHERE ingreso = to_date('03/09/2007 12:11:27','dd/mm/yyyy hh24:mi:ss') ;

Explained.

SQL_9iR2> @explains

-------------------------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |   500 |  9500 |     2 |       |       |
|*  1 |  TABLE ACCESS FULL   | ESTUDIANTES  |   500 |  9500 |     2 |     2 |     2 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ESTUDIANTES"."INGRESO"=TO_DATE(' 2007-09-03 12:11:27', 'syyyy-mm-ddhh24:mi:ss'))

Claramente vemos que en el Explain Plan tenemos 2 columnas nuevas: Pstart y Pstop. Estas columnas nos están indicando que sólo estamos haciendo un full scan de la partición 2, no de la tabla completa.

Moraleja: Utilicen el Explain Plan cuando estén interesados en el plan de ejecución de la consulta. Utilicen el Autotrace cuando estén interesados en las estadísticas de la consulta.

Estado de los cursores

2007-08-31T15:47:00.000-03:00

A menudo veo que los desarrolladores y DBA's consultan la vista V$OPEN_CURSOR para identificar todos los cursores que se encuentran abiertos en la sesión. Pero esta vista muestra realmente los cursores actualmente abiertos en la sesión? La respuesta es NO.

Veamos un ejemplo:

Consultamos la vista V$OPEN_CURSOR:


SQL_9iR2> SELECT count(*)
  2  FROM v$open_cursor ;

  COUNT(*)
----------
       169

1 row selected.

La vista nos muestra que tenemos 169 cursores abiertos. Pero en realidad lo que nos está mostrando es la cantidad de cursores actualmente abiertos en la sesión y los cursores que se encuentran actualmente cerrados (pero abiertos). Estos cursores que se encuentran cerrados pero cacheados, son los cursores que Oracle mantiene silenciosamente abiertos con la esperanza de que los volvamos a utilizar en la sesión.
Pero porque Oracle cachea los cursores? Principalmente para reducir la cantidad de parseos. Este cacheo de cursores es muy importante en aplicaciones como Oracle Forms, que suelen cerrar todos los cursores abiertos por el form principal cuando se cambia de un form a otro. Entonces, cacheando los cursores reducimos en gran medida los parseos que se realizan. Por otro lado, como reducimos la cantidad de parseos, reducimos también los loqueos (latches) que se realizan el la Library Cache y en la Shared Pool.

Suelo crear en mis bases de datos la vista MY_STATS para que los desarrolladores puedan consultar fácilmente las estadísticas de sus respectivas sesiones:


SQL_9iR2> CREATE VIEW my_stats AS
  2  SELECT s.*, n.name
  3  FROM v$mystat s, v$statname n
  4  WHERE s.statistic# = n.statistic# ;

View created.

Veamos que suecede si consultamos el valor del parámetro 'opened cursors current' de la vista MY_STATS:


SQL_9iR2> SELECT *
  2  FROM my_stats
  3  WHERE name = 'opened cursors current' ;

       SID STATISTIC#      VALUE NAME
---------- ---------- ---------- -----------------------------------------------
        43          3          4 opened cursors current

1 row selected.

Vemos que nos devuelve el valor 4. Esa es la cantidad real de cursores actualmente abiertos en nuestra sesión.

Index-Skip Scans

2007-08-30T10:41:00.001-03:00

El "Index-Skip Scans" está disponible a partir de Oracle 9i. Cuando tenemos un índice compuesto, permite en algunas circunstancias, que el CBO no tome en cuenta la primer columna del índice, sino que lea las restantes. Esto es útil cuando en una consulta hacemos referencia a alguna de las columnas que no están en la cabecera del índice pero que sin embargo queremos utilizar ese índice. Obviamente, el CBO utiliza Index-Skip Scans solamente cuando se cumplen 2 condiciones:

1- La columna cabecera del índice debe contener muy pocos valores distintos. Osea, tiene que ser una columna no selectiva. Tipicamente son las columnas apropiadas para la utilización de un Bitmap Index.
2- En la consulta debemos hacer referencia, por lo menos, a alguna de las columnas restantes del índice.

Supongamos que tenemos 3 consultas:


SELECT d FROM test WHERE a = :a ;

SELECT d FROM test WHERE a = :a AND b = :b ;

SELECT d FROM test WHERE a = :a AND b = :b AND c = :c ;

Qué índice nos conviene crear para que sea utilizado en las 3 consultas?
Claramente crearíamos un índice B*Tree compuesto por las columnas A,B,C.

Qué sucede si tenemos ésta consulta?


SELECT d FROM test WHERE b = :b AND c = :c ;

El índice es utilizado?

Veamos un ejemplo:


SQL_9iR2> CREATE TABLE test AS
  2  SELECT mod(level,3) a, level b, level c, 'nom_'||level d
  3  FROM dual
  4  CONNECT BY level <= 20000 ;

Table created.

SQL_9iR2> CREATE INDEX t_abc_idx ON test(a,b,c) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

Ya tenemos nuestra tabla creada y un índice por las columnas A,B,C. Observen que inserté en la columna A sólo 3 valores distintos (0,1 y 2).

Bien, veamos el explain plan de la consulta que vimos anteriormente:


SQL_9iR2> explain plan for
  2  SELECT d
  3  FROM test
  4  WHERE a = 2 AND b = 182 AND c = 182 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    21 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    21 |     2 |
|*  2 |   INDEX RANGE SCAN          | T_ABC_IDX   |     1 |       |     1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST"."A"=2 AND "TEST"."B"=182 AND "TEST"."C"=182)

Como podemes ver, el índice es utilizado porque estamos accediendo a las 3 columnas del índice. Que sucede si accedemos sólo a las columnas B y C ?


SQL_9iR2> explain plan for
  2  SELECT d
  3  FROM test
  4  WHERE b = 182 AND c = 182 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    20 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    20 |     5 |
|*  2 |   INDEX SKIP SCAN           | T_ABC_IDX   |     1 |       |     4 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST"."B"=182 AND "TEST"."C"=182)
       filter("TEST"."B"=182 AND "TEST"."C"=182)

Observamos que el índice es utilizado porque cumplimos con las 2 condiciones necesarios para que el CBO utilice Index-Skip Scans.

La utilización de ésta clase de acceso es más costosa que realizar un accedo directo a través del índice, pero en general es menos costosa que utilizar un full-scan.

APPEND hint

2007-08-28T09:21:00.001-03:00

El hint APPEND permite que Oracle comience a escribir bloques nuevos luego de la HWM (high water mark) de la tabla.

Veo a menudo a desarrolladores y DBA's usar este hint, y en la mayoría de los casos, el hint no es usado por Oracle. Pero porque?

Veamos un ejemplo:

SQL_9iR2> CREATE TABLE test ( a number, b number ) ;

Table created.

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

SQL_9iR2> INSERT INTO test
2  SELECT level, level
3  FROM dual
4  CONNECT BY level <= 1000000 ;    

1000000 rows created.    

Statistics
---------------------------------------------------                    
   9  recursive calls        
10713  db block gets           
4128  consistent gets                           
   2  physical reads 
20461776  redo size
 626  bytes sent via SQL*Net to client
 835  bytes received via SQL*Net from client
   4  SQL*Net roundtrips to/from client
   2  sorts (memory)
   0  sorts (disk)

1000000  rows processed

Como podemos ver, se está generando aprox. 19 MB de Redo para insertar en la tabla 1.000.000 de registros. Qué sucedería si necesitamos realizar una carga masiva de datos? Estaríamos consumiendo gran cantidad de Redo.
Para reducir la cantidad de Redo que se genera, podemos usar el hint APPEND.

SQL_9iR2> INSERT /*+ APPEND */ INTO test
2  SELECT level, level
3  FROM dual
4  CONNECT BY level <= 1000000 ;   

1000000 rows created.  

Statistics
---------------------------------------------------              
688  recursive calls              
242  db block gets              
242  consistent gets                    
  0  physical reads    
17120808  redo size
612  bytes sent via SQL*Net to client
849  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  2  sorts (memory)
  0  sorts (disk)

1000000  rows processed

Con el hint APPEND estamos utilizando aprox. 16 MB de Redo. Pero realmente se está utilizando el hint? No hubo una gran diferencia en la generación del Redo. Porque? Esto es debido a que existen 2 condiciones para que se utilice el hint, y debemos cumplir alguna de ellas:

- La base de datos debe estar en modo NOARCHIVELOG.
- La tabla que estamos utilizando debe estar en modo NOLOGGING.

SQL_9iR2> SELECT log_mode from v$database ;

LOG_MODE
------------
ARCHIVELOG

1 row selected.

En la base de datos que estoy utilizando para éste ejemplo estoy utilizando ARCHIVELOG, por lo cual vamos a colocar la tabla en modo NOLOGGING para ver el efecto de utilizar el hint.

SQL_9iR2> ALTER TABLE test NOLOGGING ;

Table altered.

SQL_9iR2> INSERT /*+ APPEND */ INTO test
2  SELECT level, level
3  FROM dual
4  CONNECT BY level <= 1000000 ;   

1000000 rows created.   

Statistics
---------------------------------------------------              
153  recursive calls                 
 18  db block gets                 
 23  consistent gets                    
  1  physical reads        
5944  redo size
614  bytes sent via SQL*Net to client
849  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  7  sorts (memory)
  0  sorts (disk)

1000000  rows processed

Claramente podemos ver que ahora estamos utilizando sólo 5 KB de Redo. Esto nos permite optimizar las operaciones de carga masiva de datos.

NOTA: El APPEND sólo podemos utilizarlo en sentencias del tipo INSERT AS SELECT.

La Clave del Tuning (CDT)

2007-08-23T08:53:00.000-03:00

Cuando comencé con Tuning, ya hace unos años, hubo una pregunta a la cual no le encontraba respuesta. La pregunta era: Cuál es la clave del Tuning?
A medida que iba pasando el tiempo, hubo una sola respuesta que me vino a la cabeza. Los días seguían pasando y a medida que iba adquiriendo experiencia en el Tuning, esa respuesta cada vez me iba convenciendo más... hasta que un día terminó por convencerme.
La respuesta a mi pregunta era muy fácil y podemos resumirla en una simple fórmula:

El tiempo de respuesta es inversamente proporcional al tiempo de pensar. Veamos... El tiempo de respuesta es el tiempo total que demanda cierta ejecución (ej: una consulta). El tiempo de pensar es el tiempo total que dedico para la resolución de cierto problema (ese tiempo total incluye el análisis del problema, la generación de sus posibles soluciones, las pruebas de esas soluciones y la elección de la solución más performante).

La fórmula se resume en lo siguiente: Si le dedicamos a cada problema el "tiempo de pensar" que se merece... estaremos más cerca de lograr un "tiempo de respuesta" más óptimo.

Recuerden: Cada problema es un mundo.

Download Oracle Database 11g Release 1

2007-08-15T12:18:00.000-03:00

Ya se encuentra disponible AQUI la base de datos Oracle 11g Release 1 sólo para Linux x86.

El hermoso Teorema de Pitágoras

2007-08-01T09:41:00.000-03:00

Pueden ver AQUI una de las demostraciones más hermosas que jamas vi del Teorema de Pitágoras. Lean el artículo... seguramente les fascinará como a mi.

WHEN OTHERS THEN NULL ;

2007-07-27T15:58:00.000-03:00

Veo muchas veces a los desarrolladores poniendo en sus códigos...


EXCEPTION
WHEN OTHERS THEN 
    NULL ;

Las programadores que hacen ésto no estan concientes de sus graves consecuencias.
Un desarrollador JAMAS debe poner código de ese tipo. Porque? Por la simple razón que poniendolo, es como si jamas lo hubieramos puesto. Se entiende? No tiene ningún sentido ponerlo porque no cumple ninguna función. Ahh! Si! Cumple con la función de que si tenemos un error durante la ejecución de nuestro código... jamas nos enteramos; y si nos enteramos de que hubo un error es demasiado difícil encontrarlo.

Veamos un muy sencillo ejemplo:


SQL_9iR2> CREATE OR REPLACE PROCEDURE pr_log_errores
  2  (
  3  p_cod_error  IN  VARCHAR2 ,
  4  p_msj_error  IN  VARCHAR2
  5  )
  6  IS
  7  BEGIN
  8      INSERT INTO log_errores
  9      (
 10      fecha ,
 11      cod_error,
 12      msj_error
 13      )
 14      VALUES
 15      (
 16      SYSDATE ,
 17      p_cod_error,
 18      p_msj_error
 19      ) ;
 20      COMMIT ;
 21  EXCEPTION
 22  WHEN OTHERS THEN
 23      NULL ;
 24  END ;
 25  /

Procedure created.

SQL_9iR2> BEGIN
  2      pr_log_errores('ORA-00001','unique constraint violated.') ;
  3  END ;
  4  /

PL/SQL procedure successfully completed.

Como podemos ver, ejecutamos nuestro procedimiento de prueba y terminó perfectamente... pero, realmente terminó bien? Veamos...


SQL_9iR2> SELECT *
  2  FROM log_errores ;

no rows selected

Aha! El procedimiento no insertó en la tabla de logueo el error que le pasamos como parámetro. Pero como puede ser esto posible?


SQL_9iR2> DESC log_errores
 Name               Null?    Type
 ------------------ -------- -------------
 FECHA                       DATE
 COD_ERROR                   VARCHAR2(100)
 MSJ_ERROR                   VARCHAR2(10)

El campo MSJ_ERROR tiene una longitud de 10 caracteres y nosotros queremos introducir una cadena más larga. Nosotros nunca nos enteramos de que hubo un error.
Imaginense si es un proceso Batch crítico que se ejecuta todos los días de forma automática durante la madrugada. Si ocurre algún error... nadie se podría dar cuenta que el proceso en algún punto falló.

Corregimos nuestro procedimiento...


SQL_9iR2> CREATE OR REPLACE PROCEDURE pr_log_errores
  2  (
  3  p_cod_error  IN  VARCHAR2 ,
  4  p_msj_error  IN  VARCHAR2
  5  )
  6  IS
  7  BEGIN
  8      INSERT INTO log_errores
  9      (
 10      fecha ,
 11      cod_error,
 12      msj_error
 13      )
 14      VALUES
 15      (
 16      SYSDATE ,
 17      p_cod_error,
 18      p_msj_error
 19      ) ;
 20      COMMIT ;
 21  EXCEPTION
 22  WHEN OTHERS THEN
 23      RAISE ;
 24  END ;
 25  /

Procedure created.

SQL_9iR2> BEGIN
  2      pr_log_errores('ORA-00001','unique constraint violated.') ;
  3  END ;
  4  /
BEGIN
*
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "IP_UTILS.PR_LOG_ERRORES", line 24
ORA-06512: at line 2

Compulsive Tuning Disorder

2007-07-26T16:37:00.000-03:00

Descubrí AQUI si tenés los síntomas del CTD...

Geeks...

2007-07-26T09:41:00.000-03:00

Muy interesante ARTÍCULO que explica cómo tratar en un ambiente laboral a los Geeks y cuales son sus necesidades.
Darles trabajos desafiantes, importantes y significativos a ésta clase de personas es esencial.

Si sos un Geek o crees que lo sos y estas en un trabajo en el cual sentis que estas desperdiciando mucho de vos, quizás sea hora de pensar en un cambio...

Evitá el uso de Variables Globales

2007-07-17T12:24:00.000-03:00

Veamos un ejemplo:


SQL_9iR2> CREATE OR REPLACE PACKAGE test_pkg
 2  AS
 3      PROCEDURE test_global_variable ;
 4  END ;
 5  /

Package created.

SQL_9iR2> CREATE OR REPLACE PACKAGE BODY test_pkg
 2  AS
 3  variable_globar    NUMBER ;
 4  PROCEDURE mostrar_variable_global ( p_valor  IN  NUMBER )
 5  IS
 6  BEGIN
 7      dbms_output.put_line( 'Valor de la variable global: '||p_valor ) ;
 8      variable_globar := 2 ;
 9      dbms_output.put_line( 'Valor de la variable global: '||p_valor ) ;
10  END ;
11
12  PROCEDURE test_global_variable
13  IS
14  BEGIN
15      variable_globar := 1 ;
16      mostrar_variable_global(variable_globar) ;
17  END ;
18
19  END test_pkg ;
20  /

Package body created.


SQL_9iR2> EXEC test_pkg.test_global_variable ;
Valor de la variable global: 1
Valor de la variable global: 2

PL/SQL procedure successfully completed.

Cómo puede ser ésto posible? Bueno, como los valores IN se pasan por referencia, el valor de la variable global también es pasada al procedimiento mostrar_variable_global por referencia.

Para evitar éste problema con las variables globales tenemos 2 opciones:
1) No usar Variables Globales :)
2) Si estamos forzados a utilizarlas, podemos asignar el contenido de la variable global a una nueva variable y pasar el valor de esa nueva variable como parámetro. Sino, podemos modificar el input del parámetro.

Veamos las 2 opciones implementadas:

- Opción 1:


SQL_9iR2> CREATE OR REPLACE PACKAGE test_pkg
 2  AS
 3  PROCEDURE test_global_variable ;
 4  END test_pkg ;
 5  /

Package created.

SQL_9iR2> CREATE OR REPLACE PACKAGE BODY test_pkg
 2  AS
 3  variable_globar    NUMBER ;
 4  PROCEDURE mostrar_variable_global ( p_valor  IN  NUMBER )
 5  IS
 6  BEGIN
 7      dbms_output.put_line( 'Valor de la variable global: '||p_valor ) ;
 8      variable_globar := 2 ;
 9      dbms_output.put_line( 'Valor de la variable global: '||p_valor ) ;
10  END ;
11
12  PROCEDURE test_global_variable
13  IS
14  l_variable_global_aux    NUMBER ;
15  BEGIN
16      variable_globar := 1 ;
17      l_variable_global_aux := variable_globar ;
18      mostrar_variable_global(l_variable_global_aux) ;
19  END ;
20
21  END test_pkg ;
22  /

Package body created.


SQL_9iR2> EXEC test_pkg.test_global_variable ;
Valor de la variable global: 1
Valor de la variable global: 1

- Opción 2:


SQL_9iR2> CREATE OR REPLACE PACKAGE test_pkg
 2  AS
 3  PROCEDURE test_global_variable ;
 4  END test_pkg ;
 5  /

Package created.

SQL_9iR2> CREATE OR REPLACE PACKAGE BODY test_pkg
 2  AS
 3  variable_globar    NUMBER ;
 4  PROCEDURE mostrar_variable_global ( p_valor  IN  NUMBER )
 5  IS
 6  BEGIN
 7      dbms_output.put_line( 'Valor de la variable global: '||p_valor ) ;
 8      variable_globar := 2 ;
 9      dbms_output.put_line( 'Valor de la variable global: '||p_valor ) ;
10  END ;
11
12  PROCEDURE test_global_variable
13  IS
14  BEGIN
15      variable_globar := 1 ;
16      mostrar_variable_global(variable_globar+0) ;
17  END ;
18
19  END test_pkg ;
20  /

Package body created.


SQL_9iR2> EXEC test_pkg.test_global_variable ;
Valor de la variable global: 1
Valor de la variable global: 1

PL/SQL procedure successfully completed.

The 10g Plan Table

2007-07-17T09:50:00.000-03:00

Muy interesante ARTÍCULO que muestra cómo sacar más provecho de la Plan Table en 10g...

SQL Injection

2007-07-14T13:19:00.000-03:00

SQL Injection es una técnica de ataque a una base de datos. Mediante una aplicación Web, el atacante puede modificar los parámetros que envía desde un formulario hacia la base de datos y de ésta manera, modificar la sentencia SQL que se ejecuta. Podemos ejecutar sentencias DML, DDL... entre otras cosas.

Veamos un ejemplo de SQL Injection en Oracle:

Creamos una tabla para nuestra prueba que contiene usuarios y passwords:


SQL_9iR2> CREATE TABLE login AS
2  SELECT 'user_'||level username, to_char(level*10000) password
3  FROM dual
4  CONNECT BY level <= 5 ;  Table created.  

SQL_9iR2> SELECT *
2  FROM login ;

USERNAME         PASSWORD
---------------- ----------
user_1           10000
user_2           20000
user_3           30000
user_4           40000
user_5           50000

5 rows selected.

Creamos un procedimiento de login que recibe como parámetros el usuario y password de validación. Si los datos son correctos, se devuelven todos los usuarios y passwords de la tabla login, en caso contrario se devuelve una leyenda de error:


SQL_9iR2> CREATE OR REPLACE PROCEDURE pr_login
2  (
3      p_username IN VARCHAR2 ,
4      p_password IN VARCHAR2
5  )
6  IS
7      l_flag     BOOLEAN DEFAULT FALSE ;
8      l_cursor   SYS_REFCURSOR ;
9      l_query    VARCHAR2(4000) ;
10      registro   login%ROWTYPE ;
11  BEGIN
12      l_query := 'SELECT *'
13                 ||' FROM login'
14                 ||' WHERE username = '''||p_username||''''
15                 ||'   AND password = '''||p_password||'''' ;
16
17      dbms_output.put_line('CONSULTA: '||l_query) ;
18
19      OPEN l_cursor FOR l_query ;
20      LOOP
21          FETCH l_cursor INTO registro ;
22          EXIT WHEN l_cursor%NOTFOUND ;
23          dbms_output.put_line('USER : '||registro.username||' 
'||'PASS:'||registro.password) ;
24          IF NOT ( l_flag ) THEN
25              l_flag := TRUE ;
26          END IF ;
27      END LOOP ;
28      CLOSE l_cursor ;
29      IF NOT ( l_flag ) THEN
30          dbms_output.put_line('LOGIN INCORRECTO!') ;
31      END IF ;
32  EXCEPTION
33      WHEN OTHERS THEN
34          dbms_output.put_line('LOGIN INCORRECTO!') ;
35  END ;
36  /

Procedure created.

Bien, ahora vamos a probar nuestro procedimiento de login:


SQL_9iR2> EXEC pr_login('user_2','123456789') ;

CONSULTA: SELECT * FROM login WHERE username = 'user_2'   AND password = '123456789'

LOGIN INCORRECTO!

PL/SQL procedure successfully completed.

El procedimiento anduvo a la perfección! Validó el usuario y password, y como resultan ser incorrectos, nos devolvió un error.
Fijense que en el procedimiento incluimos una linea de código (la nro. 17) que nos va a mostrar la sentencia SQL que estamos ejecutando.

Ahora probemos nuevamente ejecutar el procedimiento de login pero modificando el segundo parámetro:


SQL_9iR2> EXEC pr_login('user_2','123456789'' OR ''x''=''x') ;

CONSULTA: SELECT * FROM login WHERE username = 'user_2'   AND password = '123456789' OR 'x'='x'

USER : user_1 PASS: 10000
USER : user_2 PASS: 20000
USER : user_3 PASS: 30000
USER : user_4 PASS: 40000
USER : user_5 PASS: 50000

PL/SQL procedure successfully completed.

Les presento a SQL Injection! Como acabamos de observar, modificando uno de los parámetros, pudimos lograr modificar la sentencia SQL que se ejecuta en la base de datos y por consiguiente, ver todos los usuarios y passwords de la tabla login.

La única pregunta que queda por hacer es: ¿ Qué tan segura es su aplicación ?

Webcast lanzamiento Oracle Database 11g

2007-07-13T12:33:00.000-03:00

Pueden ver AQUI, los Webcast del lanzamiento de Oracle Database 11g del pasado 11 de Julio.

High Water Mark (HWM)

2007-07-12T12:34:00.000-03:00

Generalmente, cuando se crea una tabla o un índice... se crea un segmento asociado al objeto. El segmento se crea con determinados bloques del data file, pero muy poco espacio es destinado para nuestro uso. Cuando los datos van llenando los bloques asignados al segmento, se van alocando más bloques para que usemos.
A medida que los bloques van siendo alocados, la HWM se posiciona en el último bloque para mostrarnos la cantidad total de bloques alocados hasta el momento y estaban disponibles para ser usados.
Un error común es pensar que a medida que eliminamos datos de los bloques, la HWM se refresca para mostrarnos que estamos utilizando menos bloques; pero la realidad es que la HWM se mantiene siempre apuntando al último bloque alocado.
Sabemos que cuando realizamos un Full Scan, son leídos secuencialmente todos los bloques de la tabla hasta la HWM, por lo cual, debemos tener en claro que aunque eliminemos datos de una tabla, ese espacio "vacío" se va a seguir leyendo, lo cual trae como consecuencia la lectura de bloques innecesarios.

Veamos un ejemplo:


SQL_9iR2> CREATE TABLE emp
2  (id NUMBER , sexo VARCHAR2(1 ) ;

Table created.

SQL_9iR2> INSERT INTO emp
2  SELECT level , 'M'
3  FROM dual
4  CONNECT BY level <= 800000 ; 

800000 rows created.  

SQL_9iR2> INSERT INTO emp
2  SELECT level , 'F'
3  FROM dual
4  CONNECT BY level <= 200000 ; 

200000 rows created.  

SQL_9iR2> ANALYZE TABLE emp COMPUTE STATISTICS ;

Table analyzed.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT sexo
2  FROM emp
3  WHERE id = 900000 ;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=160 Card=10000 Bytes=60000)
1   0   TABLE ACCESS (FULL) OF 'EMP' (Cost=160 Card=10000 Bytes=60000)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   1655  consistent gets
      0  physical reads
      0  redo size
    213  bytes sent via SQL*Net to client
    363  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

SQL_9iR2> DELETE FROM emp
2  WHERE sexo = 'M' ;

800000 rows deleted.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT sexo
2  FROM emp
3  WHERE id = 900000 ;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=160 Card=10000 Bytes=60000)
1   0   TABLE ACCESS (FULL) OF 'EMP' (Cost=160 Card=10000 Bytes=60000)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   1655  consistent gets
    419  physical reads
      0  redo size
    213  bytes sent via SQL*Net to client
    363  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

Como pudimos ver, aunque hayamos eliminados datos de la tabla, seguimos leyendo la misma cantidad de bloques que antes porque la HWM no se refrescó y sigue apuntando al último bloque alocado en el segmento.
Para solucionar éste problema, podemos realizar...

1) TRUNCATE ...
- Si la tabla esta vacia podemos hacer un TRUNCATE para resetear la HWM. Sino, podemos hacer un export de los datos, luego truncar la tabla y realizar un import.
2) ALTER TABLE ... MOVE
- De ésta manera reorganizamos la tabla. Tener en cuenta que luego de ejecutar el ALTER, hay que hacer un REBUILD de todos los índices de la tabla!
3) Dropear y recrear el objeto (export/import)
4) ALTER TABLE ... SHRINK SPACE | SHRINK SPACE COMPACT
- Para 10g en adelante.

Apliquemos a nuestro ejemplo el punto 2...


SQL_9iR2> ALTER TABLE emp MOVE ;

Table altered.

SQL_9iR2> ANALYZE TABLE emp COMPUTE STATISTICS ;

Table analyzed.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT sexo
2  FROM emp
3  WHERE id = 900000 ;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=2000 Bytes=12000)
1   0   TABLE ACCESS (FULL) OF 'EMP' (Cost=33 Card=2000 Bytes=12000)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    335  consistent gets
      0  physical reads
      0  redo size
    213  bytes sent via SQL*Net to client
    363  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

Como vemos, la HWM se refresco y ahora estamos leyendo sólo los bloques que contienen nuestros datos.

Overclocking your brain

2007-07-10T13:15:00.000-03:00

Excelente ARTÍCULO que explica varios ejercicios para optimizar el cerebro.

“I just found out that the brain is like a computer. If that’s true, then there really aren’t any stupid people. Just people running DOS.”

- Anonymous

Modificar el prompt de SQL*Plus

2007-07-10T11:30:00.000-03:00

Cuando estamos trabajando con SQL*Plus, solemos trabajar con varias ventanas abiertas cada una conectada a una instancia diferente. Esto suele ser un problema ya que podemos confundirnos de instancia y trabajar en una instancia diferente. Cuando iniciamos SQL*Plus, Oracle busca un archivo llamado "login.sql" dentro del path definido en la variable ORACLE_PATH, y si no lo encuentra, busca el archivo "glogin.sql" (global login).
Podemos modificar el prompt de SQL*Plus para identificar con facilidad el esquema e instancia en donde nos encontramos.
Para realizar ésta modificación, abrimos el archivo "login.sql" o "glogin.sql" y colocamos lo siguiente:


set term off
set serveroutput on size 1000000 format wrapped
set linesize 70
set trimspool on
set pagesize 9999
set heading on
set underline -

define sql_prompt=idle
define sql_prompt = 'not connected'

column id_plus_exp FOR 990 HEADING i
column parent_id_plus_exp FOR 990 HEADING p
column plan_plus_exp FOR a60
column object_node_plus_exp FOR a8
column other_tag_plus_exp FOR a29
column other_plus_exp FOR a44

column user_sid new_value sql_prompt

select lower(user)||'@'||'&_CONNECT_IDENTIFIER' user_sid 
from dual;

set sqlprompt '&sql_prompt> '

set timing on
set term on

Nota: Este script es el que tengo actualmente en mi cliente. Se puede modificar a gusto de cada uno.

El prompt quedará de la siguiente manera: "user@instancia>"

Fast Dual

2007-07-10T11:24:00.001-03:00

Muchas aplicaciones ejecutan millones de veces consultas a la tabla DUAL. En versiones anteriores a la 10g, Oracle realizaba 3 LIO's (Logical I/O) por cada vez que se consultaba a la tabla. Esto es muy costoso en ambientes en los cuales se hace referencia a esta tabla todo el tiempo.
Para solucionar este problema, podemos modificar la tabla DUAL y hacer referencia a la tabla X$DUAL. Esta tabla es una tabla virtual, lo cual no implica ningún LIO.

Ejemplo:


$ sqlplus "/as sysdba"

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

SQL_9iR2> SELECT * FROM dual ;


Statistics
----------------------------------------------------------
       0  recursive calls
       0  db block gets
       3  consistent gets
       0  physical reads
       0  redo size
     402  bytes sent via SQL*Net to client
     504  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed


SQL_9iR2> SELECT * FROM x$dual ;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0A8A90D8          0          1 X


SQL_9iR2> CREATE VIEW fast_dual AS
2  SELECT 'X' dummy FROM x$dual ;

View created.

SQL_9iR2> GRANT SELECT ON fast_dual TO PUBLIC ;

Grant succeeded.



SQL_9iR2> CONN algun_esquema/algun_password ;

SQL_algun_esquema> CREATE SYNONYM dual FOR sys.fast_dual ;

Synonym created.

SQL_algun_esquema> SET AUTOTRACE TRACEONLY STATISTICS
SQL_algun_esquema> SELECT * FROM dual ;

1 row selected.

Statistics
---------------------------------------------------
       0  recursive calls
       0  db block gets
       0  consistent gets
       0  physical reads
       0  redo size
     326  bytes sent via SQL*Net to client
     498  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed

En 10gR2 se soluciona este problema ya que se modificó la tabla DUAL existente y ahora se hace uso de la tabla virtual X$DUAL.

Hard Parse, Soft Parse & “Softer” Soft Parse

2007-07-10T09:38:00.000-03:00

Procesamiento de una consulta:

1) Validación Sintáctica
2) Validación Semántica
3) Optimización
4) Generación del QEP (Query Execution Plan)
5) Ejecución del QEP (Query Execution Plan)

El punto 1 al 4 forma parte del Parseo de la consulta, mientras que el punto 5 es la propia ejecución.

Cuando ejecutamos una consulta, siempre se realizan, como mínimo, los pasos 1 y 2. Luego de ejecutarse estos pasos, Oracle transforma la consulta en un valor hash y la envía a la Shared Pool y en la Library Cache se busca si existe alguna consulta con el mismo valor hash (si alguna sesión ya la utilizó en algun momento). En caso de que exista, se compara el texto de la consulta con la que se encontró en la Library Cache para validar si son exactamente iguales (este paso adicional se realiza porque puede llegar a haber varias consultas con el mismo valor hash); en caso de que lo sean, se procede a ejecutar esa consulta. Esto es lo que llamamos un Soft Parse. Si la consulta no existe, Oracle realiza los pasos 3 y 4. Esto es conocido como un Hard Parse. El Hard Parse es muy costoso para Oracle Server ya que implica realizar varios latches (loqueos) en la SGA y consume mucha CPU.
Como bien sabemos, cada consulta que ejecutamos implica la utilización de un cursor (un cursor es un espacio de memoria destinado a la ejecución de nuestra consulta). Lo ideal, es que nuestra aplicación abra los cursores que vaya a utilizar, ejecute las sentencias x veces y luego los cierre. Muchas aplicaciones como Forms no suelen ejecutar los cursores de esta forma, lo que implica que no podamos reutilizar los cursores y siempre tengamos que abrir nuevamente los que ya ejecutamos.
Para reducir éste problema, podemos utilizar el parámetro de inicialización SESSION_CACHED_CURSORS que nos va a permitir realizar un "Softer" Soft Parse. Si setemos el parámetro en 100, Oracle mantendrá 100 cursores abiertos para que los podamos reutilizar y evitarnos tener que abrirlos cada vez. Este espacio de memoria destinado al manejo de cursores, se mantiene con una lista LRU.
Oracle recomiendo que el parámetro se setee en una primera instancia en 50 e ir monitoreandolo para verificar si conviene incrementar su valor. Este parámetro debe setearse considerando el valor de OPEN_CURSORS.

select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
       to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
       to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from ( select value calls from v$sysstat where name = 'parse count (total)' ),
     ( select value hard from v$sysstat where name = 'parse count (hard)' ),
     ( select value sess from v$sysstat where name = 'session cursor cache hits' ) ;

CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
----------------- ----------- -----------
        59.11%      39.49%       1.39%


select
'session_cached_cursors'  parameter,
lpad(value, 5)  value,
decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
from
( select
    max(s.value)  used
  from
    sys.v_$statname  n,
    sys.v_$sesstat  s
  where
    n.name = 'session cursor cache count' and
    s.statistic# = n.statistic#
),
( select
    value
  from
    sys.v_$parameter
  where
    name = 'session_cached_cursors'
)
union all
select
'open_cursors',
lpad(value, 5),
to_char(100 * used / value,  '990') || '%'
from
( select
    max(sum(s.value))  used
  from
    sys.v_$statname  n,
    sys.v_$sesstat  s
  where
    n.name in ('opened cursors current', 'session cursor cache count') and
    s.statistic# = n.statistic#
  group by
    s.sid
),
( select
    value
  from
    sys.v_$parameter
  where
    name = 'open_cursors'
) ;

PARAMETER              VALUE           USAGE
---------------------- --------------- -----
session_cached_cursors   100            100%
open_cursors             300             57%

Si el valor del SESSION_CACHED_CURSORS se encuentra en el 100%, deberíamos incrementar el valor del parámetro con normalidad.

How To Be A Programmer

2007-07-09T23:46:00.000-03:00

Excelente GUÍA de cómo se un programador. Recomiendo leerla.
Me causó gracia la frase... "Debugging is fun, because it begins with a mystery".

Oracle Database 11g

2007-07-09T23:17:00.000-03:00

Oracle anunció el lanzamiento de Oracle Database 11g el día 11 de Julio de 2007 en la ciudad de Nueva York.

Nieva en Buenos Aires! histórico!!!

2007-07-09T19:59:00.000-03:00

Qué mejor manera de celebrar el día de la Independencia que con una nevada???

La última vez que ocurrió fue en junio de 1918. El fenómeno se registra a raíz de la irrupción de aire polar en los niveles medios de la atmósfera y la temperatura promedio. La térmica llegó 1,2 grados bajo grado. "Continuará hasta la madrugada de mañana y puede intensificarse durante la noche", dijo el Servicio Meteorológico.

fuente: Clarin.com