Oracle Live

02/04/2016

Purgar cursor en la shared pool

Filed under: Database,Debugger,SQL Tuning — mogukiller @ 1:32 am

Cuando tenemos una query que ha cambiado su plan de ejecucion y queremos comprobar si los cambios que hemos hecho favorecen que vuelva a coger el plan de ejecución bueno, es necesario que primero invalidemos el cursor que se encuentra almacenado en la shared.

Version: 11.2.0.4

-- Creamos el entorno.

CREATE TABLESPACE PRUEBAS DATAFILE '+DG_DATOS' SIZE 100M;
CREATE USER MOGU IDENTIFIED BY temporal DEFAULT TABLESPACE PRUEBAS;
GRANT DBA TO MOGU;

sqlplus mogu/temporal

CREATE TABLE SP_TABLE 
AS
SELECT
  dbms_random.random AS ID_VALOR,
  dbms_random.string('u',25) AS VALOR 
FROM dual connect by level <110000;
 
UPDATE SP_TABLE SET ID_VALOR=1 WHERE ID_VALOR<0;
commit;
 
CREATE INDEX IDX_ID_VALOR ON SP_TABLE(ID_VALOR);

-- Lanzamos una query sobre SP_TABLE
cat query_sq.sql

SELECT /*SP*/ count(*) FROM SP_TABLE where ID_VALOR=10;

--Queries en la shared_pool
cat info_cursor.sql

col text for a30
SELECT 	SQL_ID,substr(SQL_TEXT,0,30) as text ,FETCHES,EXECUTIONS,PARSE_CALLS,END_OF_FETCH_COUNT,LOADS,FIRST_LOAD_TIME,LAST_LOAD_TIME,INVALIDATIONS FROM V$SQLAREA WHERE sql_text like 'SELECT /*SP%';
/*
SQL_ID        TEXT                              FETCHES EXECUTIONS PARSE_CALLS END_OF_FETCH_COUNT      LOADS FIRST_LOAD_TIME     LAST_LOAD_TIME      INVALIDATIONS
------------- ------------------------------ ---------- ---------- ----------- ------------------ ---------- ------------------- ------------------- -------------
c8g4budf6m2yz SELECT /*SP count(*) FROM SP          1          1           1                  1          1 2016-03-09/12:56:08 2016/03/09 12:56:08             0
*/

1.- Metodo: flush de la shared
'nota: Se aplica en versiones antiguas, invalida todos los cursores de la shared'

ALTER SYSTEM FLUSH SHARED_POOL;

@info_cursor.sql
/*
no rows selected
*/
@query_sq.sql
@info_cursor.sql
/*
SQL_ID        TEXT                              FETCHES EXECUTIONS PARSE_CALLS END_OF_FETCH_COUNT      LOADS FIRST_LOAD_TIME     LAST_LOAD_TIME      INVALIDATIONS
------------- ------------------------------ ---------- ---------- ----------- ------------------ ---------- ------------------- ------------------- -------------
c8g4budf6m2yz SELECT /*SP count(*) FROM SP          1          1           1                  1          2 2016-03-09/12:59:34 2016/03/09 13:57:05             1		<<<< Indica las veces que se ha invalidado.
*/

El flush de la shared provoca que se invaliden todos los cursores. Para invalidar unicamente el que queremos utilizamos el paquete DBMS_SHARED_POOL.
Por defecto este paquete no esta instalado por lo que deberíamos ejecutar:

SQL> @?/rdbms/admin/dbmspool.sql

Comencemos viendo un ejemplo en 11gR1:


SQL> desc DBMS_SHARED_POOL
/*
PROCEDURE PURGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN						<<<<< V$SQLAREA [ADDRESS,HASH_VALUE]
 FLAG                           CHAR                    IN     DEFAULT			<<<<< 'C' para un cursor
 HEAPS                          NUMBER                  IN     DEFAULT
PROCEDURE PURGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA                         VARCHAR2                IN
 OBJNAME                        VARCHAR2                IN						<<<<< Nombre del objeto a ser purgado
 NAMESPACE                      NUMBER                  IN
 HEAPS                          NUMBER                  IN
PROCEDURE PURGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HASH                           VARCHAR2                IN
 NAMESPACE                      NUMBER                  IN
 HEAPS                          NUMBER                  IN
*/


SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID='c8g4budf6m2yz';
/*
ADDRESS          HASH_VALUE
---------------- ----------
000000015EB33A20 1550420959
*/

EXEC DBMS_SHARED_POOL.PURGE(NAME =>'000000015EB33A20,1550420959', FLAG=>'C');

