Pregunta para un aspirante a un puesto de DBA

En esta semana me han ofrecido un proyecto en Elance basado en preparar unas 300 preguntas para candidatos a puestos de DBA de Oracle, incluyendo la respuesta y las explicaciones. Las reglas no permitían preguntas de si/no/verdadero/falso, ni multi opción, y debían basarse en escenarios de trabajo reales.

Finalmente el proyecto no saldrá adelante con mi participación, y quiero compartir con vosotros la pregunta que lancé como ejemplo: (la solución y explicación está en los comentarios)

QUESTION: 
Database is stuck. No more connections are allowed because ofORA-00257: archiver error. Connect internal only, until freed.You check the following:
– db_recovery_file_dest_size is set to 20GB
– db_recovery_file_dest filesystem/folder has 30GB of free space available
– v$flash_recovery_area_usage shows a 99% of occupation.
What would you do first to set the database available with minimum impact as fast as possible?
POSSIBLE ANSWERS:
A:Delete archivelogs from Flash Recovery Area with OS commands becausethat would free the archiver area.
B:Launch an archivelogs backup with RMAN using the “delete input” clause because that would free the archiver area.
C:Change the parameter db_recovery_file_dest_size to 50G.
D: Change the database mode to NOARCHIVELOG, delete archivelogs from Flash Recovery Area and set back the database to ARCHIVELOG mode.

Blog de Clarisa Mamán

Gracias a la Comunidad Oracle Hispana estoy teniendo la oportunidad de conocer otros blogs de tecnología Oracle en español y después del de Andrew Reid me gustaría mencionar el de Clarisa Mamán.
Su blog está lleno de videotutoriales sobre APEX y desarrollo SQL sobre Oracle. Los videos son claros y precisos, tanto que invitan a matricularse en su curso sobre APEX en que enseña cómo desarrollar una aplicación completa, desde la instalación a los detalles finales.

 Blog de Clarisa Mamán

Blog interesante: Andrew Reid.

Hace poco he descubierto el blog de Andrew Reid que no conocía y me ha parecido muy interesante. He leído algunos artículos y tienen muy buena pinta. Trata tanto temas de rendimiento como asuntos de administración, con scripts detallados y tests hechos a conciencia!

Totalmente recomendable!!

Si quieres conocer más sobre el trabajo de Andrew en la red, quizás quieras echarle un ojo a su blog en inglés: http://international-dba.blogspot.com.es/

La autoridad de ORDER BY

Para que el resultado de una consulta SQL esté ordenado, la única cláusula válida es ORDER BY.


Oracle únicamente garantiza la devolución ordenada de las filas cuando se establece ORDER BY como criterio de ordenación.

He repetido estas dos frases como un mantra miles de veces.

Cualquier otra forma de obtener los datos ordenados es:

  • Una casualidad.
  • Una ilusión.
  • Una circunstancia temporal.
  • Una combinación de las anteriores.

Los ejemplos que mostraré a continuación, de resultados ordenados sin cláusula ORDER BY en el comando SELECT, son fruto de su imaginación

FALSO MITO #1 – Si las filas se insertaron de forma ordenada.

SQL> create table objetos as select object_id, object_name, object_type 
   2  from dba_objects order by object_id;

Table created.

SQL> select * from objetos where rownum<10 font="">


 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
———- ——————– ——————-
         2 C_OBJ#               CLUSTER
         3 I_OBJ#               INDEX
         4 TAB$                 TABLE
         5 CLU$                 TABLE
         6 C_TS#                CLUSTER
         7 I_TS#                INDEX
         8 C_FILE#_BLOCK#       CLUSTER
         9 I_FILE#_BLOCK#       INDEX
        10 C_USER#              CLUSTER

9 rows selected.

FALSO MITO #2 – Si se accede a las filas mediante un índice.

SQL> create table objetos_indice as select object_id, object_name, object_type 
   2  from dba_objects;

Table created.

SQL> create index idx_object_id on objetos_indice (object_id);

Index created.

SQL> select * from objetos_indice where object_id between 11256 and 11260;

 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
