Version Count

Qué son los version count?

Lo primero hay que hay que recordar es todas las SQL son compartidas implícitamente. Cuando una sentencia SQL se ejecuta en la base de datos, el RDBMS crea un valor hash para el texto de la sentencia y ese has ayuda al RDMBMS a encontrar fácilmente el SQL si ya esta en la shared pool.

Por ejemplo: :- ‘select count(*) from emp’ obtiene el valor 8086390035

Se crea un cursor padre para la sql y un solo cursos hijo. No importa que la sentencia SQL nunca se comparta. Siempre se crea un padre y un hijo, la forma más sencilla es pensar que el cursor padre representa el valor hash y el cursor hijo representa los metadatos para esa sql.

Los metadatos serian toda la información necesaria para que esa select se ejecute. Por ejemplo en la sentencia que hemos puesto la tabla EMP  es de Scott. Tendríamos que pensar que el cursor padre se puede compartir pero el hijo depende de los permisos que tenga el usuario.  Por ejemplo para la sentencia dada los metadatos que están en el cursor hijo no serían iguales si los usuarios son diferentes. Con lo cual si el usuario pepito ejecutara select count(*) from emp, tendría que crear un nuevo cursor hijo con sus metadatos, con las validaciones necesarias para la ejecución de la select.
Cuando hay demasiadas versiones innecesarias de un mismo cursor, cada vez que el cursor es ejecutado el motor tiene que revisar entre todos los hijos para ver cuál necesita, esto supone un gasto de CPU, que se podría estar utilizando en otra cosa.

Oracle tiene un script que te permite averiguar porque no se está reutilizando la select. Se ejecuta como sys y te crea algún que otro objeto.

El script esta en metalink

Document 438755.1 High SQL Version Counts – Script to determine reason(s)

Pero aquí pongo una select que hace algo similar sin tener que crear nada. Mostrará todas las razones por la cuan no se está utilizando, luego es necesario buscar la explicación a estas razones. Os paso el enlace de dónde encontrar esa explicación.

https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3059.htm

En este ejemplo buscamos por un sql_id concreto, pero entre comentario esta como buscar por los que tiene un determinado numero de cursores.

set linesize 200

select version_count,address,hash_value,parsing_schema_name,reason,sql_text from 
(select
address,''
||decode(max(    UNBOUND_CURSOR    ),'Y', '     UNBOUND_CURSOR    ')
||decode(max(    SQL_TYPE_MISMATCH    ),'Y', '     SQL_TYPE_MISMATCH    ')
||decode(max(    OPTIMIZER_MISMATCH    ),'Y', '     OPTIMIZER_MISMATCH    ')
||decode(max(    OUTLINE_MISMATCH    ),'Y', '     OUTLINE_MISMATCH    ')
||decode(max(    STATS_ROW_MISMATCH    ),'Y', '     STATS_ROW_MISMATCH    ')
||decode(max(    LITERAL_MISMATCH    ),'Y', '     LITERAL_MISMATCH    ')
||decode(max(    FORCE_HARD_PARSE    ),'Y', '     FORCE_HARD_PARSE    ')
||decode(max(    EXPLAIN_PLAN_CURSOR    ),'Y', '     EXPLAIN_PLAN_CURSOR    ')
||decode(max(    BUFFERED_DML_MISMATCH    ),'Y', '     BUFFERED_DML_MISMATCH    ')
||decode(max(    PDML_ENV_MISMATCH    ),'Y', '     PDML_ENV_MISMATCH    ')
||decode(max(    INST_DRTLD_MISMATCH    ),'Y', '     INST_DRTLD_MISMATCH    ')
||decode(max(    SLAVE_QC_MISMATCH    ),'Y', '     SLAVE_QC_MISMATCH    ')
||decode(max(    TYPECHECK_MISMATCH    ),'Y', '     TYPECHECK_MISMATCH    ')
||decode(max(    AUTH_CHECK_MISMATCH    ),'Y', '     AUTH_CHECK_MISMATCH    ')
||decode(max(    BIND_MISMATCH    ),'Y', '     BIND_MISMATCH    ')
||decode(max(    DESCRIBE_MISMATCH    ),'Y', '     DESCRIBE_MISMATCH    ')
||decode(max(    LANGUAGE_MISMATCH    ),'Y', '     LANGUAGE_MISMATCH    ')
||decode(max(    TRANSLATION_MISMATCH    ),'Y', '     TRANSLATION_MISMATCH    ')
||decode(max(    BIND_EQUIV_FAILURE    ),'Y', '     BIND_EQUIV_FAILURE    ')
||decode(max(    INSUFF_PRIVS    ),'Y', '     INSUFF_PRIVS    ')
||decode(max(    INSUFF_PRIVS_REM    ),'Y', '     INSUFF_PRIVS_REM    ')
||decode(max(    REMOTE_TRANS_MISMATCH    ),'Y', '     REMOTE_TRANS_MISMATCH    ')
||decode(max(    LOGMINER_SESSION_MISMATCH    ),'Y', '     LOGMINER_SESSION_MISMATCH    ')
||decode(max(    INCOMP_LTRL_MISMATCH    ),'Y', '     INCOMP_LTRL_MISMATCH    ')
||decode(max(    OVERLAP_TIME_MISMATCH    ),'Y', '     OVERLAP_TIME_MISMATCH    ')
||decode(max(    EDITION_MISMATCH    ),'Y', '     EDITION_MISMATCH    ')
||decode(max(    MV_QUERY_GEN_MISMATCH    ),'Y', '     MV_QUERY_GEN_MISMATCH    ')
||decode(max(    USER_BIND_PEEK_MISMATCH    ),'Y', '     USER_BIND_PEEK_MISMATCH    ')
||decode(max(    TYPCHK_DEP_MISMATCH    ),'Y', '     TYPCHK_DEP_MISMATCH    ')
||decode(max(    NO_TRIGGER_MISMATCH    ),'Y', '     NO_TRIGGER_MISMATCH    ')
||decode(max(    FLASHBACK_CURSOR    ),'Y', '     FLASHBACK_CURSOR    ')
||decode(max(    ANYDATA_TRANSFORMATION    ),'Y', '     ANYDATA_TRANSFORMATION    ')
||decode(max(    PDDL_ENV_MISMATCH    ),'Y', '     PDDL_ENV_MISMATCH    ')
||decode(max(    TOP_LEVEL_RPI_CURSOR    ),'Y', '     TOP_LEVEL_RPI_CURSOR    ')
||decode(max(    DIFFERENT_LONG_LENGTH    ),'Y', '     DIFFERENT_LONG_LENGTH    ')
||decode(max(    LOGICAL_STANDBY_APPLY    ),'Y', '     LOGICAL_STANDBY_APPLY    ')
||decode(max(    DIFF_CALL_DURN    ),'Y', '     DIFF_CALL_DURN    ')
||decode(max(    BIND_UACS_DIFF    ),'Y', '     BIND_UACS_DIFF    ')
||decode(max(    PLSQL_CMP_SWITCHS_DIFF    ),'Y', '     PLSQL_CMP_SWITCHS_DIFF    ')
||decode(max(    CURSOR_PARTS_MISMATCH    ),'Y', '     CURSOR_PARTS_MISMATCH    ')
||decode(max(    STB_OBJECT_MISMATCH    ),'Y', '     STB_OBJECT_MISMATCH    ')
||decode(max(    CROSSEDITION_TRIGGER_MISMATCH    ),'Y', '     CROSSEDITION_TRIGGER_MISMATCH    ')
||decode(max(    PQ_SLAVE_MISMATCH    ),'Y', '     PQ_SLAVE_MISMATCH    ')
||decode(max(    TOP_LEVEL_DDL_MISMATCH    ),'Y', '     TOP_LEVEL_DDL_MISMATCH    ')
||decode(max(    MULTI_PX_MISMATCH    ),'Y', '     MULTI_PX_MISMATCH    ')
||decode(max(    BIND_PEEKED_PQ_MISMATCH    ),'Y', '     BIND_PEEKED_PQ_MISMATCH    ')
||decode(max(    MV_REWRITE_MISMATCH    ),'Y', '     MV_REWRITE_MISMATCH    ')
||decode(max(    ROLL_INVALID_MISMATCH    ),'Y', '     ROLL_INVALID_MISMATCH    ')
||decode(max(    OPTIMIZER_MODE_MISMATCH    ),'Y', '     OPTIMIZER_MODE_MISMATCH    ')
||decode(max(    PX_MISMATCH    ),'Y', '     PX_MISMATCH    ')
||decode(max(    MV_STALEOBJ_MISMATCH    ),'Y', '     MV_STALEOBJ_MISMATCH    ')
||decode(max(    FLASHBACK_TABLE_MISMATCH    ),'Y', '     FLASHBACK_TABLE_MISMATCH    ')
||decode(max(    LITREP_COMP_MISMATCH    ),'Y', '     LITREP_COMP_MISMATCH    ')
||decode(max(    PLSQL_DEBUG    ),'Y', '     PLSQL_DEBUG    ')
||decode(max(    LOAD_OPTIMIZER_STATS    ),'Y', '     LOAD_OPTIMIZER_STATS    ')
||decode(max(    ACL_MISMATCH    ),'Y', '     ACL_MISMATCH    ')
||decode(max(    FLASHBACK_ARCHIVE_MISMATCH    ),'Y', '     FLASHBACK_ARCHIVE_MISMATCH    ')
||decode(max(    LOCK_USER_SCHEMA_FAILED    ),'Y', '     LOCK_USER_SCHEMA_FAILED    ')
||decode(max(    REMOTE_MAPPING_MISMATCH    ),'Y', '     REMOTE_MAPPING_MISMATCH    ')
||decode(max(    LOAD_RUNTIME_HEAP_FAILED    ),'Y', '    LOAD_RUNTIME_HEAP_FAILED    ')
||decode(max(    HASH_MATCH_FAILED    ),'Y', '   HASH_MATCH_FAILED    ')
||decode(max(    PURGED_CURSOR    ),'Y', '   PURGED_CURSOR    ')
||decode(max(    BIND_LENGTH_UPGRADEABLE    ),'Y', '   BIND_LENGTH_UPGRADEABLE    ')
reason
from gv$sql_shared_cursor
group by
address) join gv$sqlarea using(address) where SQL_ID = '<id>'  /* version_count>5 */
order by version_count desc,address;