@info_cursor.sql
/*
no rows selected
*/
@query_sq.sql
@info_cursor.sql
/*
SQL_ID        TEXT                              FETCHES EXECUTIONS PARSE_CALLS END_OF_FETCH_COUNT      LOADS FIRST_LOAD_TIME     LAST_LOAD_TIME      INVALIDATIONS
------------- ------------------------------ ---------- ---------- ----------- ------------------ ---------- ------------------- ------------------- -------------
c8g4budf6m2yz SELECT /*SP count(*) FROM SP          1          1           1                  1          3 2016-03-09/12:59:34 2016/03/09 15:38:06             2
*/


-- Purgar cursores de la shared en una Oracle 10g

alter session set events '5614566 trace name context forever'; -- bug 5614566
exec dbms_shared_pool.purge(000000060BE09FD8,4264204289,'C');

-- Invalidar todos los cursores dependientes de una tabla

cat query_sq2.sql

SELECT /*SP2*/ count(*) FROM SP_TABLE where ID_VALOR=10 and valor like '%A%';

@info_cursor.sql
/*
SQL_ID        TEXT                              FETCHES EXECUTIONS PARSE_CALLS END_OF_FETCH_COUNT      LOADS FIRST_LOAD_TIME     LAST_LOAD_TIME      INVALIDATIONS
------------- ------------------------------ ---------- ---------- ----------- ------------------ ---------- ------------------- ------------------- -------------
c8g4budf6m2yz SELECT /*SP count(*) FROM SP          1          1           1                  1          3 2016-03-09/12:59:34 2016/03/09 15:38:06             2
2pjz0tdrwbj3a SELECT /*SP2 count(*) FROM S          1          1           1                  1          1 2016-03-09/15:44:27 2016/03/09 15:44:27             0
*/

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        OWNNAME =>'MOGU',
        TABNAME =>'SP_TABLE',
        NO_INVALIDATE =>FALSE);        <<<< Invalida todos los cursores dependientes de la tabla.
END;
/
/*
SQL_ID        TEXT                              FETCHES EXECUTIONS PARSE_CALLS END_OF_FETCH_COUNT      LOADS FIRST_LOAD_TIME     LAST_LOAD_TIME      INVALIDATIONS
------------- ------------------------------ ---------- ---------- ----------- ------------------ ---------- ------------------- ------------------- -------------
c8g4budf6m2yz SELECT /*SP count(*) FROM SP          1          1           1                  1          3 2016-03-09/12:59:34 2016/03/09 15:38:06             3		<<< No purga el cursor pero lo invalida
2pjz0tdrwbj3a SELECT /*SP2 count(*) FROM S          1          1           1                  1          1 2016-03-09/15:44:27 2016/03/09 15:44:27             1
*/

@query_sq2.sql
@info_cursor.sql
/*
SQL_ID        TEXT                              FETCHES EXECUTIONS PARSE_CALLS END_OF_FETCH_COUNT      LOADS FIRST_LOAD_TIME     LAST_LOAD_TIME      INVALIDATIONS
------------- ------------------------------ ---------- ---------- ----------- ------------------ ---------- ------------------- ------------------- -------------
c8g4budf6m2yz SELECT /*SP count(*) FROM SP          1          1           1                  1          3 2016-03-09/12:59:34 2016/03/09 15:38:06             3
2pjz0tdrwbj3a SELECT /*SP2 count(*) FROM S          1          1           1                  1          2 2016-03-09/15:44:27 2016/03/09 15:49:50             1	  <<<< Se vuelve a cargar el cursor
*/

'nota: Otra forma de invalidar todos los cursores dependientes de una tabla es añadir un comentario'

COMMENT ON TABLE MOGU.SP_TABLE IS 'Invalidamos cursores';

/*
SQL_ID        TEXT                              FETCHES EXECUTIONS PARSE_CALLS END_OF_FETCH_COUNT      LOADS FIRST_LOAD_TIME     LAST_LOAD_TIME      INVALIDATIONS
------------- ------------------------------ ---------- ---------- ----------- ------------------ ---------- ------------------- ------------------- -------------
c8g4budf6m2yz SELECT /*SP count(*) FROM SP          1          1           1                  1          5 2016-03-09/12:59:34 2016/03/09 16:00:44             4	<<<<< Los invalida y en la siguiente ejecucion -
2pjz0tdrwbj3a SELECT /*SP2 count(*) FROM S          1          1           1                  1          4 2016-03-09/15:44:27 2016/03/09 16:00:51             3	<<<<< los vuelve a cargar 

*/

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: