Uso de índices basados en funciones con conversiones TIMESTAMP

Cuando un filtro por una columna se realiza mediante una función, el optimizador no utiliza los índices de esa columna ya que la función «transforma» los valores y hace que el índice no resulte válido. Para sortear ese obstáculo, Oracle dispone de los índices basados en funciones.

En ocasiones es el propio motor quien añade funciones a los filtros y eso puede volvernos un poco locos intentando averiguar por qué Oracle no usa los índices de la columna. Por ejemplo:

SQL> create table test (id number, dt date);

Tabla creada.

SQL> insert into test select rownum, to_date(’07/01/2014 13:00′,’DD/MM/YYYY HH24:MI’)+rownum/144 from dba_objects;

94874 filas creadas.

SQL>  select * from test where dt

        ID DT
———- ——————–
         1 07-ENE-2014 13:10:00
         2 07-ENE-2014 13:20:00
         3 07-ENE-2014 13:30:00
         4 07-ENE-2014 13:40:00
         5 07-ENE-2014 13:50:00

SQL> create index idx_test_fecha on test(dt);

Índice creado.


La tabla tiene 94874 filas numeradas con fechas a partir de 7 de enero de 2014 13:10 para cada 10 minutos consecutivamente. El índice creado sobre la columna de fecha no servirá si en el filtro de una sentencia SELECT se compara con un tipo de dato TIMESTAMP (pues causaría una conversión implícita de los valores de la columna DT de tipo fecha):


SQL> explain plan for select * from test where dt

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
—————————————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     3 |    66 |    71   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     3 |    66 |    71   (8)| 00:00:01 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
—————————————————————————-

   1 – filter(INTERNAL_FUNCTION(«DT»)
              14:00:00′))

Note
—–
   – dynamic sampling used for this statement (level=2)

Esta conversión implícita nos lleva al FULL SCAN de la tabla TEST.

Para este caso, además, crear un índice basado en funciones tampoco serviría, pues la función de conversión TO_TIMESTAMP no es determinista. Las funciones deterministas devuelven siempre el mismo valor a un determinado paso de parámetros, pero TO_TIMESTAMP se apoya en las variables de NLS locales como el timezone.

SQL> create index idx_fb_test_fecha on test(to_timestamp(dt));
create index idx_fb_test_fecha on test(to_timestamp(dt))
                                       *
ERROR en línea 1:
ORA-01743: sólo se pueden indexar funciones puras

Supongamos que nuestra base de datos es local y siempre se consultará con TIMESTAMP sobre una misma zona horaria. En ese caso, podemos crear nuestra propia función TO_TIMESTAMP determinista e intentar crear el índice sobre ésta (y que las consultas incluyan nuestra función, claro!).

SQL> create or replace function to_timestamp_determinista(fecha timestamp) return timestamp deterministic is
  2  begin
  3     return to_timestamp(fecha);
  4  end;
  5  /

Función creada.

SQL> create index idx_fb_test_fecha on test(to_timestamp_determinista(dt));


Índice creado.

Ahora ya es posible que nuestra consulta pueda utilizar el índice basado en función para convertir a TIMESTAMP de forma determinística, y beneficiarnos del uso del índice para recuperar las 7 filas entre más de 94.000.
SQL> select * from test
  2  where to_timestamp_determinista(dt)<
  3        to_timestamp_determinista(to_date(’07/01/2014 14:00′,’DD/MM/YYYY HH24:MI’));

        ID DT
———- ——————–
         1 07-ENE-2014 13:10:00
         2 07-ENE-2014 13:20:00
         3 07-ENE-2014 13:30:00
         4 07-ENE-2014 13:40:00
         5 07-ENE-2014 13:50:00

SQL> explain plan for
  2  select * from test
  3  where to_timestamp_determinista(dt)<
  4        to_timestamp_determinista(to_date(’07/01/2014 14:00′,’DD/MM/YYYY HH24:MI’));

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
—————————————————————–
Plan hash value: 2253730852

————————————————————————————————-
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————-
|   0 | SELECT STATEMENT            |                   |  5378 |   231K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST              |  5378 |   231K|     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_FB_TEST_FECHA |   968 |       |     4   (0)| 00:00:01 |
————————————————————————————————-

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
—————————————————————————–

   2 – access(«SYS».»TO_TIMESTAMP_DETERMINISTA»(INTERNAL_FUNCTION(«DT»))<«TO_TIMESTAMP_DETERMINISTA»(TIMESTAMP’ 2014-01-07 14:00:00′))

Note
—–
   – dynamic sampling used for this statement (level=2)

19 filas seleccionadas.

¿Te ha parecido interesante esta entrada? 
Si es así, échale un ojo a mi libro sobre Optimización SQL en Oracle.

Optimización SQL en Oracle – Ya a la venta!

El libro ya está disponible en todas las tiendas Amazon (.com, .co.uk, .es, .de, etc.)

¡Muchísimas gracias! ¡Espero que os guste y os sea útil!

Amazon.es              Amazon.com

Podéis echarle un ojo al interior aquí (mejor en pantalla completa):

Optimización SQL en Oracle. Últimos retoques.

¡Por fin!

El libro «Optimización SQL en Oracle» está terminado. 

En cuanto finalice el diseño de la portada y la contraportada (si los de Amazon no ponen impedimento) ya estará disponible para comprar tanto en amazon.com como en amazon.eu.

