Defragmentación de un tablespace mediante scripts.

Accede a la versión actualizada de esta entrada en el blog de Café Database aquí:

En español:

https://cafedatabase.com/es/como-defragmentar-los-contenidos-de-un-tablespace-entero/

En inglés:

https://cafedatabase.com/en/how-to-defrag-the-entire-contents-in-a-tablespace/

TOP 7 ERRORS in Oracle GoldenGate12c that will save your day

Accede a la versión actualizada de esta entrada en el blog de Café Database aquí:

En español:

https://cafedatabase.com/es/los-7-errores-mas-comunes-con-oracle19c-goldengate/

 

En inglés:

https://cafedatabase.com/en/the-7-most-common-errors-in-oracle19c-goldengate/

 

Cómo crear un cluster indexado en oracle

Bueno, pues una primera prueba-tutorial... pasen y vean !

Intervenciones en el podcast de la Comunidad Oracle Hispana (completo).

Ha terminado la temporada 2 del Show de la Comunidad Hispana y os paso los audios del programa de la Comunidad Oracle Hispana, donde en mi sección dedicada a la Optimización SQL cuento algunos de los consejos y conceptos que desarrollo en mi libro "Optimización SQL en Oracle".

Más o menos ésto es lo que se contó:

Temporada 2 - Programa 2 - Abril de 2014

Fernando García y Clarisa Maman presentan conmigo la nueva sección y damos un repaso a los contenidos del libro y su historia. 

Temporada 2 - Programa 3 - Mayo de 2014

Arranca la sección con un tema que ha vuelto loco a más de uno: los productos cartesianos. No obstante, dándole una vuelta de tuerca, pues hay casos en los que el producto cartesiano es la forma óptima de resolver una sentencia... lo crees?

Temporada 2 - Programa 4 - Junio de 2014

Intento resolver la pregunta sobre por qué Oracle no siempre utiliza el índice que queremos y presentamos junto con Fernando García y Clarisa Mamán el concurso del libro "Optimización SQL en Oracle".

Temporada 2 - Programa 5 - Julio de 2014

Cuento en qué consisten las hints, menciono algunas de mis favoritas, y pongo ejemplos de cómo pueden usarse para ayudar a resolver problemas de optimización (y no, no es forzando el uso de un índice).

Temporada 2 - Programa 6 - Agosto de 2014

Desvelamos quien ha sido el ganador del concurso y cuento un poco más sobre el paquete DBMS_STATS y su uso para recopilar estadísticas de forma eficiente. 

Temporada 2 - Programa 7 - Setiembre 2014

Hablo sobre el uso de las vistas materializadas y cómo pueden ayudarnos, sobre todo, en entornos data warehouse sobre tablas con un gran volumen de datos.

Temporada 2 - Programa 8 - Octubre 2014

No mucha gente conoce los índices bitmap y sus beneficios. Hablo de ellos y de como estas estructuras, mal implementadas, pueden causarnos problemas de rendimiento usados bajo ciertas condiciones.

Temporada 2 - Programa 9 - Noviembre 2014

En el último programa de la temporada cuento un truco que puede ser muy útil gestionando problemas de rendimiento en bases de datos subidas de versión. El optimizador cambia su comportamiento y decisiones en cada versión de Oracle y cuento cómo configurar el optimizador para que se comporte como en versiones anteriores.

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

El ¿auto deadlock?

¡Últimamente me pasan unas cosas muy curiosas!

¿Qué puede causar que una sesión aparezca en la vista DBA_WAITERS como bloqueadora y como en espera? Fernando García sabe la respuesta, pues él "estaba allí" cuando sucedió.

Se trata de la sesión 80 y, como podéis ver, los bloqueos son todos sobre el objeto 524308 (una tabla).

Se admiten apuestas!!! La base de datos es una Oracle12c y hay tres sesiones en el juego.
PISTA: No hay, ni hubo, ni habrá en este ejemplo un deadlock ORA-00060.



PDB1@ORCL> select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE
--------------- --------------- --------------------------
MODE_HELD
----------------------------------------
MODE_REQUESTED   LOCK_ID1   LOCK_ID2
---------------------------------------- ---------- ----------
    80     44 Transaction
Exclusive
Exclusive     524308  2374

    72     44 Transaction
Exclusive
Share     524308  2374

    80     80 Transaction
None
Exclusive     524308  2374

    72     80 Transaction
None
Share     524308  2374

Resuelto el misterio del año 0000!.

Hoy, gracias a una discusión a tres bandas en twitter con Tony Doval @tonydoval, Xavier Picamal @Condebond y Elias Fernández @sailefm se ha resuelto por fin el misterio del año 0000 en algunas de mis bases de datos.

¿Quién localizó el bug? El premio es para Elias Fernández! (que se lleva mi más sincero "me quito el sombrero").