———- ——————– ——————-
     11256 ALL_APPLY            SYNONYM
     11257 DBA_APPLY_PARAMETERS VIEW
     11258 DBA_APPLY_PARAMETERS SYNONYM
     11259 ALL_APPLY_PARAMETERS VIEW
     11260 ALL_APPLY_PARAMETERS SYNONYM

FALSO MITO #3 – Si la tabla tiene estructura IOT.

SQL> create table objetos_iot
  2  (object_id, object_name, object_type,
  3   constraint pk_objetos_iot primary key (object_id))
  4  organization index
  5  as select object_id, object_name, object_type
  6  from dba_objects;

Table created.


SQL> select * from objetos_iot where object_id between 11256 and 11265;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
     11256 ALL_APPLY                      SYNONYM
     11257 DBA_APPLY_PARAMETERS           VIEW
     11258 DBA_APPLY_PARAMETERS           SYNONYM
     11259 ALL_APPLY_PARAMETERS           VIEW
     11260 ALL_APPLY_PARAMETERS           SYNONYM
     11261 _DBA_APPLY_SOURCE_SCHEMA       VIEW
     11262 _DBA_APPLY_SOURCE_OBJ          VIEW
     11263 DBA_APPLY_INSTANTIATED_OBJECTS VIEW
     11264 DBA_APPLY_INSTANTIATED_OBJECTS SYNONYM
     11265 ALL_APPLY_INSTANTIATED_OBJECTS VIEW

10 rows selected.
FALSO MITO #4 – Si la tabla tiene estructura de cluster.
SQL> create cluster clu_objetos (object_id number) tablespace test;

Cluster created.


SQL> create index idx_cluster_objetos on cluster clu_objetos;

Index created.

SQL> create table padre_objeto (id number constraint pk_objeto primary key) cluster clu_objetos(id);

Table created.

SQL> insert into padre_objeto values (1);

1 row created.

SQL> insert into padre_objeto values (2);

1 row created.

SQL> insert into padre_objeto values (3);

1 row created.

SQL> insert into padre_objeto values (4);

1 row created.

SQL> insert into padre_objeto values (5);

1 row created.

SQL> insert into padre_objeto values (6);

1 row created.

SQL> create table hijo_objeto (id number constraint fk_objeto_padre references padre_objeto(id)) cluster clu_objetos(id);

Table created.

SQL> insert into hijo_objeto values (3);

1 row created.

SQL> insert into hijo_objeto values (6);

1 row created.

SQL> insert into hijo_objeto values (5);

1 row created.

SQL> insert into hijo_objeto values (2);

1 row created.

SQL> select * from hijo_objeto;

        ID
———-
         2
         3
         5
         6
FALSO MITO #5 – Si la sentencia utiliza DISTINCT.

SQL> create table test_orden (texto varchar2(50));

Table created.

SQL> insert into test_orden values (‘PPPPP’);

1 row created.

SQL> insert into test_orden values (‘FFFFF’);

1 row created.

SQL> insert into test_orden values (‘ZZZZZ’);

1 row created.

SQL> insert into test_orden values (‘AAAAA’);


1 row created.

SQL> select texto from test_orden;

TEXTO
————————————————–
PPPPP
FFFFF
ZZZZZ
AAAAA


SQL> select distinct texto from test_orden;

TEXTO
————————————————–
AAAAA
FFFFF
PPPPP
ZZZZZ

FALSO MITO #6 – Si la sentencia utiliza GROUP BY.

SQL> select texto from test_orden;

TEXTO
————————————————–
PPPPP
FFFFF
ZZZZZ
AAAAA

SQL> select texto from test_orden group by texto;

TEXTO
————————————————–
AAAAA
FFFFF
PPPPP

ZZZZZ


FALSO MITO #7 – Si la sentencia utiliza realiza una join de tipo MERGE JOIN.

SQL> create table test_orden2 as select * from test_orden;


Table created.

SQL> select /*+USE_MERGE(t,t2) */ t.texto
  2  from test_orden t, test_orden2 t2
  3  where t.texto=t2.texto;

