Advanced-rewrite Oracle Database, tuning sin código fuente

Por Enrique Orbegozo
Publicado en abril 2013

Es sabido por todo DBA que los problemas de desempeño son en gran medida atribuibles a código ineficiente, pero allí nos tienen recibiendo las quejas por lo lenta que es la base de datos; no nos queda más que encontrar este código y proceder a corregirlo, pero ¿qué podemos hacer si no tenemos siquiera acceso al código fuente? Lo más que podíamos hacer era hacer ajustes a las estadísticas o a algunos parámetros, quizás crear algún índice, pero nada más, afortunadamente desde la versión Oracle 10g se nos presenta una poderosa herramienta para situaciones como ésta y como siempre, nada mejor para entender cómo funciona que mediante un ejemplo práctico.

Imaginemos que luego de hacer las inspecciones del caso encontramos que el query problemático es el siguiente:

selecte.ename, d.dname
fromemp e, dept d 
wheree.deptno = to_number(d.deptno)

--------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------
|  0 | SELECT STATEMENT   |      |   14 |   308 |     7  (15)|
|* 1 |  HASH JOIN         |      |   14 |   308 |     7  (15)|
|  2 |   TABLE ACCESS FULL| DEPT |    4 |    52 |     3   (0)|
|  3 |   TABLE ACCESS FULL| EMP  |   14 |   126 |     3   (0)|
--------------------------------------------------------------

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

   1 - access("E"."DEPTNO"=TO_NUMBER(TO_CHAR("D"."DEPTNO")))

La tabla dept tiene como clave primaria a la columna deptno, pero el índice asociado no se está usando debido a que se le está aplicando el operador to_number a dicha columna. Si tuviésemos acceso al código procederíamos a remover este operador para que quede algo así:

selecte.ename, d.dname
fromemp e, dept d 
wheree.deptno = d.deptno

--------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost(%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |   14 |   308 |     4  (0)|
|  1 |  NESTED LOOPS                |         |   14 |   308 |     4  (0)|
|  2 |   TABLE ACCESS FULL          | EMP     |   14 |   126 |     3  (0)|
|  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    13 |     1  (0)|
|* 4 |    INDEX UNIQUE SCAN         | PK_DEPT |    1 |       |     0  (0)|
--------------------------------------------------------------------------

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

   4 - access("E"."DEPTNO"="D"."DEPTNO")

Como no tenemos acceso al código lo más que podríamos hacer es crear un índice funcional para to_number(deptno), pero coincidirán en que es una solución poco elegante y eficiente, más aun cuando Oracle nos ofrece la solución perfecta por medio del nuevo package dbms_advanced_rewrite, el cual nos permite cambiar una sentencia por otra de forma transparente.

Para empezar a usarlo debemos en primer lugar otorgar los privilegios requeridos al usuario que emplearemos para hacer este mapeo, que para nuestro ejemplo será el usuario scott.

SYS@orcl> grant execute on sys.dbms_advanced_rewrite to scott;

Grant succeeded.

SYS@orcl> grant create materialized view to scott;

Grantsucceeded.

A continuación hacemos uso del procedimiento declare_rewrite_equivalence al interior de dbms_advanced_rewrite, debemos proporcionar un nombre único, la sentencia original y la sentencia por la cual deseamos cambiarla.

SCOTT@orcl> begin
2  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
3    name => 'use_index',
  4    source_stmt => 'select e.ename, d.dname from emp e, dept d 
  5      where e.deptno = to_number(d.deptno)',
  6    destination_stmt => 'select e.ename, d.dname from emp e, dept d 
  7      where e.deptno = d.deptno');
8  end;
9  /

PL/SQL procedure successfully completed.

Deben tener en cuenta que ambas sentencias deben coincidir en la cantidad de columnas y en los tipos de datos o de otra forma tendrían un error similar a este:

ERROR at line 1:
ORA-30390: the source statement is not equivalent to the destination statement
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

Pues bien, veamos ahora si es verdad tanta belleza, para lo cual ejecutamos nuevamente el código original.

SCOTT@orcl> select e.ename, d.dname from emp e, dept d
2  wheree.deptno = to_number(d.deptno);

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

--------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------
|  0 | SELECT STATEMENT   |      |   14 |   308 |     7  (15)|
|* 1 |  HASH JOIN         |      |   14 |   308 |     7  (15)|
|  2 |   TABLE ACCESS FULL| DEPT |    4 |    52 |     3   (0)|
|  3 |   TABLE ACCESS FULL| EMP  |   14 |   126 |     3   (0)|
--------------------------------------------------------------

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

   1 - access("E"."DEPTNO"=TO_NUMBER(TO_CHAR("D"."DEPTNO")))

Como diría Condorito: ¡exijo una explicación! Simple, el parámetro query_rewrite_integrity controla las posibilidades de reemplazar una sentencia por otra, y por defecto tiene el valor enforced y para nuestro caso es requerido que sea trusted. Hagamos el cambio y reintentemos con nuestro query anterior.

SCOTT@orcl> alter session set query_rewrite_integrity=trusted;

Session altered.

SCOTT@orcl> select e.ename, d.dname from emp e, dept d 
2  wheree.deptno = to_number(d.deptno);

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

--------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost(%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |   14 |   308 |     4  (0)|
|  1 |  NESTED LOOPS                |         |   14 |   308 |     4  (0)|
|  2 |   TABLE ACCESS FULL          | EMP     |   14 |   126 |     3  (0)|
|  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    13 |     1  (0)|
|* 4 |    INDEX UNIQUE SCAN         | PK_DEPT |    1 |       |     0  (0)|
--------------------------------------------------------------------------

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

   4 - access("E"."DEPTNO"="D"."DEPTNO")

¡Finalmente funcionó!, vemos como Oracle ha reemplazado un query por otro y ahora sí se usa la clave primaria de la tabla dept. Pero qué tal si éste no es el único código con problemas sino que hay otros similares, ¿se hará o no la transformación? pues hagamos la prueba.

SCOTT@orcl> select e.ename, d.dname from emp e, dept d 
2  wheree.deptno = to_number(d.deptno)
3  ande.ename = 'KING';

ENAME      DNAME
---------- --------------
KING       ACCOUNTING

---------------------------------------------------------------
| Id | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|  0 | SELECT STATEMENT   |      |     1 |    22 |     7  (15)|
|* 1 |  HASH JOIN         |      |     1 |    22 |     7  (15)|
|* 2 |   TABLE ACCESS FULL| EMP  |     1 |     9 |     3   (0)|
|  3 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)|
---------------------------------------------------------------

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

   1 - access("E"."DEPTNO"=TO_NUMBER(TO_CHAR("D"."DEPTNO")))
2 - filter("E"."ENAME"='KING')

¡Ya no funciona! ¿Quiere decir que tendremos que crear tantos mapeos como sentencias hayan? Afortunadamente no, es cuestión de crear el mapeo indicándole a Oracle que no sea tan estricto, para ello usamos el parámetro rewrite_mode cambiándolo de su valor por defecto text_match por el valor general. Primero eliminemos el mapeo que habíamos creado y lo creamos nuevamente con el ajuste requerido.

SCOTT@orcl> execute sys.dbms_advanced_rewrite.drop_rewrite_equivalence('use_index')

PL/SQL procedure successfully completed.

SCOTT@orcl> begin
2  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
3    name => 'use_index',
  4    source_stmt => 'select e.ename, d.dname from emp e, dept d
  5      where e.deptno = to_number(d.deptno)',
  6    destination_stmt => 'select e.ename, d.dname from emp e, dept d
  7      where e.deptno = d.deptno',
  8    rewrite_mode => 'GENERAL');
9  end;
10  /

PL/SQL procedure successfully completed.

Ahora reintentamos con el query anterior.

SCOTT@orcl> select e.ename, d.dname from emp e, dept d
2  wheree.deptno = to_number(d.deptno)
3  ande.ename = 'KING';

ENAME      DNAME
---------- --------------
KING       ACCOUNTING

---------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |    1 |    22 |     4   (0)|
|  1 |  NESTED LOOPS                |         |    1 |    22 |     4   (0)|
|* 2 |   TABLE ACCESS FULL          | EMP     |    1 |     9 |     3   (0)|
|  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    13 |     1   (0)|
|* 4 |    INDEX UNIQUE SCAN         | PK_DEPT |    1 |       |     0   (0)|
---------------------------------------------------------------------------

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

   2 - filter("E"."ENAME"='KING')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

¡Bien! Las cosas vuelven a la normalidad, Oracle hace nuevamente el cambio aun cuando el código proporcionado no es idéntico al indicado durante el mapeo, pero vayamos un poco más allá, hagamos la prueba usando bind variables, después de todo estamos conscientes de que usar literales usualmente no es una buena práctica, ¿seguirá Oracle haciendo el cambio?

SCOTT@orcl> variable enamevarchar2(14)
SCOTT@orcl>execute :ename := 'KING'

PL/SQL procedure successfully completed.

SCOTT@orcl> select e.ename, d.dname from emp e, dept d 
2  wheree.deptno = to_number(d.deptno)
3  ande.ename = :ename;

ENAME      DNAME
---------- --------------
KING       ACCOUNTING

---------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |    1 |    22 |     4   (0)|
|  1 |  NESTED LOOPS                |         |    1 |    22 |     4   (0)|
|* 2 |   TABLE ACCESS FULL          | EMP     |    1 |     9 |     3   (0)|
|  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    13 |     1   (0)|
|* 4 |    INDEX UNIQUE SCAN         | PK_DEPT |    1 |       |     0   (0)|
---------------------------------------------------------------------------

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

   2 - filter("E"."ENAME"=:ENAME)
   4 - access("E"."DEPTNO"="D"."DEPTNO")

¡Perfecto! Todo está saliendo a pedir de boca, pero sigamos forzando la situación y démosle a Oracle un nuevo query para ponerlo a prueba.

SCOTT@orcl> variable empno number
SCOTT@orcl>execute :empno := 7839

PL/SQL procedure successfully completed.

SCOTT@orcl> select e.ename, d.dname from emp e, dept d 
2  wheree.deptno = to_number(d.deptno)
3  ande.ename = :ename
4  ande.empno = :empno;

ENAME      DNAME
---------- --------------
KING       ACCOUNTING

--------------------------------------------------------------------------
| Id | Operation                    | Name   | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |        |    1 |    26 |     4   (0)|
|  1 |  NESTED LOOPS                |        |    1 |    26 |     4   (0)|
|* 2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    13 |     1   (0)|
|* 3 |    INDEX UNIQUE SCAN         | PK_EMP |    1 |       |     0   (0)|
|* 4 |   TABLE ACCESS FULL          | DEPT   |    1 |    13 |     3   (0)|
--------------------------------------------------------------------------

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

   2 - filter("E"."ENAME"=:ENAME)
   3 - access("E"."EMPNO"=TO_NUMBER(:EMPNO))
   4 - filter("E"."DEPTNO"=TO_NUMBER(TO_CHAR("D"."DEPTNO")))

¿Qué pasó? Esto ya parece una telenovela pero afortunadamente la explicación es bastante sencilla: para que la transformación pueda realizarse, se debe hacer referencia a las columnas que están en la sentencia original y si observamos con detenimiento, la columna empno no aparece en ella. Hagamos entonces un mapeo en el que si aparezca, y es más haremos que no sea explícito sino implícito.

SCOTT@orcl> execute sys.dbms_advanced_rewrite.drop_rewrite_equivalence('use_index')

PL/SQL procedure successfully completed.

SCOTT@orcl> begin
2  sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
3    name => 'use_index',
  4    source_stmt => 'select e.*, d.dname from emp e, dept d
  5      where e.deptno = to_number(d.deptno)',
  6    destination_stmt => 'select e.*, d.dname from emp e, dept d 
  7      where e.deptno = d.deptno',
  8    rewrite_mode => 'GENERAL');
9  end; 
10  /

PL/SQL procedure successfully completed.

Ahora sí, al indicar e.* allí estará desde luego la columna empno, hagamos nuevamente la prueba.

SCOTT@orcl> select e.ename, d.dname from emp e, dept d 
2  wheree.deptno = to_number(d.deptno)
3  ande.ename = :ename
4  ande.empno = :empno;

ENAME      DNAME
---------- --------------
KING       ACCOUNTING

---------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |    1 |    26 |     2   (0)|
|  1 |  NESTED LOOPS                |         |    1 |    26 |     2   (0)|
|* 2 |   TABLE ACCESS BY INDEX ROWID| EMP     |    1 |    13 |     1   (0)|
|* 3 |    INDEX UNIQUE SCAN         | PK_EMP  |    1 |       |     0   (0)|
|  4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |    4 |    52 |     1   (0)|
|* 5 |    INDEX UNIQUE SCAN         | PK_DEPT |    1 |       |     0   (0)|
---------------------------------------------------------------------------

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

   2 - filter("E"."ENAME"=:ENAME)
   3 - access("E"."EMPNO"=TO_NUMBER(:EMPNO))
5 - access("E"."DEPTNO"="D"."DEPTNO")

Simplemente ¡magnífico!. No tuvimos que escribir en nuestro querye.* sino que escogimos algunas de las columnas de emp como son e.ename y e.empno y Oracle no encontró problemas en hacer la conversión.

Qué lástima, justo cuando la cosa se ponía interesante llegamos al final de esta demostración, pero espero que les haya motivado a investigar más sobre el tema y desde luego agreguen a dbms_advanced_rewrite a su arsenal de armas en la eterna guerra contra el código ineficiente, ¡suerte en la batalla!

 


Enrique Orbegozo tiene más de 18 años de experiencia con productos Oracle. El provee servicios de consultoría independiente, enfocados en el diseño e implementación de soluciones de alta disponibilidad y contingencia, así como migraciones y upgrades con zerodowntime, pero también destaca en el afinamiento de aplicaciones, gracias a su experiencia previa diseñando y codificando rutinas altamente optimizadas en SQL y PL/SQL. En 1999 recibió el Oracle ACE Award, en reconocimiento a su trayectoria y gran disposición a compartir sus conocimientos y experiencia, ya sea en conferencias, foros o en su blog SnapshotToo Old.