El libro ha contado con dos revisores técnicos de peso, que le han sacado punta a todo y han sentado a debate tanto los ejemplos, imágenes y conceptos expuestos, como la filosofía de trabajo del libro. Son Arturo Gutierrez y Jetro Marco. Gracias a ellos el libro ha pasado de unas 316 páginas a las más de 420 actuales. 
El índice finalmente ha quedado así:
Al final abordamos todo desde las tripas, tanto el tratamiento de las bind variables como el funcionamiento del Bind Variable Peeking, el Adaptive Cursor Sharing, el  como SQL Profiles, SQL Baselines, reescritura de vistas materializadas, el paralelismo, particionamiento, así como trazas y planes de ejecución de todo.
El libro está lleno de técnicas, herramientas, base de conocimiento y nuestro aporte profesional sobre cómo optimizar cualquier SQL que de un mal rendimiento. Nos metemos a contar qué pasa en particular con los entornos data warehouse (¿Se puede ejecutar PL/SQL masivo en un entorno data warehouse? … pues hay un capítulo que trata de cómo hacerlo a un rendimiento brutal!), desmontar mitos, descubrir la realizad subyacente de trucos como lanzar INSERTS con APPEND y cosas así. ¿Por qué Oracle dice que seguirá un plan de ejecución y luego decide utilizar otro? ¿cómo lidiar con todo esto sin volvernos locos????
Hemos destripado todas las hints, poniéndolas a prueba. Hemos buscado ejemplos de SQL ineficiente y de múltiples ejecuciones de un mismo código para rizar el rizo y comprender qué sucede en el CBO, cómo se estima la cardinalidad de las operaciones, por qué (a veces) Oracle se equivoca y por qué a veces somos nosotros los que no entendemos al motor.
Además, la bbdd está disponible para descargar gratuitamente aquí, y el SQL del libro (próximamente)!

Manejo de subconsultas en la cláusula SELECT. Parte II

(Continúa de Parte I)

Este post podría llamarse «La paradoja del increíble coste menguante» como si de un relato de G. K. Chesterton se tratara.

Si alguien pensó por la lectura de la parte I de este post que las subconsultas en la cláusula SELECT mejoraban el rendimiento, pues permitían reproducir consultas en estrella sin necesidad de tener un modelo en estrella, ni dimensiones ni jerarquías, está al borde de cometer un grave error.
El optimizador ignora los costes de combinación de las subconsultas en la cláusula SELECT, contando únicamente con el coste de acceso a los objetos de esa subconsulta. Esto sucede incluso en versión Oracle11gR2.
Como ejemplo sirva la siguiente consulta formulada sobre VUELOS (57.711 filas), RESERVAS (171.113 filas) y CLIENTES (9999 filas).

Consulta de reservas, con datos de vuelos y clientes expresado con dos joins

select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
    from vuelos, reservas, clientes
    where vuelos.id_vuelo=reservas.vue_id_vuelo
      and reservas.cli_nif=clientes.nif;

Consulta de reservas, con datos de vuelos y clientes expresado con una join y una subconsulta en la cláusula SELECT

select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos
       where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
    clientes.apellidos
    from reservas, clientes
    where reservas.cli_nif=clientes.nif;

Consulta de reservas, con datos de vuelos y clientes expresado con dos subconsultas en la cláusula SELECT

select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos   
       where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
     (select clientes.apellidos    from clientes 
       where reservas.cli_nif=clientes.nif) cliente
    from reservas;
Los correspondientes planes de ejecución parecen evidenciar lo mencionado anteriormente: el optimizador de costes no es capaz de evaluar el impacto de la combinación de elementos de la consulta principal con los de las subconsultas en la cláusula SELECT. Por este motivo, los costes de los planes de ejecución cada vez son inferiores.

Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con dos joins con el plan de ejecución asociado y la traza de AUTOTRACE

SQL> select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
  2      from vuelos, reservas, clientes
  3      where vuelos.id_vuelo=reservas.vue_id_vuelo
  4        and reservas.cli_nif=clientes.nif;
171113 filas seleccionadas.
Transcurrido: 00:00:01.54
Plan de Ejecución
———————————————————-
Plan hash value: 858327892
—————————————————————————————-
| Id  | Operation           | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT    |          |   171K|    13M|       |   904   (2)| 00:00:11 |
|*  1 |  HASH JOIN          |          |   171K|    13M|       |   904   (2)| 00:00:11 |
|   2 |   TABLE ACCESS FULL | CLIENTES |  9999 |   361K|       |    27   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |          |   171K|  7686K|  1528K|   875   (2)| 00:00:11 |
|   4 |    TABLE ACCESS FULL| VUELOS   | 57711 |   845K|       |   137   (1)| 00:00:02 |
|   5 |    TABLE ACCESS FULL| RESERVAS |   171K|  5180K|       |   311   (2)| 00:00:04 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   1 – access(«RESERVAS».»CLI_NIF»=»CLIENTES».»NIF»)
   3 – access(«VUELOS».»ID_VUELO»=»RESERVAS».»VUE_ID_VUELO»)
Estadísticas
———————————————————-
         15  recursive calls
          0  db block gets
      13013  consistent gets
         96  physical reads
          0  redo size
    7835592  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed
A grandes rasgos, el resumen de la ejecución puede ser una lectura de 13.013 bloques en memoria, un tiempo de ejecución de 1 minuto y 54 segundos y un coste de 904.

Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con una join y una subconsulta en la cláusula SELECT con el plan de ejecución asociado y la traza de AUTOTRACE

