Oracle Database Tuning: Mi tabla Mágica.!!!

Este fin de semana vamos a realizar un poco de magia.

Pero para realizar magia necesitamos a un mago, así que vamos a crear una cuenta común y corriente en nuestra base de de datos.

login as: root
root@10.0.0.1's password:
Last login: Mon May 25 18:29:22 2015
[root@dbvisit01 ~]# su - oracle
[oracle@dbvisit01 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Mon May 25 18:36:34 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> create user mago identified by magia;

User created.

Ahora sí, tenemos a mi mago dentro de la base de datos. Sin embargo, es necesario que le brinde algunos permisos adicionales de sistema, como que pueda crear procedimientos, funciones, llamar a algunas funciones del esquema "SYS" y no más.

SQL> grant create session to mago;

Grant succeeded.

SQL> alter user mago quota unlimited on users;

User altered.

SQL> grant create table to mago;

Grant succeeded.

Vamos a conectarnos con nuestro mago virtual y vamos a crear dos tablas idénticas, sólo que una de ellas será mágica.

SQL> connect mago/magia
Connected.
SQL> create table tabla_normal( orden number, nombre varchar2(10));

Table created.

SQL> create table tabla_magica( orden number, nombre varchar2(10));

Table created.

Vamos a agregar tan sólo 3 registros a cada una de las tablas.

SQL> insert into tabla_normal values(&secuencia, '&nombre');
Enter value for secuencia: 1
Enter value for nombre: EVA
old   1: insert into tabla_normal values(&secuencia, '&nombre')
new   1: insert into tabla_normal values(1, 'EVA')

1 row created.

SQL> /
Enter value for secuencia: 2
Enter value for nombre: ADAN
old   1: insert into tabla_normal values(&secuencia, '&nombre')
new   1: insert into tabla_normal values(2, 'ADAN')

1 row created.

SQL> /
Enter value for secuencia: 3
Enter value for nombre: PARAISO
old   1: insert into tabla_normal values(&secuencia, '&nombre')
new   1: insert into tabla_normal values(3, 'PARAISO')

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> insert into tabla_magica values(&secuencia, '&nombre');
Enter value for secuencia: 1
Enter value for nombre: EVA
old   1: insert into tabla_magica values(&secuencia, '&nombre')
new   1: insert into tabla_magica values(1, 'EVA')

1 row created.

SQL> /
Enter value for secuencia: 2
Enter value for nombre: ADAN
old   1: insert into tabla_magica values(&secuencia, '&nombre')
new   1: insert into tabla_magica values(2, 'ADAN')

1 row created.

SQL> /
Enter value for secuencia: 3
Enter value for nombre: PARAISO
old   1: insert into tabla_magica values(&secuencia, '&nombre')
new   1: insert into tabla_magica values(3, 'PARAISO')

1 row created.

SQL> commit;

Commit complete.

Ahora vamos a crear una función, que reciba como parámetro un valor numérico y espere un segundo antes de continuar con la siguiente ejecución. Para esto hemos brindado previamente el privilegio CREATE PROCEDURE y hemos dado permisos de ejecución sobre dbms_lock.

SQL> create or replace function un_segundo_espera ( val in number)
return number as
begin
dbms_lock.sleep(1);
return val;
end un_segundo_espera;
/

Recordemos, que nuestras tablas tienen una estructura de dos columnas únicamente.
SQL> set timing on

SQL> desc tabla_normal
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDEN                                              NUMBER
 NOMBRE                                             VARCHAR2(10)

Utilicemos la función para desplegar el valor de la columna "ORDEN" en la tabla normal, con la espera de 1 segundo entre tuplas.

SQL> select un_segundo_espera(orden) from tabla_normal;

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:03.02

Como pueden ver, el tiempo de ejecución es de 3 segundos, para 3 registros.

Volvamos a ejecutar la consulta.

SQL> /

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:03.00

Vamos consistentes con los tiempos de ejecución.

Ahora hagamos la misma consulta con la tabla mágica.

SQL> select un_segundo_espera(orden) from tabla_magica;

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:00.00

Que pasó.? Como es que la ejecución en la tabla mágica dura 0 segundos.?

Alguien esta haciendo trampa.!!!

Bueno la verdad no estamos haciendo trampa. ES MAGIA.!!!

Desde la versión de base de datos Oracle 11gR2, existe la característica que permite enviar a memoria, resultados generados sobre tablas de la base de datos. Esta característica se habilita utilizando la instrucción result_cache con el parámetro "mode force".

SQL> alter table tabla_magica result_cache ( mode force);

Table altered.

Elapsed: 00:00:00.03

Cuando la consulta es ejecutada por primera vez, vamos a experimentar un retardo de 2 veces el tiempo normal de ejecución. Esto es necesario para popular los registros en memoria.

SQL> select un_segundo_espera(orden) from tabla_magica;

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:06.02

Pero una vez hechos públicos los resultados en memoria, no existe tiempo de ejecución a nivel de la base de datos, para cada una de las consultas posteriores.

SQL> select un_segundo_espera(orden) from tabla_magica;

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:00.00

Vamos otra vez, para ver si es cierto.

SQL> /

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:00.00

Y si me salgo del SQL*Plus o la aplicación, se mantienen los datos en memoria.?

Veamos la prueba, para estar más seguros.

[oracle@dbvisit01 ~]$ sqlplus mago/magia

SQL*Plus: Release 12.1.0.2.0 Production on Mon May 25 19:01:20 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Mon May 25 2015 18:53:10 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TABLA_NORMAL                   TABLE
TABLA_MAGICA                   TABLE

SQL> set timing on

Ejecutemos la consulta llamando a la función.

SQL> select un_segundo_espera(orden) from tabla_magica;

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:00.00

Que pasa si la base de datos se reinicia o si limpiamos el cache de resultados de la instancia de la base de datos.?

Pues, sería como si volviéramos a empezar. La primera ejecución deberá primero hacer públicos el resultado de mi consulta antes de obtener los mismos tiempos de respuesta.

Con la siguiente instrucción podemos eliminar los datos hechos públicos en el cache de memoria.

SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

Ejecutamos por primera vez la consulta con el resultado de los 6 segundos en el tiempo de ejecución.

SQL>  select un_segundo_espera(orden) from tabla_magica;

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:06.02

Para la segunda ejecución y las siguientes, el beneficio de nuestro acto de "MAGIA", se podrá ver.

SQL> select un_segundo_espera(orden) from tabla_magica;

UN_SEGUNDO_ESPERA(ORDEN)
------------------------
                       1
                       2
                       3

Elapsed: 00:00:00.00