Antes de recorrer ao alert log, é interessante verificar como estão os parâmetros relacionados a Hugepages no banco de dados, recomendado validar em todas as instâncias quando o ambiente for RAC.

col inst_id format 99
col name format a25
col display_value format a20
select name, inst_id, display_value
from gv$parameter
where name in ('sga_max_size','use_large_pages','memory_max_target')
order by 1,2;

NAME                      INST_ID DISPLAY_VALUE
------------------------- ------- --------------------
memory_max_target               1 0
memory_max_target               2 0
sga_max_size                    1 60G
sga_max_size                    2 60G
use_large_pages                 1 ONLY
use_large_pages                 2 ONLY

6 rows selected.

Se o parâmetro use_large_pages está configurado como ONLY então sabemos que é garantido estar usando Hugepages. Por outro lado, se estiver como FALSE, então é garantia de que não está usando Hugepages.

Se o parâmetro estiver como TRUE, então a utilização de Hugepages é algo que ocorre sempre que possível, mas não é garantia. Neste caso, para ter certeza que a instância alocou a SGA em Hugepages no último startup, podemos consultar no alert log.

Nota: Isso se aplica somente a Standard HugePages (recomendado) e não a Transparent HugePages (não recomendado), conforme doc:

“If there is insufficient HugePages when an instance starts and the initialization parameter use_large_pages is set to only, then the database fails to start and an alert log message provides the necessary information on Hugepages.”

“Transparent HugePages can cause memory allocation delays at runtime. To avoid performance issues, Oracle recommends that you disable Transparent HugePages on all Oracle Database servers. Oracle recommends that you instead use standard HugePages for enhanced performance”

Exemplo no 11gR2

Navegue até o diretório do alert log e execute este comando:

grep -B2 -A10 "Large Pages Information" alert_HML1.log

O resultado será similar a este:

Sat Mar 13 15:26:37 2021
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Parameter use_large_pages = ONLY
Per process system memlock (soft) limit = UNLIMITED

Total Shared Global Region in Large Pages = 60 GB (100%)

Large Pages used by this instance: 30721 (60 GB)
Large Pages unused system wide = 25118 (49 GB)
Large Pages configured system wide = 90577 (177 GB)
Large Page size = 2048 KB
********************************************************************
--
Wed Apr 07 14:56:51 2021
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Parameter use_large_pages = ONLY
Per process system memlock (soft) limit = UNLIMITED

Total Shared Global Region in Large Pages = 60 GB (100%)

Large Pages used by this instance: 30721 (60 GB)
Large Pages unused system wide = 29 (58 MB)
Large Pages configured system wide = 90577 (177 GB)
Large Page size = 2048 KB
********************************************************************
--
Sat Apr 24 07:53:27 2021
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Parameter use_large_pages = ONLY
Per process system memlock (soft) limit = UNLIMITED

Total Shared Global Region in Large Pages = 60 GB (100%)

Large Pages used by this instance: 30721 (60 GB)
Large Pages unused system wide = 88195 (172 GB)
Large Pages configured system wide = 120577 (236 GB)
Large Page size = 2048 KB
********************************************************************

Exemplo no 12cR1

Navegue até o diretório do alert log e execute este comando:

O resultado será similar a este:

grep -A10 "Available system pagesizes" alert_HML2.log

 Available system pagesizes:
  4K, 2048K
Fri May 14 09:06:02 2021
 Supported system pagesize(s):
Fri May 14 09:06:02 2021
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
Fri May 14 09:06:02 2021
     2048K             5474            5122            5122        NONE
Fri May 14 09:06:02 2021
 Reason for not supporting certain system pagesizes:
Fri May 14 09:06:02 2021
--
 Available system pagesizes:
  4K, 2048K
Fri May 14 09:20:19 2021
 Supported system pagesize(s):
Fri May 14 09:20:19 2021
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
Fri May 14 09:20:19 2021
     2048K             5474            5122            5122        NONE
Fri May 14 09:20:19 2021
 Reason for not supporting certain system pagesizes:
Fri May 14 09:20:19 2021
--
 Available system pagesizes:
  4K, 2048K
Fri May 14 11:02:41 2021
 Supported system pagesize(s):
Fri May 14 11:02:41 2021
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
Fri May 14 11:02:41 2021
     2048K             5474            5122            5122        NONE
Fri May 14 11:02:41 2021
 Reason for not supporting certain system pagesizes:

Exemplos de Cenários

Atualizado em 09/06/2021.

A) Quando o servidor não tem Hugepages suficiente e o parâmetro use_large_pages é configurado com valor TRUE.

Neste exemplo, o tamanho da SGA era 400 GB, mas o servidor só tinha 194.602 páginas de Hugepages. A instância alocou 95% da SGA em Hugepages, o restante ficou fora.

Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = UNLIMITED

Total Shared Global Region in Large Pages = 380 GB (95%)

Large Pages used by this instance: 194561 (380 GB)
Large Pages unused system wide = 41 (82 MB)
Large Pages configured system wide = 194602 (380 GB)
Large Page size = 2048 KB

Recomendação: Aumentar a quantidade de páginas de Hugepages no sistema operacional e alterar o parâmetro use_large_pages para ONLY. Se não for possível aumentar a quantidade de Hugepages (talvez já esteja no limite seguro), pode ser preferível reduzir o tamanho da SGA para caber totalmente na Hugepages e alterar o parâmetro use_large_pages para ONLY. Esse modo de configuração deve oferecer performance mais previsível.

B) Quando o servidor não tem Hugepages suficiente e o parâmetro use_large_pages é configurado com valor ONLY.

Erro ao tentar iniciar a instância:

ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3651
Additional information: -1725824605

Erro reportado no alert log:

Wed Jun 09 09:09:42 2021
ERROR: Failed to get available system pages to allocate memory
Wed Jun 09 09:09:42 2021
**********************************************************************
Wed Jun 09 09:09:42 2021
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

Wed Jun 09 09:09:42 2021
 Per process system memlock (soft) limit = UNLIMITED
Wed Jun 09 09:09:42 2021
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 15G
Wed Jun 09 09:09:42 2021
 Available system pagesizes:
  4K, 2048K
Wed Jun 09 09:09:42 2021
 Supported system pagesize(s):
Wed Jun 09 09:09:42 2021
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
Wed Jun 09 09:09:42 2021
     2048K             7258            7682            7250        NONE
Wed Jun 09 09:09:42 2021
 Reason for not supporting certain system pagesizes:
Wed Jun 09 09:09:42 2021
  4K - Large pagesizes only
Wed Jun 09 09:09:42 2021
RECOMMENDATION:
Wed Jun 09 09:09:42 2021
 1. Configure system with expected number of pages for every
 supported system pagesize prior to the next instance restart operation.
Wed Jun 09 09:09:42 2021
**********************************************************************

Recomendação: Nestes cenários onde o parâmetro use_large_pages já está configurado com valor ONLY e a diferença entre EXPECTED_PAGES vs AVAILABLE_PAGES é relativamente baixa, seria preferível reduzir o tamnho da SGA para caber na Hugepages já configurada.