Invalidar/echar a los cursores en Oracle 11g

Alguno preguntara que sentido tiene invalidar un cursor del shared pool pues, si tienes el bind peeking habilitado tiene mucho sentido! Porque invalidando un cursor invalidas el plan de ejecucion y obligas al optimizador reparsear y generar un plan de ejecucion nuevo. Es muy util para la situacion de la tabla de factura que he mencionado en el otro post Mas alla de Bind Peeking

Las versiones anteriores de 11g la unica manera que yo sepa para invalidar los cursores es lanzando cualquier tipo de DDL (no destructivo claro) sobre una de las tablas del cursor que quieres invalidar. Por ejemplo para invalidar una query como esta:

select /* PURGE */
ename, dname
from emp, dept
where emp.deptno = dept.deptno
and emp.deptno = :deptno;

Una manera de invalidar seria alter table emp logging. Cual es el problema con este metodo? Pues que invalida todos los cursores que referencia a la tabla emp, en un entorno con bastante concurrencia esto significa un pico de Parsing y posibles contenciones.

Hoy por casualidad he estado mirando el package DBMS_SHARED_POOL de 11g para ver si ya se puede usar sql_id en vez de hash value para ciertas operaciones y veo que existe tiene un procedimiento nuevo, PURGE y te permite “echar” el cursor del shared pool. Viendolo me vino a la cabeza lo que podria hacer con eso!

Ejemplo (abrimos dos sesiones uno lanzando la query y la otra monitorizando v$sqlarea con sys/system):


----------
PASO 1
----------
sesion 1 (lanzamos dos sentencias referenciando a la tabla emp, una con JOIN y la otra sin):

var deptno number
exec :deptno := 30

select  /* PURGE */
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno;

select  /* PURGE */
   empno, ename
from emp
where emp.deptno = :deptno;

sesion 2:

select sql_fulltext, executions, loads, VERSION_COUNT, invalidations, hash_value, address, plan_hash_value, parse_calls
from v$sqlarea
where sql_text like '%PURGE%'
and PARSING_SCHEMA_NAME  = 'LSC';

SQL_FULLTEXT                             EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS HASH_VALUE ADDRESS  PLAN_HASH_VALUE PARSE_CALLS
---------------------------------------- ---------- ---------- ------------- ------------- ---------- -------- --------------- -----------
select  /* PURGE */                               1          1             1             0 2983748266 2DD77FA0      1274118195           1
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno

select  /* PURGE */                               1          1             1             0 4001409383 2DD756EC      2204715243           1
   empno, ename
from emp
where emp.deptno = :deptno

2 rows selected.

----------
PASO 2
----------
A la antigua usanza e invalidamos el cursor:

sesion 1:
alter table emp logging;

sesion 2:
select sql_fulltext, executions, loads, VERSION_COUNT, invalidations, hash_value, address, plan_hash_value, parse_calls
from v$sqlarea
where sql_text like '%PURGE%'
and PARSING_SCHEMA_NAME  = 'LSC';

SQL_FULLTEXT                             EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS HASH_VALUE ADDRESS  PLAN_HASH_VALUE PARSE_CALLS
---------------------------------------- ---------- ---------- ------------- ------------- ---------- -------- --------------- -----------
select  /* PURGE */                               1          1             1             1 2983748266 2DD77FA0      1274118195           1
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno

select  /* PURGE */                               1          1             1             1 4001409383 2DD756EC      2204715243           1
   empno, ename
from emp
where emp.deptno = :deptno

2 rows selected.

en el campo V$SQLAREA.INVALIDATIONS ha incrementado de 0 a 1, acabamos de invalidar los cursores, si volvemos a ejecutar la query veremos como incrementa el VERSION_COUNT de 1 a 2 e INVALIDATIONS se resetea a cero. Como se puede comprobar se han invalidado los dos cursores por el DDL.

----------
PASO 3
----------
Con el metodo nuevo DBMS_SHARED_POOL invalidamos la Query con la JOIN:

sesion 2:

alter system flush shared_pool;

sesion 1:

select  /* PURGE */
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno;

select  /* PURGE */
   empno, ename
from emp
where emp.deptno = :deptno;

sesion 2:

select sql_fulltext, executions, loads, VERSION_COUNT, invalidations, hash_value, address, plan_hash_value, parse_calls
from v$sqlarea
where sql_text like '%PURGE%'
and PARSING_SCHEMA_NAME  = 'LSC'

SQL_FULLTEXT                             EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS HASH_VALUE ADDRESS  PLAN_HASH_VALUE PARSE_CALLS
---------------------------------------- ---------- ---------- ------------- ------------- ---------- -------- --------------- -----------
select  /* PURGE */                               1          1             1             0 2983748266 2DD77FA0      1274118195           1
   ename, dname
from       emp, dept
where      emp.deptno = dept.deptno
and        emp.deptno = :deptno

select  /* PURGE */                               1          3             2             2 4001409383 2DD756EC      2204715243           1
   empno, ename
from emp
where emp.deptno = :deptno

Sesion 2:
exec dbms_shared_pool.purge(name => '2DD77FA0, 2983748266', flag => 'C')

select sql_fulltext, executions, loads, VERSION_COUNT, invalidations, hash_value, address, plan_hash_value, parse_calls
from v$sqlarea
where sql_text like '%PURGE%'
and PARSING_SCHEMA_NAME  = 'LSC'

SQL_FULLTEXT                             EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS HASH_VALUE ADDRESS  PLAN_HASH_VALUE PARSE_CALLS
---------------------------------------- ---------- ---------- ------------- ------------- ---------- -------- --------------- -----------
select  /* PURGE */                               2          3             2             2 4001409383 2DD756EC      2204715243           2
   empno, ename
from emp
where emp.deptno = :deptno

1 row selected.

Vemos que el cursor de la JOIN ha desaparecido del shared pool. Semanticamente hablando la verdad es que lo que hace el package es echar y no invalidar…. pero bueno la idea es obtener un plan de ejecucion nuevo.

One thought on “Invalidar/echar a los cursores en Oracle 11g

  1. roque

    maestro vos sabes que tengo un problema , tengo mucho parseo en mi bd , y los quiero minimizar , esta todo en pl/sql , pero el problema es que los stores devuelven un cursor de la forma
    open nombre_cursor for
    consulta

    y cada evz que llaman al paquete me parsea esa consulta , pero dela forma cursor pepe is (select * from t1) si me lo comparte , solo que un cursor explicito no lo puedo retornar , no se si me explico.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s