La cuestión es que el año 0000 no existe, aunque en algunas bases de datos he visto lo siguiente:

SQL> select * from zero_leap_year
  2  where to_char(date_year,'yyyy')='0000' and rownum<6;

DATE_YEAR
--------------------
30-DEC-0000 00:00:00
30-JAN-0000 00:00:00
30-DEC-0000 00:00:00
29-FEB-0000 00:00:00
30-JAN-0000 00:00:00

El año 0000 no existe. Del año 1 antes de Cristo se pasa al año 1 después de Cristo. Cualquier forma de insertar un año 0000 o una fecha 29-febrero en un año no bisiesto dará los siguientes errores:

SQL> insert into zero_leap_year values (to_date('20-02-0000 00:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into zero_leap_year values (to_date('20-02-0000 00:00:00','dd-mm-yyyy hh24:mi:ss'))
                                           *
ERROR at line 1
ORA-01841 :(full) year must be between -4713 and +9999, and not be 0
SQL> insert into zero_leap_year values (to_date('29-02-2007 00:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into zero_leap_year values (to_date('29-02-2007 00:00:00','dd-mm-yyyy hh24:mi:ss'))
                                           *
ERROR at line 1:
ORA-01839: date not valid for month specified

No obstante, estas filas misteriosas seguían apareciendo. Tanto en versión Oracle9i, Oracle10g y Oracle11g. ¿Cómo han podido colarse? Muy probablemente como Elias Fernandez encontró: partiendo de una fecha como, por ejemplo, 1-enero del año 1, restarle 1 día. Voilà! 

SQL> select (TO_DATE('01/01/0001 00:00:00', 'DD/MM/YYYY HH24:MI:SS') - 1) from dual;

(TO_DATE('01/01/0001
--------------------
31-DIC-0000 00:00:00


No sólo eso... ese año 0000 que no existe en la historia, según Oracle, es bisiesto!

SQL> select (TO_DATE('01/01/0001 00:00:00', 'DD/MM/YYYY HH24:MI:SS') - 307) from dual;

(TO_DATE('01/01/0001
--------------------
29-FEB-0000 00:00:00


Esto es un bug en toda regla!

Pregunta para un aspirante a un puesto de DBA (parte II)

Viene de la parte 1.

En este proyecto era muy importante para el cliente el número de palabras de cada pregunta (debía oscilar entre 350-500). Por mi parte lo consideré excesivo. Suelo dedicar a cada exposición el texto que creo conveniente. Por eso tengo entradas larguísimas (cómo montar un RAC o la replicación con GoldenGate) y otras muy cortas.

Así que propuse el siguiente ejemplo (120 palabras aproximadamente) en que, según mi entender, no es preciso extenderse más con la pregunta ni la exposición de las respuestas.

QUESTION: 

Which of the following SQL commands will raise an error when executed on a table named TEST placed in a READ ONLY tablespace?

POSSIBLE ANSWERS:

A: AUDIT INSERT ON test;

B: TRUNCATE TABLE test;

C: DROP TABLE test;

D: ALTER TABLE test MOVE TABLESPACE system;

¿Qué tal? ¿Sabéis la respuesta?

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 of ORA-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 because that 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 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

Accede a la versión actualizada de esta entrada en el blog de Café Database aquí:

En español:

https://cafedatabase.com/es/ordenaciones-desordenadas/

En inglés:

https://cafedatabase.com/en/unordered-sorts/

Í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

Accede a la versión actualizada de esta entrada en el blog de Café Database aquí:

En español:

https://cafedatabase.com/es/indexando-columnas-con-funciones-impuras/

En inglés:

https://cafedatabase.com/en/indexing-columns-with-non-pure-functions/

El año 0 y Oracle GoldenGate

Inscríbete ahora al curso a un precio especial! Sólo durante el mes de agosto!

El año 0 no existe. Del año -1 (es decir, año 1 antes de Cristo) se pasa al año 1 y el primer año bisiesto de la historia es el año 4.

No obstante, en una base de datos he visto filas que en un campo de tipo DATE habían conseguido introducir una fecha '29-FEB-0000'.

Oracle realiza dos controles con las fechas:

Uno específicamente sobre el año cero, que es el siguiente:

SQL> create table fechas (fecha date);

Tabla creada.

SQL> insert into fechas values (to_date('01-01-0000','DD-MM-YYYY'));

insert into fechas values (to_date('01-01-0000','DD-MM-YYYY'))

                                    *

ERROR en línea 1:

ORA-01841: el valor (completo) del año debe estar entre -4713 y +9999, y no debe ser igual a 0

Otro sobre la validez de la fecha, de modo que:

• Un día 31 es válido sólo para los meses enero, marzo, mayo, julio, agosto, octubre y diciembre.
• Un día 29 sólo es válido para el mes de febrero en los años bisiestos.
• Un día 30 es válido para todos los meses excepto febrero.

SQL> insert into fechas values (to_date('29-02-0001','DD-MM-YYYY'));

insert into fechas values (to_date('29-02-0001','DD-MM-YYYY'))

                                    *

ERROR en línea 1:

ORA-01839: fecha incorrecta para el mes especificado

Como comento en este caso, en una base de datos consiguieron saltar los controles del motor y se introdujeron fechas en año 0, incluso el '29-FEB-0000'.

NOTA: Ni idea cómo lo consiguieron. Intenté insertar esa fecha con SQL dinámico, o desde PL/SQL, etc y siempre recibía alguno de estos dos errores.

SQL>  select CODIGO, FECHA from  TABLA_BASE

  2  where FECHA =(select min(FECHA) from TABLA_BASE);

CODIGO       FECHA

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

000600211048 01-ENE-0000 00:00:00

00060164681- 01-ENE-0000 00:00:00

El caso es que esa base de datos se iba a migrar de Oracle9i a Oracle11gR2, y los procedimientos de export/import propagaban las fechas incluyendo estos registros sobre el año 0. De modo que la importación fue bien, pero en cuanto volvieron a insertar un año 0 en la tabla, al propagarse por Orace GoldenGate al futuro entorno, el error ORA-01841 apareció de pronto.

Oracle GoldenGate Delivery for Oracle process started, group REPL discard file opened: 2013-09-26 17:07:02

Current time: 2013-09-26 17:16:35

Discarded record from action ABEND on error 1841

OCI Error ORA-01841: el valor (completo) del año debe estar entre -4713 y +9999, y no debe ser igual a 0 (status = 1841). UPDATE "APP_OWNER"."TABLA_BASE" SET "ACTIVO" = :a4,"TIPO" = :a5,"DURACION" = :a6,"FECHA" = :a7 WHERE "CODIGO" = :b0 AND "FECHA_ORIGEN" = :b1 AND "CATEGORIA" = :b2 AND "TABLA_BASE_CAT" = :b3

Aborting transaction on ./dirdat/ab beginning at seqno 58 rba 4270475

                         error at seqno 58 rba 4270475

Problem replicating APP_OWNER.TABLA_BASE to APP_OWNER.TABLA_BASE

Mapping problem with compressed update record (target format)...

*

CODIGO = 000600371922

FECHA_ORIGEN = 2004-05-27 00:00:00

CATEGORIA = asi1

TABLA_BASE_CAT = ~#

ACTIVO = NULL

TIPO = T

DURACION = 60

FECHA = 0000-02-29 00:00:00

*

Process Abending : 2013-09-26 17:16:35

Para solucionar esto, en el fichero de parámetros de REPLICAT, hay que mapear las fechas a una fecha válida. Para evitar, además, el problema del 29 de febrero envié el mapeo al año 0004.

Replicat repl

UserID oggadm1@bbdd, password *****

AssumeTargetDefs

DiscardFile ./dirrpt/repl.dsc

ALLOWNOOPUPDATES

TABLEEXCLUDE APP_OWNER.VM_TABLE1_DS

TABLEEXCLUDE APP_OWNER.VM_TABLE2_DS

Map app_owner.tabla_base, Target app_owner.tabla_base,

COLMAP (USEDEFAULTS,fecha=@STRSUB(fecha,"0000","0004"));

Map app_owner.*, Target app_owner.*;

Durante todo el mes de agosto, 60% de descuento en el nuevo curso.

Curso práctico sobre Oracle GoldenGate12c a solo 9,99$ con un descuento del 60%! Solo durante agosto 2018!

¿Quieres aprender GoldenGate y no tienes tiempo ni los recursos para montarte un entorno de pruebas? ¿Has intentado aprender y has desistido tras encontrar errores que no has podido solucionar?

En este curso te guiaré paso a paso para que consigas:

• Crear dos maquinas virtuales en tu PC o tu portátil, configuradas cada una con un Oracle12c con multitenant y el software de Oracle GoldenGate12c, siguendo paso a paso todos los aspectos de la configuración de los hosts, la red, el software, variables de entorno,... TODO para que el entorno de test funcione sin errores.
• Realizar dos replicaciones lógicas de datos entre diferentes usuarios y diferentes bases de datos, creando entornos clonados y comprendiendo todos los conceptos necesarios para una configuración de GoldenGate sin errores.
• Aprender todos los aspectos de arquitectura relevantes a la replicación de datos entre dos bases de datos (el logging, el control sobre el SCN, la arquitectura de procesos y la gestión de transacciones.
• Disponer de una guía práctica, con diccionario de términos, área de solución de problemas a los errores más frecuentes en GoldenGate
• Acceso de por vida al curso, y a sus sucesivas expansiones sin ningún coste adicional!

Inscríbete ahora al curso a un precio especial! Sólo durante el mes de agosto!

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.