SQL> select reservas.id_reserva, reservas.importe,
  2     (select vuelos.detalles from vuelos
  3         where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
  4      clientes.apellidos
  5      from reservas, clientes
  6      where reservas.cli_nif=clientes.nif;
171113 filas seleccionadas.
Transcurrido: 00:00:02.40
Plan de Ejecución
———————————————————-
Plan hash value: 402988295
—————————————————————————————-
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT            |          |   171K|    11M|   340   (2)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| VUELOS   |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | VUE_PK   |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN                  |          |   171K|    11M|   340   (2)| 00:00:05 |
|   4 |   TABLE ACCESS FULL         | CLIENTES |  9999 |   361K|    27   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL         | RESERVAS |   171K|  5180K|   311   (2)| 00:00:04 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – access(«VUELOS».»ID_VUELO»=:B1)
   3 – access(«RESERVAS».»CLI_NIF»=»CLIENTES».»NIF»)
Estadísticas
———————————————————-
         15  recursive calls
          0  db block gets
     374003  consistent gets
          0  physical reads
          0  redo size
    7835589  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed
En esta ejecución, el número de bloques leídos en memoria ha aumentado a 374.003 y el tiempo de ejecución ha aumentado a 2 minutos 40 segundos. Sin embargo el coste de la ejecución se ha reducido a 340 (menos de la mitad). El número de bytes estimado como total de la ejecución también se estima mejorado: de 13 millones a 11 millones.

Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con dos subconsultas en la cláusula SELECT con el plan de ejecución asociado y la traza de AUTOTRACE
SQL> select reservas.id_reserva, reservas.importe,
  2       (select vuelos.detalles from vuelos   
  3          where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
  4       (select clientes.apellidos    from clientes 
  5          where reservas.cli_nif=clientes.nif) cliente
  6      from reservas;
171113 filas seleccionadas.
Transcurrido: 00:00:02.39
Plan de Ejecución
———————————————————-
Plan hash value: 465102819
—————————————————————————————-
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT            |          |   171K|  5180K|   311   (2)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| VUELOS   |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | VUE_PK   |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID| CLIENTES |     1 |    37 |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN         | CLI_PK   |     1 |       |     1   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL          | RESERVAS |   171K|  5180K|   311   (2)| 00:00:04 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – access(«VUELOS».»ID_VUELO»=:B1)
   4 – access(«CLIENTES».»NIF»=:B1)
Estadísticas
———————————————————-
         15  recursive calls
          0  db block gets
     406374  consistent gets
          0  physical reads
          0  redo size
    7835587  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed
En este caso, el tiempo de ejecución es prácticamente el mismo, mientras que el coste se muestra aun mejor que el de la ejecución anterior (340 anteriores frente a 311) pero el número de bloques leídos en memoria aumenta (374.003 anteriores frente a 406.374).

Las trazas generadas por la utilidad tkprof vienen a confirmar prácticamente lo mostrado en la traza de autotrace.

Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con dos joins

select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
    from vuelos, reservas, clientes
    where vuelos.id_vuelo=reservas.vue_id_vuelo
      and reservas.cli_nif=clientes.nif
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    11409      0.39       0.51         96      13009          0      171113
——- ——  ——– ———- ———- ———- ———-  ———-
total    11411      0.39       0.52         96      13009          0      171113

Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con una join y una subconsulta en la cláusula SELECT

select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
    clientes.apellidos
    from reservas, clientes
    where reservas.cli_nif=clientes.nif
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    11409      1.26       1.27          0     373999          0      171113
——- ——  ——– ———- ———- ———- ———-  ———-
total    11411      1.27       1.27          0     373999          0      171113

Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con dos subconsultas en la cláusula SELECT

select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos   
       where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
     (select clientes.apellidos    from clientes 
       where reservas.cli_nif=clientes.nif) cliente
    from reservas
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    11409      1.32       1.24          0     406370          0      171113
——- ——  ——– ———- ———- ———- ———-  ———-
total    11411      1.32       1.24          0     406370          0      171113
En las dos ejecuciones con subconsultas en la cláusula SELECT se aprecia, además, el aumento de tiempo de CPU por el mayor número de bloques a procesar en memoria.
Cuidado, por tanto, con las subconsultas expresadas a ese nivel de ejecución, pues el optimizador no evalua sus pesos correctamente, al quedar fuera del estudio de accesos y combinaciones entre tablas, mediante joins y filtros convencionales. Los resultados expresados por los planes de ejecución de su estimación en coste pueden confundir, ya que muestran costes mejores sobre ejecuciones claramente más ineficientes.

Manejo de subconsultas en la cláusula SELECT. Parte I.

El motor SQL de Oracle es muy flexible. Se permite el uso de subconsultas en la cláusula WHERE, y HAVING, así como directamente en la cláusula FROM (se tratan como vistas) y también como forma de resolver una columna en el conjunto de resultados en la cláusula SELECT.
Lo curioso es que, dado que el formateo de filas se hace al final, de algún modo el optimizador ignora el coste de combinación de las tablas de la subconsulta, pues las trata una vez ha procesado la sentencia.
Por ejemplo, la consulta de clientes con su identificación de sexo y estado civil.
SQL> set autotrace traceonly
SQL> select nif, cn_sexo sexo, cn_ec estado
  2  from clientes, sexos, estadosciviles
  3  where clientes.ec_id_ec=estadosciviles.id_ec
  4    and clientes.sex_id_sexo=sexos.id_sexo;
9999 filas seleccionadas.
Plan de Ejecución
———————————————————-
Plan hash value: 3779357754
————————————————————————————————
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————
|   0 | SELECT STATEMENT              |                | 10001 |   341K|    34   (6)| 00:00:01 |
|*  1 |  HASH JOIN                    |                | 10001 |   341K|    34   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL           | ESTADOSCIVILES |     5 |    55 |     3   (0)| 00:00:01 |
|   3 |   MERGE JOIN                  |                | 10000 |   234K|    31   (7)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| SEXOS          |     2 |    16 |     2   (0)| 00:00:01 |
|   5 |     INDEX FULL SCAN           | SEX_PK         |     2 |       |     1   (0)| 00:00:01 |
|*  6 |    SORT JOIN                  |                |  9999 |   156K|    29   (7)| 00:00:01 |
|   7 |     TABLE ACCESS FULL         | CLIENTES       |  9999 |   156K|    27   (0)| 00:00:01 |
————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
   1 – access(«CLIENTES».»EC_ID_EC»=»ESTADOSCIVILES».»ID_EC»)
   6 – access(«CLIENTES».»SEX_ID_SEXO»=»SEXOS».»ID_SEXO»)
       filter(«CLIENTES».»SEX_ID_SEXO»=»SEXOS».»ID_SEXO»)
Estadísticas
———————————————————-
          1  recursive calls
          0  db block gets
        108  consistent gets
          1  physical reads
          0  redo size
     346739  bytes sent via SQL*Net to client
       7846  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       9999  rows processed
La lectura de 108 bloques en memoria corresponde mayoritariamente a los bloques de la tabla CLIENTES, al índice de clave primaria de la tabla SEXOS y al acceso completo por FULL SCAN de la tabla ESTADOSCIVILES. El coste se tasa en 34.
Esta misma consulta, expresando la resolución del valor de sexo y estado civil del cliente directamente en las columnas de resultado, suponen un plan de ejecución similar, pero tasado ligeramente inferior.
SQL> select nif,
  2         (select cn_sexo from sexos where id_sexo=clientes.sex_id_sexo) sexo,
  3         (select cn_ec from estadosciviles where id_ec=clientes.ec_id_ec) estado
  4  from clientes;
9999 filas seleccionadas.
Transcurrido: 00:00:00.07
Plan de Ejecución
———————————————————-
Plan hash value: 3745735041
———————————————————————————————-
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————-
|   0 | SELECT STATEMENT            |                |  9999 |   156K|    27   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SEXOS          |     1 |     8 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SEX_PK         |     1 |       |     0   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID| ESTADOSCIVILES |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN         | EC_PK          |     1 |       |     0   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL          | CLIENTES       |  9999 |   156K|    27   (0)| 00:00:01 |
———————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – access(«ID_SEXO»=:B1)
   4 – access(«ID_EC»=:B1)
Estadísticas
———————————————————-
          1  recursive calls
          0  db block gets
        766  consistent gets
          0  physical reads
          0  redo size
     380260  bytes sent via SQL*Net to client
       7846  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9999  rows processed
Prácticamente, los costes de acceso y gestión de la tabla SEXOS y ESTADOSCIVILES parecen omitirse en el cálculo (el anterior plan medía 34, menos 3 de acceso a cada tabla y 1 de acceso al índice), pero el volumen total de bloques leídos en memoria se multiplica por siete.

Traza de ejecución con uso de joins

select nif, cn_sexo sexo, cn_ec estado
from clientes, sexos, estadosciviles
where clientes.ec_id_ec=estadosciviles.id_ec
  and clientes.sex_id_sexo=sexos.id_sexo
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      668      0.01       0.04          1        108          0        9999
——- ——  ——– ———- ———- ———- ———-  ———-
total      670      0.01       0.04          1        108          0        9999
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91  (VUELOS)
Number of plan statistics captured: 1
Rows     Execution Plan
——-  —————————————————
      0  SELECT STATEMENT   MODE: ALL_ROWS
   9999   HASH JOIN
      5    TABLE ACCESS   MODE: ANALYZED (FULL) OF ‘ESTADOSCIVILES’  (TABLE)
   9999    MERGE JOIN
      2     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘SEXOS’ (TABLE)
      2      INDEX   MODE: ANALYZED (FULL SCAN) OF ‘SEX_PK’ (INDEX (UNIQUE))
   9999     SORT (JOIN)
   9999      TABLE ACCESS   MODE: ANALYZED (FULL) OF ‘CLIENTES’ (TABLE)

Traza de ejecución con subconsultas en la cláusula SELECT

select nif,
       (select cn_sexo from sexos where id_sexo=clientes.sex_id_sexo) sexo,
       (select cn_ec from estadosciviles where id_ec=clientes.ec_id_ec) estado
from clientes
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      668      0.00       0.01          0        766          0        9999
——- ——  ——– ———- ———- ———- ———-  ———-
total      670      0.00       0.01          0        766          0        9999
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91  (VUELOS)
Number of plan statistics captured: 1
Rows     Execution Plan
——-  —————————————————
      0  SELECT STATEMENT   MODE: ALL_ROWS
      2   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘SEXOS’ (TABLE)
      2    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF ‘SEX_PK’ (INDEX (UNIQUE))
      5   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘ESTADOSCIVILES’ (TABLE)
      5    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF ‘EC_PK’ (INDEX (UNIQUE) )
   9999   TABLE ACCESS   MODE: ANALYZED (FULL) OF ‘CLIENTES’ (TABLE)

La ejecución mediante subconsultas parece indicar al optimizador que no es necesario ordenar la tabla CLIENTES para facilitar las joins, y que el obtener por cada fila desde memoria las filas correspondientes al sexo y al estado civil resulta óptimo.
Es algo muy parecido a una ejecución en estrella. La diferencia está en el cálculo de un hash para resolver los valores de sexo y estado civil, o el acceso directo a los bloques. Aunque la segunda ejecución parezca ejecutarse en menos tiempo, hay que tener en cuenta el alto consumo de bloques en memoria, cosa que no sucedería en una ejecución en estrella. 
En este caso, la ejecución con subconsultas en la cláusula SELECT sustituyendo las joins ha supuesto una mejora del rendimiento. En la parte II publicaré un caso completamente opuesto. Mientras que los costes en el plan de ejecución aparentemente van siendo cada vez menores, el rendimiento real de las ejecuciones resulta cada vez peor.

Optimización SQL en Oracle. En venta, próximamente!

En breve estará a la venta mi libro «Optimización SQL en Oracle». 
Durante los últimos dos años he estado escribiendo este libro que resume, a mi modo de ver, todo lo que un administrador o programador debería conocer para optimizar código SQL.
En él describo cómo funciona el optimizador y cómo se comporta el servidor para escoger los mejores planes de ejecución, los aspectos a considerar para crear tablas  de diferentes tipos (tablas IOT, clusters, tablas particionadas, etc.) y lo mismo relativo a los índices. Herramientas para optimizar SQL, desde asesores a las herramientas «manuales» como explain plan, tkprof, autotrace, generación de trazas, análisis de AWR, etc.
Además, también dedico un apartado a los entornos datawarehouse, a optimización SQL de código ineficiente con casos prácticos resueltos, y un glosario completo de hints con ejemplos de su uso y «maluso», y sus consecuencias para el rendimiento.
Este libro responde preguntas y cuestiones habituales como el motivo por qué no siempre es eficiente acceder a las tablas usando índices, escenarios ineficientes, usos incorrectos de tipos de datos y sus consecuencias en la optimización, uso correcto del paralelismo, el particionamiento, las vistas materializadas, jerarquías, dimensiones, consecuencias de usar NOLOGGING, como tratar subconsultas, uso de IN y EXISTS, DISTINCT, ordenaciones, etc.
El esquema del libro es el siguiente:
Por el momento está en fase BETA, pendiente de revisión técnica. Para esta revisión cuento con dos administradores de los más fuertes de España, y vamos a asegurarnos que en las más de 300 páginas no se nos escapa un error.
Me gustaría decir, como los de Valve, «When it’s done, it’s done» como fecha de publicación, pero espero que en cosa de un par de meses pueda estar disponible a la venta.
Estoy contento porque se trata del primer libro en español que trata exclusivamente de optimización SQL y todo su universo. Muchos libros (principalmente en inglés) tratan de aspectos del rendimiento, sobre todo del motor (memoria, procesos) o se centran exclusivamente en administración o programación, pero éste es el primer libro que conozco absolutamente específico, en español, con ejemplos en español, tablas con nombres en cristiano (vuelos, reservas, etc.), sin ser una traducción de una obra en inglés o un copia/pega de partes de la documentación de Oracle.
Yo estoy satisfecho del resultado, y espero que pueda ser de utilidad. Estoy seguro de que incluso los usuarios más avanzados se sorprenderán aprendiendo cosas nuevas, o redefiniendo conceptos, o encontrando una forma práctica y accesible de resumir las funcionalidades y componentes que afectan a la eficiencia del servidor de base de datos.
Os dejo unas imágenes del libro, en fase BETA, listo para revisarlo y corregirlo antes de sacarlo a la luz.


Funciones Analíticas.

Hay veces en las que encuentro consultas SQL a las que llamo «query ataque de pánico», porque puedo imaginarme el sufrimiento del que la programó. Con mucha frecuencia, este tipo de consultas aparecen en casos en las que una función analítica hubiera resuelto toda la complejidad del problema.

Al detalle. Una consulta del tipo «Datos de cliente con la fecha del primer contrato, fecha de la primera cancelación de contrato, fecha del último contrato contratado, fecha de…» suele consultarse con una subconsulta para cada «fecha de…».

Éste ejemplo, o el típico «Los tres contratos más recientes, las cinco últimas cancelaciones, etc.» siempre hacen que los programadores realicen una subconsulta por cada una de las condiciones… y otra y otra y al final el rendimiento se incrementa tanto de consultar varias veces la misma tabla.

…evidentemente, la consulta SQL se ha hecho tan vasta que resulta muy complicado mantenerla.

Para esta casuística, las funciones analíticas se aplican a un subconjunto de registros, por lo que Oracle, para gestionarlo correctamente, crea una ventana SQL intermedia para reagrupar una y otra vez los resultados de una consulta. Así, dado el anterior ejemplo, Oracle tomaría todos los contratos de ese cliente y los agruparía para cada columna de resultados: el primer contrato contratado, el primer cancelado, el último contrato de alta, etc. sin necesidad de consultar una y otra vez la tabla de contratos.

Las funciones analíticas tienen la siguiente sintaxis (no es la sintaxis completa).

FUNCIÓN_ANALITICA(campo)
OVER (PARTITION BY campo_agr1, campo_agr2
ORDER BY campo_ord1 NULLS LAST)

Las funciones analíticas están descritas en la documentación de Oracle y entre éstas, por ejemplo, se hallan MAX(), MIN(), RANK(), etc.

Un ejemplo de su uso sería, por ejemplo, intentar corregir esta consulta:


SELECT     a.ID_FACTURA,                                  
           a.FALINEA_AUX – b.minCount + 1 ID_FALINEA,     
           a.ID_CLIENT,                                   
           a.ID_COMPTEFACT,                               
           a.PRODUCT_ID,                                  
           a.ID_PRCATPRODUCTE,                            
           a.DS_PRNUMSERVEI,                              
           a.ID_FACONCEPTE,                               
           a.DT_FAFACTURACIO,                             
           a.NUM_FAIMPORTCONCEPTE,                        
           a.PRODUCT_LABEL,                               
           a.DT_MOVIMENT,                                 
           a.FG_TIPUSOPERACIO,                            
           a.asset_id,                                    
           a.PRODUCT_ATTR_VALUE                           
FROM vw_ci_linia_factura_tmp a,                       
     (select t.id_factura,                                
             t.dt_fafacturacio,                           
             min(t.falinea_aux) minCount                  
      from vw_ci_linia_factura_tmp t                  
      group by t.id_factura,                              
               t.dt_fafacturacio                          
     ) b                                                  
WHERE a.id_factura = b.id_factura                         
ORDER BY a.id_factura, a.FALINEA_AUX – b.minCount + 1 ASC;

En este caso, la necesidad de obtener la primera línea de factura para cada linea de factura, parece que obliga a cargar dos veces la vista (y su conjunto de tablas) en memoria y hacer una JOIN entre ambas.

No es necesario. Los costes de ejecución se reducen a la mitad.


SELECT     a.ID_FACTURA,                     
           a.FALINEA_AUX – min(falinea_aux) over 
(partition by id_factura, dt_fafacturacio) +1 ID_FALINEA,      
           a.ID_CLIENT,                   
           a.ID_COMPTEFACT,             
           a.PRODUCT_ID,                 
           a.ID_PRCATPRODUCTE,           
           a.DS_PRNUMSERVEI,             
           a.ID_FACONCEPTE,              
           a.DT_FAFACTURACIO,            
           a.NUM_FAIMPORTCONCEPTE,       
           a.PRODUCT_LABEL,              
           a.DT_MOVIMENT,                
           a.FG_TIPUSOPERACIO,           
           a.asset_id,                   
        a.PRODUCT_ATTR_VALUE             
FROM sta_vw_ci_linia_factura_tmp a       
ORDER BY 1,2 ASC;

Hablemos de Constraints.

Las constraints son reglas que ofrecen una garantía de integridad en la información vinculada. Existen 5 tipos de constraints.

–   Primary key
–   Foreign key
–   Unique key
–   Not Null
–   Check

Las constraints tienen, básicamente, tres propósitos:

         1.- Forzar integridad. Una constraint con estado ENABLED evita que, sobre las tablas, puedan modificarse valores de forma que no se satisfaga la regla de la constraint.

         2.- Validar la información. Una constraint con estado DISABLED VALIDATE realiza una validación sobre los datos, certificando que la información contenida en las tablas satisface la regla de la constraint.

         3.- Asumir la validez de la información. Una constraint con estado RELY no verifica la información, ni fuerza la integridad para las modificaciones y asume que los datos introducidos satisfacen la regla de la constraint.

Las constraints, por tanto, pueden estar ENABLED o DISABLED.
Las constraints habilitadas cumplen el propósito 1. Ningún dato introducido o modificado viola la integridad de la regla de la constraint. De este modo, sobre una primary key ENABLED no se permite un insert sobre una fila ya existente. No obstante, si la constraint está en el estado ENABLED NOVALIDATE, no se permitirán cambios que violen la regla, pero no se verificarán que los datos existentes la cumplan.

Las constraints, por tanto, pueden estar en estado VALIDATE o NOVALIDATE independientemente de si están ENABLED o DISABLED.
Las constraints con VALIDATE cumplen el propósito 2: La información existente está validada. No obstante, si la constraint está deshabilitada con DISABLED VALIDATE, no se permitirán cambios sobre las columnas afectadas, ya que aunque la constraint no está habilitada, certifica que los datos existentes son válidos y cumplen con la regla de la constraint.

Las constraints, por tanto, pueden estar en estado RELY o NORELY (también conocido como BELIEF) cuando las constraints están en estado DISABLED NOVALIDATE.
Las constraints en ese estado cumplen con el propósito 3: Asumen que la información es correcta y aportan al optimizador información válida para el uso de vistas materializadas, optimización de queries, etc. De este modo, por ejemplo, al optimizador le puede resultar mucho más fácil encontrar tablas FACT o DIMENSION por las constraints de foreign key entre las tablas.

Las constraints DISABLED NOVALIDATE RELY ni validan la información de la tabla, ni comprueban que las modificaciones cumplen las reglas de las restricciones.

Ejemplo 1: Una restricción de clave primaria habilitada con la opción de NOVALIDATE. No verifica la integridad referencial de las filas, pero no permite que las nuevas filas puedan violar la restricción.


SQL> create table valores(
  2   id number,
  3   valor varchar2(50),
  4   descrip varchar2(300),
  5   CONSTRAINT PK_VALORES PRIMARY KEY (id) deferrable initially immediate);

Tabla creada.

SQL> alter table valores disable primary key;

Tabla modificada.

SQL> insert into valores values (1,’ELEMENTO UNO’,’Primer elemento, tabla vacía’);

1 fila creada.

SQL> insert into valores values (1,’ELEMENTO UNO»’,’Primer elemento, repetido’);

1 fila creada.

SQL> insert into valores values (1,’ELEMENTO UNO»»’,’Primer elemento, otra vez repetido’);

1 fila creada.

SQL> alter table valores enable novalidate primary key;

Tabla modificada.

SQL> insert into valores values (1,’ELEMENTO UNO»»»’,’Primer elemento, violando PK, repetido’);
insert into valores values (1,’ELEMENTO UNO»»»’,’Primer elemento, violando PK, repetido’)
*
ERROR en lÝnea 1:
ORA-00001: restricci¾n ·nica (DW_VIAJES.PK_VALORES) violada

SQL> select * from valores;

        ID VALOR                                    DESCRIP
———- —————————————- —————————————-
         1 ELEMENTO UNO                             Primer elemento, tabla vacía
         1 ELEMENTO UNO’                            Primer elemento, repetido
         1 ELEMENTO UNO»                           Primer elemento, otra vez repetido
 

Ejemplo 2: Una restricción de clave primaria deshabilitada con la opción de VALIDATE. Verifica la integridad referencial de las filas, pero como está deshabilitada, no permite DML sobre la columna.


SQL> truncate table valores;

Tabla truncada.

SQL> alter table valores disable primary key;

Tabla modificada.

SQL> insert into valores values (1,’ELEMENTO UNO’,’Primer elemento, tabla vacía’);

1 fila creada.

SQL> insert into valores values (2,’ELEMENTO DOS’,’Segundo elemento, ID distinto.’);

1 fila creada.

SQL> insert into valores values (3,’ELEMENTO TRES’,’Tercer elemento, ID distinto.’);

1 fila creada.

SQL> alter table valores disable validate primary key;

Tabla modificada.

SQL> insert into valores values (3,’ELEMENTO TRES’,’Tercer elemento, ID distinto.’);
insert into valores values (3,’ELEMENTO TRES’,’Tercer elemento, ID distinto.’)
*
ERROR en lÝnea 1:
ORA-25128: No se puede insertar/actualizar/suprimir en la tabla con la restricci¾n (DW_VIAJES.PK_VALORES) desactivada y validada

SQL> insert into valores values (4,’ELEMENTO CUATRO’,’Cuarto elemento, ID distinto.’);
insert into valores values (4,’ELEMENTO CUATRO’,’Cuarto elemento, ID distinto.’)
*
ERROR en lÝnea 1:
ORA-25128: No se puede insertar/actualizar/suprimir en la tabla con la restricci¾n (DW_VIAJES.PK_VALORES) desactivada y validada

Ejemplo 3: Una restricción de clave ajena deshabilitada. Permite la entrada de valores no existentes en la PK. La opción de enable constraint con la cláusula EXCEPTIONS INTO registra en la tabla exceptions los identificadores de las filas que no cumplen la condición de la constraint.


SQL> create table master (id number primary key);

Tabla creada.

SQL> create table detail (id number primary key,  valor varchar2(50),
  2  master_id number constraint fk_master_id references master(id));

Tabla creada.

SQL> insert into master select rownum from user_tables;

40 filas creadas.

SQL> alter table detail disable constraint fk_master_id;

Tabla modificada.

SQL> insert into detail select rownum, object_name, mod(rownum,45)+1 from user_objects;

68 filas creadas.

SQL> alter table detail enable constraint fk_master_id exceptions into exceptions;
alter table detail enable constraint fk_master_id exceptions into exceptions
                                     *
ERROR en línea 1:
ORA-02298: no se puede validar (ORADBA.FK_MASTER_ID) – claves principales no encontradas

SQL> select count(*) from exceptions;

  COUNT(*)
———-
         5
SQL> select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME                     CONSTRAINT
—————— —————————— —————————— ——————
AAAY/nAAJAAAd7KAAn ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAo ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAp ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAq ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAr ORADBA                         DETAIL                         FK_MASTER_ID

Optimización SQL. Ejemplo 1. Producto cartesiano.

Para Nacho, por haber descubierto la pieza que faltaba. 😛

La siguiente consulta se ha cancelado tras 15 horas y 37 minutos de ejecución.

SQL> select count(*) from (SELECT BITOWN03.BS_C_CLIENTES_BIT_03.DNICIF_DE,
  2    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID,
  3    HSCONTRATOS_ESTADOS.ESTADO_DE,
  4    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT,
  5    BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID
  6  FROM
  7    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
  8    BITOWN03.LK_C_ESTADOS_BIT_03  HSCONTRATOS_ESTADOS,
  9    BITOWN03.BS_C_CLIENTES_BIT_03,
10    BITOWN03.BS_C_CONTRATOS_BIT_03  CONTRATOS
11  WHERE  BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID          = CONTRATOS.ABONADO_ID
12    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID = CONTRATOS.CONTRATO_ID
13    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID   = HSCONTRATOS_ESTADOS.ESTADO_ID
14    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT
15                   BETWEEN  to_date(’01-06-2005 00:00:00′,’DD-MM-YYYY HH24:MI:SS’)
16                       AND  to_date(’30-06-2005 23:59:59′,’DD-MM-YYYY HH24:MI:SS’)
17   AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID  =  ‘BA’);
  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
           *
ERROR at line 7:
ORA-00028: your session has been killed

Elapsed: 15:37:01.66

con el siguiente plan de ejecución:
SQL> @C:ORACLEORA92RDBMSADMINUTLXPLS

PLAN_TABLE_OUTPUT
————————————————————————————————————————

———————————————————————————————————————
| Id  | Operation                           |  Name                         | Rows  | Bytes | Cost  | Pstart| Pstop |
———————————————————————————————————————
|   0 | SELECT STATEMENT                    |                               |   461 | 24433 |    16 |       |       |
|   1 |  NESTED LOOPS                       |                               |   461 | 24433 |    16 |       |       |
|   2 |   MERGE JOIN CARTESIAN              |                               |  1448M|    58G|    16 |       |       |
|   3 |    NESTED LOOPS                     |                               |   971 | 35927 |    16 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID     | LK_C_ESTADOS_BIT_03           |     1 |    20 |     1 |       |       |
|*  5 |      INDEX UNIQUE SCAN              | PK_C_ESTADOS_BIT_03           |     1 |       |       |       |       |
|*  6 |     INDEX RANGE SCAN                | IDX_FECHA_ESTADO_CONT_JAVIER  |   971 | 16507 |    15 |       |       |
|   7 |    BUFFER SORT                      |                               |  1491K|  8739K|     1 |       |       |
|   8 |     INDEX FULL SCAN                 | PK_C_CLIENTES_BIT_03          |  1491K|  8739K|       |       |       |
|*  9 |   TABLE ACCESS BY GLOBAL INDEX ROWID| BS_C_CONTRATOS_BIT_03         |     1 |    10 |       | ROWID | ROW L |
|* 10 |    INDEX UNIQUE SCAN                | PK_C_CONTRATOS_BIT_03         |     1 |       |       |       |       |
———————————————————————————————————————

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

   5 – access(«HSCONTRATOS_ESTADOS».»ESTADO_ID»=’BA’)
   6 – access(«HS_C_ESTADOS_CONTRATO_BIT_03″.»FEC_ESTADO_DT»>=TO_DATE(‘2005-06-01 00:00:00’, ‘yyyy-mm-dd
              hh24:mi:ss’) AND «HS_C_ESTADOS_CONTRATO_BIT_03».»ESTADO_ID»=’BA’ AND
              «HS_C_ESTADOS_CONTRATO_BIT_03».»FEC_ESTADO_DT»<=TO_DATE(‘2005-06-30 23:59:59’, ‘yyyy-mm-dd hh24:mi:ss’))
       filter(«HS_C_ESTADOS_CONTRATO_BIT_03″.»ESTADO_ID»=»HSCONTRATOS_ESTADOS».»ESTADO_ID» AND
              «HS_C_ESTADOS_CONTRATO_BIT_03».»ESTADO_ID»=’BA’)
   9 – filter(«BS_C_CLIENTES_BIT_03″.»ABONADO_ID»=»CONTRATOS».»ABONADO_ID»)
  10 – access(«HS_C_ESTADOS_CONTRATO_BIT_03″.»CONTRATO_ID»=»CONTRATOS».»CONTRATO_ID»)

Note: cpu costing is off

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************



Las tablas contienen estadísticas fieles. El producto cartesiano tampoco engaña: cruzará 58 Gb. para atender nuestra petición. De todas maneras, algo falla. Ese plan no es óptimo.

Las estadísticas se generan mediante la siguiente llamada a DBMS_STATS.

SQL> begin
  2      DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>usuario,
  3         TABNAME=>tabla,
  4         ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
  5         METHOD_OPT => ‘FOR ALL INDEXED COLUMNS SIZE 1’,
  6         DEGREE=>4);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Es preciso no únicamente analizar las tablas, sino también los índices. Algunos de ellos están analizados con el procedimiento DBMS_STATS.GATHER_INDEX_STATS, pero una de las tablas está particionada. Lo mejor y más simple es incluir el parámetro CASCADE=>TRUE en la llamada a DBMS_STATS.

Es cierto que el coste de análisis de estas tablas incrementará. Además, el parámetro CASCADE afecta, no sólo a índices, sino también a vistas materializadas, particiones, etc. (es decir, a todas sus dependencias).

Con las tablas analizadas de este modo, el nuevo plan de ejecución es éste:



SQL> @c:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
———————————————————————————————————-

————————————————————————————————
| Id  | Operation                      |  Name                         | Rows  | Bytes | Cost  |
————————————————————————————————
|   0 | SELECT STATEMENT               |                               |   471 | 30615 |   405 |
|*  1 |  HASH JOIN                     |                               |   471 | 30615 |   405 |
|*  2 |   INDEX RANGE SCAN             | IDX_FECHA_ESTADO_CONT_JAVIER  |  8740 |   145K|    75 |
|   3 |   NESTED LOOPS                 |                               |   771K|    35M|   257 |
|   4 |    NESTED LOOPS                |                               |  1626K|    46M|   257 |
|   5 |     TABLE ACCESS BY INDEX ROWID| LK_C_ESTADOS_BIT_03           |     1 |    20 |     1 |
|*  6 |      INDEX UNIQUE SCAN         | PK_C_ESTADOS_BIT_03           |     1 |       |       |
|   7 |     INDEX FAST FULL SCAN       | IDX_ABONADO_CONTRATO          |  1626K|    15M|   256 |
|   8 |    TABLE ACCESS BY INDEX ROWID | BS_C_CLIENTES_BIT_03          |     1 |    18 |       |
|*  9 |     INDEX UNIQUE SCAN          | PK_C_CLIENTES_BIT_03          |     1 |       |       |
————————————————————————————————

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

   1 – access(«HS_C_ESTADOS_CONTRATO_BIT_03″.»CONTRATO_ID»=»CONTRATOS».»CONTRATO_ID» AND
              «HS_C_ESTADOS_CONTRATO_BIT_03″.»ESTADO_ID»=»HSCONTRATOS_ESTADOS».»ESTADO_ID»)
   2 – access(«HS_C_ESTADOS_CONTRATO_BIT_03″.»FEC_ESTADO_DT»>=TO_DATE(‘2005-06-01 00:00:00’,
              ‘yyyy-mm-dd hh24:mi:ss’) AND «HS_C_ESTADOS_CONTRATO_BIT_03».»ESTADO_ID»=’BA’ AND
              «HS_C_ESTADOS_CONTRATO_BIT_03».»FEC_ESTADO_DT»<=TO_DATE(‘2005-06-30 23:59:59’, ‘yyyy-mm-dd
              hh24:mi:ss’))
       filter(«HS_C_ESTADOS_CONTRATO_BIT_03».»ESTADO_ID»=’BA’)
   6 – access(«HSCONTRATOS_ESTADOS».»ESTADO_ID»=’BA’)
   9 – access(«BS_C_CLIENTES_BIT_03″.»ABONADO_ID»=»CONTRATOS».»ABONADO_ID»)

Note: cpu costing is off

30 rows selected.

La ejecución ahora sólo tarda: 3 segundos.
El coste de análisis aumenta de 26 minutos a 32 minutos. La ganancia justifica el coste.

Muy bien. 🙂