Esperas latch: cache buffer chains

Este tipo de contención se da por una sentencia sql lee más buffers de los que necesita y múltiples sesiones esperan para leer el mismo bloque.

Si la contención es muy elevada, se tiene que buscar la sentencia  que lee buffers de más y revisar sus accesos para intentar mejorarla.

La solución suele ser revisar la sql para evitar los accesos innecesarios.

Lo primero para identificar si hay identificar los eventos que provocan esperas, normalmente vemos como las esperas por cache buffers chains van aumentando:

set lines 400 
set pages 400
select count (*), event, max (SECONDS_IN_WAIT) from gv$session_wait group by event;

Con esta select identificaríamos cuál es las sesiones y la select que está provocando las esperas por este evento

SELECT c.status,c.sid, d.piece, c.serial#, c.username,last_call_et segundos, d.sql_text
 FROM v$session c, v$sqltext d
 WHERE c.sql_hash_value = d.hash_value and sid in (select sid from v$session_wait where event='latch: cache buffers chains')
 ORDER BY c.sid, d.piece;

Y a partir de ahí lo que queda es limpiar la select.

Medir el rendimiento de Shared Pool

Este parte de memoria en Oracle se encarga de almacenar en memoria los últimos SQL y PLSQL ejecutados.

Se compone de tres segmentos:

