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';
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
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
*************************************************************************
*************************************************************************
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
*************************************************************************