TEXTO
————————————————–
AAAAA
FFFFF
PPPPP
ZZZZZ
OLVIDEN ESTOS EJEMPLOS!!!!
NO TRATEN DE IMPLEMENTARLOS EN PRODUCCIÓN!!!!
Todas estas ordenaciones casuales, sin uso de la cláusula ORDER BY, son sólo fruto de su imaginación…



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

Índices basados en funciones. Problemas en migraciones de versión.

Una base de datos Oracle 9i tenía una tabla con un campo fecha y un índice basado en función para localizar los valores nulos. La función NVL asignaba un valor ‘NULO’ a los campos vacíos, con el fin de localizar estas filas nulas, y para no dar un conflicto de tipos, convertía la fecha a TO_CHAR.

De este modo, la consulta se ejecutaba así:
Ejecución en Oracle 9i
SQL>  create index fbi_fecha on test(NVL(TO_CHAR(FECHA),’NULO’));

Índice creado.

SQL> explain plan for
  2  select * from test
  3  where NVL(TO_CHAR(FECHA),’NULO’) = ‘NULO’;

Explained.

SQL> @?/rdbms/admin/utlxpls

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

—————————————————————————
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
—————————————————————————
|   0 | SELECT STATEMENT            |             |   130 |  1040 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |   130 |  1040 |     5 |
|*  2 |   INDEX RANGE SCAN          | FBI_FECHA   |   130 |       |     3 |
—————————————————————————

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

   2 – access(NVL(TO_CHAR(«TEST».»FECHA»),’NULO’)=’NULO’)

Note: cpu costing is off

15 rows selected.
No obstante, al migrar esta base de datos a Oracle 11g, esta misma sentencia no usaba el índice basado en función, y hacía un acceso FULL SCAN.
Ejecución en Oracle 11g

SQL>  create index fbi_fecha on test(NVL(TO_CHAR(FECHA),’NULO’));

Índice creado.

SQL> explain plan for
  2  select * from test
  3  where NVL(TO_CHAR(FECHA),’NULO’) = ‘NULO’;

Explicado.

SQL> @?/rdbms/admin/utlxpls

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

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      | 10681 | 85448 |   571   (9)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| TEST | 10681 | 85448 |   571   (9)| 00:00:07 |
————————————————————————–

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

   1 – filter(NVL(TO_CHAR(INTERNAL_FUNCTION(«FECHA»)),’NULO’)=’NULO’)

13 filas seleccionadas.
El motivo: aunque la sintaxis de creación de los índices ha sido la misma, internamente su almacenamiento es ligeramente distinto. Mientras en Oracle9i se almacena la función TO_CHAR sin formato de máscara, en Oracle11g se define con un formato de máscara por defecto.
Ejecución en Oracle 9i
SQL> select index_name, column_expression
  2  from user_ind_expressions
  3  where index_name=’FBI_FECHA’;

INDEX_NAME                     COLUMN_EXPRESSION
—————————— ———————————————–
FBI_FECHA                      NVL(TO_CHAR(«FECHA»),’NULO’)


Ejecución en Oracle 11g
SQL> select index_name, column_expression
  2  from user_ind_expressions
  3  where index_name=’FBI_FECHA’;

INDEX_NAME                     COLUMN_EXPRESSION
—————————— ———————————————–
FBI_FECHA                      NVL(TO_CHAR(«FECHA»,’DD/MM/RR’),’NULO’)



De modo que, para que en Oracle 11g el optimizador considere el uso del íncide basado en función FBI_FECHA, la función de filtrado debe ser idéntica y debe incluir la máscara ‘DD/MM/RR’ que se ha añadido a la expresión del índice.
Ejecución en Oracle 11g

SQL> explain plan for
  2  select * from test
  3  where NVL(TO_CHAR(FECHA,’DD/MM/RR’),’NULO’) = ‘NULO’;

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
———————————————————————————
Plan hash value: 3576847778

—————————————————————————————–
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT            |           |   130 |  2210 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |   130 |  2210 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FBI_FECHA |   130 |       |     3   (0)| 00:00:01 |
—————————————————————————————–

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

   2 – access(NVL(TO_CHAR(INTERNAL_FUNCTION(«FECHA»),’DD/MM/RR’),’NULO’)=’NULO’)

14 filas seleccionadas.



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

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.