Eliminar una consulta de la Shared Pool

A partir de Oracle 10.2.0.4 en adelante, el paquete DBMS_SHARED_POOL contiene nuevos procedimientos. Uno de esos procedimientos se llama PURGE y es el encargado de eliminar determinados objetos de la Shared Pool. Estos objetos pueden ser: cursores (consultas SQL), paquetes, procedimientos, funciones, triggers, secuencias y tipos.
En versiones anterior, esto no podíamos hacerlo y lo que debíamos hacer era eliminar todas las consultas de la Shared Pool mediante la sentencia "ALTER SYSTEM FLUSH SHARED_POOL".

La creación del paquete DBMS_SHARED_POOL podemos encontrarla en $ORACLE_HOME/rdbms/admin/dbmspool.sql

En Oracle 10.2.0.4 existe el bug 5614566 que provoca el mal funcionamiento del procedimiento PURGE. Pero, tenemos un manera de evitar este bug en esa versión.

Comencemos viendo un ejemplo en 11gR1:

SQL> desc DBMS_SHARED_POOL

PROCEDURE ABORTED_REQUEST_THRESHOLD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THRESHOLD_SIZE                 NUMBER                  IN
PROCEDURE KEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT
PROCEDURE PURGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT
 HEAPS                          NUMBER                  IN     DEFAULT
PROCEDURE SIZES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MINSIZE                        NUMBER                  IN
PROCEDURE UNKEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT

Para ejecutar el procedimiento PURGE, debemos pasar como valor en el parámetro NAME el ADDRESS junto con el HASH_VALUE separados por una coma(,).
Estos valores podemos encontrarlos en la vista V$SQLAREA.

SQL> SELECT /* prueba */ 1 FROM DUAL;

         1
----------
         1

SQL> SELECT address||','||hash_value name 
     FROM v$sqlarea 
     WHERE sql_text = 'SELECT /* prueba */ 1 FROM DUAL';

NAME
-------------------
2E10E868,4198164882

Como podemos ver, lo que hicimos fue ejecutar una consulta para que sea parseada por primera vez y almacenada en la Shared Pool. Luego buscamos en la vista V$SQLAREA los valores que debo pasar en el parámetro NAME del procedimiento PURGE.

Ahora vamos a eliminar la consulta de la Shared Pool y a verificar que se haya eliminado.

SQL> exec dbms_shared_pool.purge('2E10E868,4198164882','C',1);

PL/SQL procedure successfully completed.

SQL> SELECT address||','||hash_value name 
     FROM v$sqlarea 
     WHERE sql_text = 'SELECT /* prueba */ 1 FROM DUAL';

no rows selected

La consulta fue eliminada con éxito!

Para hacer funcionar el procedimiento en 10.2.0.4, debemos realizar el siguiente alter en la sesión antes de ejecutar el procedimiento PURGE:

SQL> alter session set events '5614566 trace name context forever';

Pueden utilizar el siguiente script para hacer el purge de un determinado cursor de la Shared Pool:

DECLARE
 name    varchar2(50);
 version varchar2(3);
BEGIN
 select regexp_replace(version,'\..*') 
 into version 
 from v$instance;

 if version = '10' then
   execute immediate
   q'[alter session set events '5614566 trace name context forever']'; -- bug 5614566
 end if;

 select address||','||hash_value 
 into name
 from v$sqlarea
 where sql_id like '&sql_id';

 dbms_shared_pool.purge(name,'C',1);
END;
/

Diferencias entre BETWEEN y >= <=

Muchas personas prefieren utilizar el BETWEEN en vez de >= <= y otras personas prefieren usar lo contrario porque suponen que hay alguna diferencia a nivel de performance. Esto es un mito muy antiguo. La realidad es que no existen diferencias.

BETWEEN es una "sinónimo" de >= <=

Voy a demostrar esto con una sencilla prueba:

SQL> CREATE TABLE test AS
  2  SELECT level id, 'nombre_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 100;

Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> DESC test

 Name          Null?    Type
 ------------- -------- --------------
 ID                     NUMBER
 NOMBRE                 VARCHAR2(47)

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      7 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"<=25 AND "ID">=20))

Como podemos ver en la sección "Predicate Information", el optimizador reemplazó la sentencia BETWEEN por "ID"<=25 AND "ID">=20.
También podemos ver, que leyó 4 bloques de datos para recuperar los 6 registros mediante un acceso FULL SCAN.

Ahora, probemos colocando el >= <= en la consulta ...

SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      7 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"<=25 AND "ID">=20))   

Acá volvemos a ver en la a sección "Predicate Information" que el optimizador no realizó ningún reemplazo.
Seguimos leyendo 4 bloques de datos para recuperar los 6 registros mediante un acceso FULL SCAN.

Una cosa muy importante a destacar entre estas 2 consultas es que el hash value de ambas es el mismo (1357081020). Esto nos indica que la utilización de BETWEEN o >= <= es transparente para el optimizador ya que siempre va a utilizar >= <= para resolver la consulta.

Qué sucede si creamos un índice en la tabla? Obtendremos alguna diferencia?

SQL> CREATE UNIQUE INDEX test_uq ON test(id, nombre);

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1', cascade => true);

PL/SQL procedure successfully completed.

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1087767317

--------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| TEST_UQ |      1 |      7 |      6 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID">=20 AND "ID"<=25)

Vemos que se volvió a reemplazar el BETWEEN por >= <= y ahora como estamos accediendo por índice, leemos solamente 2 bloques de datos.

Qué sucederá si modificamos nuevamente la consulta?

SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

sys@orcl> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1087767317

--------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| TEST_UQ |      1 |      7 |      6 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID">=20 AND "ID"<=25)

Obtuvimos exactamente el mismo resultado que con el BETWEEN.
El hash value entre las 2 consultas sigue siendo el mismo (1087767317).

En esta prueba, colocamos el campo numérico ID en el WHERE pero qué sucedería si utilizamos un campo caracter?

SQL> DROP TABLE test;

Table dropped.

SQL> CREATE TABLE test AS
  2  SELECT to_char(level) id, 'nombre_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 100;

Table created.

SQL> DESC test

 Name          Null?    Type
 ------------- -------- --------------
 ID                     VARCHAR2(40)
 NOMBRE                 VARCHAR2(47)

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      1 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((TO_NUMBER("ID")>=20 AND TO_NUMBER("ID")<=25))
   
SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
-----------------------------------------------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      1 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((TO_NUMBER("ID")>=20 AND TO_NUMBER("ID")<=25))

Como observamos, ambas consultas siguen siendo idénticas.


Estas pruebas nos indican que no existe diferencia alguna entre la utilización del BETWEEN y el >= <=