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

Feliz Sant Jordi!

Hoy es el día del libro! Feliz Sant Jordi!
En Amazon España hay un 10% dto en el libro Optimización SQL en Oracle
(para compras en España no hay gastos de envío ni cargos de aduanas)


Los miembros de COH tienen un 35% dto comprando en la tienda de CreateSpace
Envío internacional desde USA.


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!

Nueva sección en la Comunidad Oracle Hispana.

A partir de abril, el Show de la Comunidad Oracle Hispana tendrá una nueva sección en el podcast con píldoras para mejorar el rendimiento de código SQL en Oracle.

En el minuto 3 empieza mi sección, que arranca con una entrevista. Muchas gracias a Fernando García y a Clarisa Maman por acogerme en este proyecto.

PD: Vaya, este post se quedó en “borrador”… :/

Preparando la edición Kindle de “Optimización SQL en Oracle”

Aunque el libro está concebido en formato papel, por el ancho de página y los contenidos (código, trazas, etc.) la edición kindle ya está en el horno!

… ¿qué os parece?, ¿alguien interesado?, ¿alguna sugerencia?, ¿dudas?, ¿comentarios?, …

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 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 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/

Í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.