Ajustes de Rendimiento:Detectar sentencias SQL no compartidas en la Shared Pool

Una de las maneras de aumentar la escalabilidad y el rendimiento de una aplicación, consiste en velar el número de sentencias SQL que están en la Shared Pool, región de la SGA (Área Global del Sistema) es una estructura básica de memoria de Oracle que sirve para facilitar la transferencia de información entre usuarios y también almacena la información estructural de la base de datos, más frecuentemente requerida. En versiones anteriores a 10g de la vista V_$SQL_SHARED_CURSOR ayudaría a diagnosticar por qué sentencias SQL no estaban siendo compartidas. En la versión 10g de a base de datos hay un evento llamado CURSORTRACE que ayuda en el mismo.

 

Área de SQL Compartido, Shared SQL Pool

En esta zona se encuentran las sentencias SQL que han sido analizadas. El análisis sintáctico de las sentencias SQL lleva su tiempo y Oracle mantiene las estructuras asociadas a cada sentencia SQL analizada durante el tiempo que pueda para ver si puede reutilizarlas.

Antes de analizar una sentencia SQL, Oracle mira a ver si encuentra otra sentencia exactamente igual en la zona de SQL compartido. Si es así, no la analiza y pasa directamente a ejecutar la que mantiene en memoria. De esta manera se premia la uniformidad en la programación de las aplicaciones. La igualdad se entiende que es lexicográfica, espacios en blanco y variables incluidas.

La base de datos Oracle asigna memoria a la shared pool cuando una nueva instrucción sql se analiza. El tamaño de esta memoria depende de la complejidad de la instrucción. Si toda la shared pool ya ha sido asignada la base de datos Oracle puede liberar elementos de la shared pool hasta que haya suficiente espacio libre para nuevas sentencias. Al liberar un elemento de la shared pool el sql asociado debe ser recompilado y reasignado a otra área de sql compartida la próxima vez que se ejecute.

El contenido de la zona de SQL compartido es:

• Las sentencias SQL y PL/SQL (texto de la sentencia
• Plan de ejecución de la sentencia SQL.
• Lista de objetos referenciados.

Los pasos de procesamiento de cada petición de análisis de una sentencia SQL son:

• Comprobar si la sentencia se encuentra en el área compartida.
• Comprobar si los objetos son los mismos
• Comprobar si el usuario tiene acceso a dichos objetos. En caso negativo, la sentencia es nueva, se analiza y los datos de análisis se almacenan de nuevo en la zona de SQL compartida.

Creamos un usuario Dummy para ilustrar nuestra explicación con un ejemplo:

SQL> create user dummy identified by dummy;

Usuario creado.

SQL> create table dummy.t (col1 varchar2(20) not null);

Tabla creada. 

Una vez creadas las tablas que nos servirán de ejemplo. Realizamos una consulta sobre la tabla creada, a fin de que quede reflejada en la Shared Pool, en la sesión de sqlplus #1.

Conectado a:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SQL> select count(*) from dummy.t;

  COUNT(*)

----------

         0

SQL> select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from dummy.t%';

SQL_TEXT                      HASH_VALUE ADDRESS

------------                  ---------- --------

select count(*) from dummy.t  2598640170 261A0758

SQL>  select * from v$sql_shared_cursor where address = '261A0758';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T

------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -

B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

R L H P B

- - - - -

fsy6q4ydf86ja 261A0758 261A04C4            0 N N N N N N N N N N N N N N N N N N

N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

N N N N N

 
 Vemos el primer "hijo" de la sentencia SQL ejecutada

En la sesión de sqlplus #2

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set optimizer_mode='RULE';

Sesión modificada.

SQL> select count(*) from dummy.t;

  COUNT(*)
----------
         0

SQL>  select * from v$sql_shared_cursor where address = '261A0758';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T

------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -

B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
R L H P B
- - - - -
fsy6q4ydf86ja 261A0758 261A04C4            0 N N N N N N N N N N N N N N N N N N

N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N N

fsy6q4ydf86ja 261A0758 2F626944            1 N N N N N N N N N N N N N N N N N N

N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N
N N N N N

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T

------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -

B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
R L H P B
- - - - -


SQL> alter session set events 'immediate trace name cursortrace level 612, address 2598640170';

Sesi¾n modificada.

SQL> alter session set optimizer_mode='FIRST_ROWS_1';

Session altered.

SQL> select count(*) from dummy.t;

  COUNT(*)
----------
         0

 

Esta es una porción del fichero de trazas, que arroja luz de porqué la consulta no está compartida en memoria:
*************************************************************************
PARSING SQLTEXT=select count(*) from dummy.t
SQLHASH=2d5ea7a8
Checking for already pinned child.
No valid child pinned
Parent 1EA853E8(261A0758) ready for search
kksSearchChildList outside while loop
kksCheckCursor: next child is #1
kksCheckCursor: pinning child #1 in shared mode 1eb62650 1ba90164
Compilation environment difference Failed sharing : 0
optimizer_mode = first_rows_1 rule
sqlstat_enabled = false true
SQL pgadep:0 pgapls:0 user
Failed sharing : Compilation environment mismatch
kksUnlockChild: releasing child
*************************************************************************