ALTO CONSUMO DE SHARED POOL, ORA-04031: unable to allocate 63496 bytes of shared memory

Hace unos días, al iniciar mi jornada laboral como DBA, me reportaron el siguiente error,el cual a mas de uno le debe resultar familiar : ORA-04031: unable to allocate 13840 bytes of shared memory.

Este error se presentó en una BD  Container RAC 12.2.0.1 de arquitectura multitenant,la cual tenia mas de un Pluggale Database.(el tratamiento de este error es el mismo en BDs no container ).

Rápidamente, revisando el alert.log de cada nodo encontramos que el siguiente error se presentaba de manera muy recurrente.

Errors in file /u01/app/oracle/diag/rdbms/cdbeai/CDBxxx/trace/CDBxxx_ora_51418.trc (incident=905203) (PDBNAME=AUDxxx):
ORA-04031: unable to allocate 13840 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(2,0)”,”ges resource dynamic”)
AUDxxx(3):Incident details in: /u01/app/oracle/diag/rdbms/cdbxxx/CDBxxxx/incident/incdir_905203/CDBxxx_ora_51418_i905203.trc
AUDxxx(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2018-03-15T09:22:29.074949-05:00

high contention shared pool_2

El mensaje de error es bastante claro,la base de datos requiere mayor memoria en el shared pool de la que hay disponible.

Verificando, la BD tiene 25GB de SGA en total

SQL> show parameter sga

NAME                                          TYPE         VALUE
————————————       ————- ——————————
allow_group_access_to_sga  boolean    FALSE
lock_sga                                    boolean    FALSE
pre_page_sga                           boolean    TRUE
sga_max_size                           biginteger 25G
sga_min_size                            big integer 0
sga_target                                big integer     25G
unified_audit_sga_queue_size integer 1048576

Investigando a mayor detalle desde cloud control,nos encontramos que el 98% de la memoria asignada al SGA es consumida por el shared pool.(Ops eso no suena nada bien!!).

shared_pool_cloudcontrol1

En la distribución de memoria entre buffers del SGA, se puede apreciar que de 25GB que tiene como total el SGA,22.6GB es consumido por el shared pool.

shared_pool_cloudcontrol2

La distribución de memoria entre buffers del SGA tambien puede visualizarse usando el siguiente query:

SQL> SELECT component, current_size/1024/1024/1024 gbytes
FROM v$sga_dynamic_components
ORDER BY 2 DESC
FETCH FIRST 5 ROWS ONLY;

COMPONENT                                                   GBYTES
—————————————————————- ———-
shared pool                                                       22.6875
large pool                                                            1.1875
Shared IO Pool                                                      .5
DEFAULT buffer cache                                     .3125
streams pool                                                        .125

Revisando a detalle el consumo dentro del shared pool, encontramos que había indicios de memoria retenida en el shared pool por “ges resources“:

SQL> SELECT pool, name, bytes/1024/1024/1024 gbytes
FROM v$sgastat
WHERE pool=’shared pool’
ORDER BY BYTES DESC
FETCH FIRST 5 ROWS ONLY;

POOL                     NAME                          GBYTES
————–         ————————–           ——————-
shared pool       ges enqueues                 5.82885164
shared pool       SQLA                               4.86808275
shared pool       ges resource dynamic 2.52088516
shared pool       free memory                 2.33909176
shared pool      gc index split transaction .86762473

Ahora pasaremos a revisar como ha ido cambiando la asignación de memoria entre buffers dentro del SGA ,desde el ultimo reinicio de la instancia de BD.Para lo cual usaremos el siguiente query bastante útil, obtenido del blog de Andrea Salzano

col FINAL_SIZE for 999,999,999,999,999
 set pages 99
 set lines 210
 col component for a20

with gv_sga_resize_ops as (
 select inst_id instance_number , to_char(START_TIME,'yyyymmdd') START_DAY, START_TIME, COMPONENT, OPER_TYPE, OPER_MODE, final_size
 from GV$SGA_RESIZE_OPS
 where COMPONENT in ('DEFAULT buffer cache','shared pool')
 )
 select instance_number inst_id, START_TIME last_start_time, COMPONENT, OPER_TYPE last_OPER_TYPE, OPER_MODE, final_size, oper_cnt from (
 select instance_number, START_DAY, COMPONENT, OPER_TYPE, OPER_MODE, final_size,
 row_number() over (partition by instance_number, START_DAY, COMPONENT, OPER_TYPE order by instance_number, START_TIME desc) rn,
 count(*)over (partition by instance_number, START_DAY, COMPONENT, OPER_TYPE) oper_cnt, START_TIME
 from gv_sga_resize_ops o
 ) where rn=1
 and instance_number=2
 order by 1,3,2;

Claramente, se puede apreciar, en la columna FINAL_SIZE,que desde el ultimo reinicio de la instancia de BD, el 22/DEC/2017 el Data Buffer Cache tenia una tendencia de hacerse mas pequeño(SHRINK),mientras que a la par el Shared Pool fue creciendo(GROW), hasta ocupar casi todo el SGA.

shARed_pool_DETAIL.jpg

Indagando un poco en My Oracle Support, así como también otras páginas de colegas https://dbamarco.wordpress.com/2015/10/29/shared-pool-memory-leak-in-12c-rac/

encontré la siguiente nota técnica, la cual hace referencia a un bug 21373473, debido a bloqueos de tipo DX y BB los cuales están siendo capturados en el shared pool , pero no están siendo limpiados.

ORA-04031 Errors Occurring with High “ges resource dynamic” & “ges enqueues” Memory Usage In The Shared Pool (Doc ID 2063751.1)

Download and apply the one-off patch number 21373473 (Patch:21373473) for your platform and version combination.

Please note that if using 12.1.0.2, then you should also apply the related patch number 21260431 (Patch:21260431) which also impacts the shared pool memory allocations identified here.

This issue can also be worked around by setting _GES_DIRECT_FREE_RES_TYPE=”CTARAHDXBB” in the instances.

+ otra nota relacionada

NOTE:1951758.1 – ORA-4031 due to Huge “ges resource dynamic” and “ges enqueues” and Instance Terminates: ORA-29770: ‘SGA: allocation forcing component growth’.

Finalmente decidí aplicar el workaround(Doc ID 2063751.1), de setear el parámetro “_ges_direct_free_res_type”, en ambas instancias del RAC:

alter system set “_ges_direct_free_res_type”="CTARAHDXBB" scope=spfile sid=’*’;

…Finalmente

Una semana después del cambio observamos que el comportamiento del shared pool volvió a la normalidad y se ha mantenido estable.

shred_pool_wa

sharedpool_wa2

 

 

PERFORMANCE TUNING : ACTIVE SESSION HISTORY -ASH -PARTE I

INTRODUCCIÓN:

Los problemas de Performance en una Base de datos Productiva,son quizá,los tipo de problema mas críticos, complejos y personalmente considero que son los mas emocionantes de resolver!!!.

¿Cuantas veces nos ha pasado en nuestro día a día como DBAs, que nos han pedido analizar e indicar a detalle que ocasionó un cuello de botella en nuestras BDs en un punto en el pasado?,probablemente de hace unas horas,días o incluso semanas atrás.

Active Session History(ASH), es una excelente herramienta para analizar problemas de performance que suceden en tiempo real,así como también en un punto en el pasado.

ASH brinda la mayoría de información disponible en trace files pero sin necesidad de generar trace files,esta siempre esta disponible.Usar ASH es extremadamente simple, por que no tienes que generar eventos e ir a la ruta en el servidor a revisar las trazas generadas, basta con consultar la vista v$active_session_history.

Cabe resaltar que Active Session History, es un feature disponible desde Oracle 10g release 1 y su uso requiere licencia Diagnostic Pack.

ash-architecture-and-advanced-usage-rmoug2014-7-6381

Cada segundo, Active Session History sensa la base de datos y recolecta importante información de las sesiones activas(sesiones inactivas no son capturadas) y pobla el ASH Buffer,el cual se puede consultar desde la vista v$active_session_history.

  • SESSION:session_id,session_serial#,user_id(puede ser usado contra la dba_users para obtener el username),session_type,session_state,qc_session_id,qc_instance_id
  • WAIT:event,event_id,event#,seq#,p1,p2,p3
  • SQL: sql_id,sql_child_number,sql_plan_hash_value,sql_opcode
  • OBJECT:current_obj#,current_file#,current_block#
  • APPLICATION: Program,Module,action,client_id,service_hash

ash

La información disponible en la v$active_session_history es poblada en base a la información disponible de las vistas v$session y v$session_wait y permite obtener información importante propia de la sesion, wait, sentencia SQL,objeto,aplicacion:

image002

RETENCIÓN:

La retención de active session history, es dependiente de la retención del AWR.

Oracle escribe la información de memoria a disco, desde v$active_session_history hacia la tabla wrh$active_session history, a la cual podemos consultar a través de la vista dba_hist_active_sess_history. Esta acción la realiza cada hora antes de cada snapshot del AWR o cuando el Ash buffer este al 2/3 de su capacidad en el SGA, cabe resaltar que no toda la información baja a disco, solo 1 de cada 10 entradas.

Calcular Retención en dias de ASH:

 select sysdate - a.sample_time ash,
 sysdate - s.begin_interval_time snap,
 c.RETENTION
 from sys.wrm$_wr_control c,
 (
 select db.dbid,
 min(w.sample_time) sample_time
 from sys.v_$database db,
 sys.Wrh$_active_session_history w
 where w.dbid = db.dbid group by db.dbid
 ) a,
 (
 select db.dbid,
 min(r.begin_interval_time) begin_interval_time
 from sys.v_$database db,
 sys.wrm$_snapshot r
 where r.dbid = db.dbid
 group by db.dbid
 ) s
 where a.dbid = s.dbid
 and c.dbid = a.dbid;

retention_ash

SELECT min(sample_time) min_sample_time, max(sample_time) max_sample_time FROM V$ACTIVE_SESSION_HISTORY;

PARÁMETROS:

_ash_size
_ash_enable
_ash_sampling_interval
_ash_disk_write_enable
_ash_disk_filter_ratio

referencias: