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.


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»… :/

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/

Í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)!

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.