Optimización SGA y PGA

SGA

Los trabajos de optimización de un gestor de base de datos Oracle pasan siempre por el estudio de las tablas de estadísticas propias del motor. Estas tablas dan información relativa a lo sucedido desde el último arranque, es decir, con cada reinicio de la instancia de base de datos se vacían.
Oracle utiliza dos estructuras de memoria principales para su trabajo: el Área Global del Sistema, SGA (System Global Area o también Shared Global Area) y el Área Global de Programa, (Program Global Area), PGA.
En la SGA Oracle guarda información sobre su estado de manera compartida. Está disponible para todos los procesos, por eso se dice que está compartida.
La SGA consta del

•     Database Buffer Cache
•     Redo Log Buffer
•     Shared Pool
•     Large Pool
•     Java Pool
•     Streams Pool

Empezando por la SGA los valores a estudiar son

La cantidad de memoria libre en la Shared Pool

SELECT * 
FROM v$sgastat 
WHERE name='free memory';

El porcentaje de aciertos de caché, según esta fórmula:

Cache Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))

SELECT name, value 
FROM v$sysstat 
WHERE name IN ('db block gets', 'consistent gets', 'physical reads')

También con

SELECT physical_reads, db_block_gets, consistent_gets, name 
FROM v$buffer_pool_statistics

Con un valor inferior a 90% es recomendable aumentar la database buffer cache.

La relación entre paradas para acceder al redo log y accesos en caché.

SELECT name, value
  FROM v$sysstat
 WHERE name = 'redo entries'
    OR name = 'redo log space requests'

Con una relación superior a 1:5.000 es necesario ampliar el tamaño del buffer de redo.

    Los aciertos de Library Cache

SELECT SUM (pins - reloads) / SUM (pins) 
FROM v$librarycache

Con un valor menor a 95% se recomienda aumentar el tamaño de la Shared Pool.

    Los aciertos en el diccionario de datos

SELECT SUM(gets) , SUM( getmisses), (SUM( getmisses)/SUM(gets))*100 
FROM v$rowcache

Con un porcentaje de fallos (getmisses) frente al de aciertos (gets) mayor de 10-15% es necesario aumentar la Shared Pool y estudiar las consultas lanzadas para asegurarse de que usan parámetros.

PGA

La PGA, Program Global Area, es la zona de memoria de cada proceso Oracle. No está compartida y contiene datos e información de control de un único proceso.
La PGA consta del

•     Área de ordenación
•     Resto de memoria privada del proceso

Siguiendo por la PGA los valores a estudiar son:

La proporción de ordenaciones que se realizan en memoria frente a las realizadas en disco.

SELECT a.value "Ordenaciones Disco",
       b.value "Ordenaciones Memoria",
       ROUND((100 * b.value) /
             DECODE((a.value + b.value), 0, 1, (a.value + b.value)),
             2) "Porcent"
  FROM v$sysstat a, v$sysstat b
 WHERE a.name = 'sorts (disk)'
 and b.name = 'sorts (memory)';


Si el porcentaje de ordenaciones realizadas en memoria es menor del 98% el área de ordenación está mal dimensionada.
Es necesario modificar ese espacio mediante el parámetro SORT_AREA_SIZE.
A partir de este momento se centra el estudio en las consultas ejecutadas.

Table Scan. Las consultas pueden desencadenar operaciones de table scan, hay que controlar que las operaciones de este tipo no deben ser full table scan, las más pesadas.
En cualquier caso, las operaciones de este último tipo sobre tablas pequeñas, de diccionario, no generan carga.

SELECT name,value FROM v$sysstat WHERE name LIKE '%table scans%'

Bloqueos. Una de las situaciones que más puede afectar al rendimiento de la base de datos es la existencia de bloqueos en el servidor.

SELECT s.sid, s.serial#, p.spid FROM v$session s, v$process p WHERE s.paddr = p.addr AND s.sid in (SELECT session_id FROM v$locked_object)

Anulaciones. El mismo caso que el anterior.

SELECT r.name "RB Segment", NVL(s.username, 'no transaction') "Username", s.osuser "OS User", s.terminal "Terminal" FROM v$lock l, v$session s, v$rollname r WHERE l.sid=s.sid(+) AND TRUNC(l.id1/65536) = r.usn AND l.type='TX' AND l.lmode=6 ORDER BY r.name

Una vez comprobada toda la configuración y la situación actual de la instancia, queda observar el uso que se está haciendo del motor: las consultas.
Las consultas lanzadas aparecen en la vista V$SQL. Al igual que el resto se vacía con el arranque de la instancia.
Estas consultas se pueden ordenar por cualquiera de sus campos, pero los más útiles son:
EXECUTIONS (número de ejecuciones), CPU_TIME (tiempo de CPU) y APPLICATION_WAIT_TIME (tiempo de espera).