1. Library cache

2. Row cache

3. UGA Si está configurada la opción de Shared Server en vez de conexiones dedicadas.

1. Library cache:

Cuando escribimos la siguiente sentencia:

SQL> SELECT empno, ename 
FROM emp WHERE empno = 1;

Oracle implícitamente realiza las siguientes acciones:

1. Convierte los caracteres del sql en sus correspondientes códigos numéricos de ASCCI

2. Estos caracteres ASCII se pasan a través de un algoritmo de hash el cual produce un único valor.

3. Oracle comprueba que este valor exista en la Shared Pool. Si existe,el proceso de servidor del usuario utiliza la versión cacheada de la sentencia para ejecutarla. Si no tiene que parsear la sentencia.

Parsear de nuevo la sentencia significa un costo extra, ya que se deben volver a ejecutar los siguientes pasos: Comprobación de los errores de sintaxis

Comprobación de los privilegios sobre los objetos que componen la sentencia.

Recoger las estadísticas para los objetos referenciados.

Preparar el plan de ejecución en función de las estadísticas disponibles.

Generar el P code.

Cuando oracle encuentra la sentencia en la Shared Pool se llama Cache hit y cuando no Cache miss. Por tanto aumentar los porcentajes de cache hint y disminuir los de cahe miss es uno de los retos a la hora de ajustar la Shared Pool.

Medir el rendimiento de la library cache requiere del análisis de los ‘HIT RATIOS’.

Esto se hace consultando la vista V$librarycache:

SQL>SELECT namespace, gets,gethits,pins,pinhits,gethitratio,pinhitratio,reloads, invalidations 
           FROM v$librarycache;

Gethitratio

Cada vez que una sentencia es parseada, el valor de GETS en la vista V$LIBRARYCACHE se incrementa en 1. La columna GETHIT almacena el número de veces que el código SQL y PL/SQL es encontrado en la shared pool con lo cual no requiere parsearlo. Por lo tanto porcentajes cercanos al 90 % son muy buenos

Pinhitratio

Mientras el GETS está relacionado con los bloqueos que se producen en tiempo de parseo, el PINS refleja los bloqueos que se producen en tiempo de ejecución. Cada vez que una sentencia es ejecutada, el valor de PINS se incrementa en 1. La columna PINHITRATIO en V$LIBRARYCACHE refleja la frecuencia con la una sentencia ejecutada encuentra su correspondiente parseo. Valores cercanos al 90% son muy óptimos

Reloads

La columna RELOADS muestra el número de veces que una sentencia ejecutada tiene que re-parsed porque la Library Cache esta anticuadao no sirve la versión parseada de la sentencia. Se puede calcular la numero de reloads comparándolo con la sentencias ejecutadas(PINS):

 SQL> SELECT SUM(reloads)/SUM(pins) "Reload Ratio" 
         FROM v$librarycache 
             Reload Ratio
             ------------ 
             .001501321 0.15

significa que no hay sentencias re-parseadas

Invalidation

Las invalidaciones se producen cuando una sentencia cachada se marca como invalida y se le fuerza a parsearse otra vez a pesar de que esta en la library cache porque alguno de los objetos que componen la sentencia ha sido modificado de cualquier manera.

2. ROW Cache:

El Dictionary Cache hit ratio muestra la frecuencia en una aplicación encuentra la información del diccionario datos que necesita en memoria en vez de tener que leerlo de disco. Esta información de it-ratio se encuentra la vista dinámica V$ROWCACHE.

SQL> SELECT   (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio" 
            FROM v$rowcache; 
 Data Dictionary Hit Ratio 
------------------------- 
.989400103