Estado de los cursores

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

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

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)

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

Ya se encuentra disponible AQUI la base de datos Oracle 11g Release 1 sólo para Linux x86.

El hermoso Teorema de Pitágoras

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.