Oracle Automatic Shared Memory Manager
Tuning sempre foi um calcanhar de Aquiles de um DBA principalmente se tratando de banco de dados como o Oracle. Sua arquitetura é bem complexa. Para um DBA é necessário conhecer bem esta arquitetura afim de realizar ajustes precisos que não comprometa ainda mais a performance ou o servidor em que o banco de dados está hospedado. Pensando em ajudar o DBA a tomar decisões, a Oracle introduziu a partir da versão 10G o novo recurso chamado Automatic Shared Memory Manager – ASMM (Gerenciador Automático de Memória Compartilhada). Este recurso faz com que o Oracle realize ajustes de acordo com o “workload” (carga de trabalho) do banco de dados. O ASMM está disponível em todas as edições do banco de dados. Como vantagem não é necessário se preocupar com alocar memória durante operações de tunning. Porém, o Oracle pode demorar para tomar uma decisão além de suas decisões se basearem em métricas padrões e também no Workload do Banco de dados.
Porém vejamos como funciona e como habilitar e desabilitar este recurso.
Quando habilitado, o ASMM considera as duas maiores áreas do banco de dados: a SGA (System Global Area) e PGA (Program Global Area). O Oracle se utiliza do sistema de grão ou seja, tira recursos de uma estrutura de memória para outra. Por exemplo: se o Oracle detectar que o Database Buffer Cache necessita de mais memória ele retira de outra área menos usada por exemplo da Shared Pool. Isso é feito através do processo MMAN (Memory Manager).
Entretanto, esse procedimento pode demorar algum tempo pois, o Oracle necessita que a área em que foi requisitada a retirada da memória libere este recurso e assim seja disponibilizado para a outra estrutura. Na versão 10G o Oracle controla apenas a SGA mas a partir da versão 11G a PGA foi incluída.
As áreas controladas pelo ASMM são:
- Default buffer cache;
- Shared Pool;
- Large Pool;
- Java Pool
- Stream Pool (este disponível a partir da versão 10G R2)
- A partir da versão 11G o Oracle também controla a PGA.
Para que o ASMM esteja habilitado, os seguintes parâmetros de inicialização (spfile ou pfile) devem conter o valor “0”:
db_cache_size
shared_pool_size
large_pool_size
java_pool_size
streams_pool_size
Para a versão 11G o parâmetro pga_aggregate_target deve estar definido como 0.
Na versão 10G é necessário também que os parâmetros sga_max_size , sga_target estejam diferentes de zero e sga_max_size não pode ser menor que sga_target assim como sga_target não pode ser maior que sga_max_size.
Porém, a partir da versão 11G também são necessários definir os parâmetros memory_max_target e memory_target para que PGA e SGA possam ser monitorados pelo ASMM. Neste caso os parâmetros sga_max_size e sga_target não podem ser maiores que memory_max_target.
Para desabilitar o ASMM basta definir um valor para os parâmetros db_cache_size, shared_pool_size,large_pool_size,java_pool_size, streams_pool_size e pga_aggregate_target. Caso apenas um parâmetro apenas receba um valor, o Oracle iniciará a instancia com valores padrões.
No meu exemplo estou utilizando um banco de dados Oracle 11.2.0.4
Abaixo vemos que o banco de dados está com ASMM habilitado:
SQL> show parameter pool_size
NAME TYPE VALUE
------------------------------------ ----------------- ------------------------------
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_size big integer 0
streams_pool_size big integer 0
SQL>
SQL> show parameter sga
NAME TYPE VALUE
------------------------- ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 900M
sga_target big integer 800M
SQL>
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------------ ------------ ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1000M
memory_target big integer 900M
shared_memory_address integer 0
SQL>Agora temos alocado:
SQL> select component , round(current_size/1024/1024,2) size_mb
from v$sga_dynamic_components
where component like '%pool'
OR component ='DEFAULT buffer cache';
COMPONENT SIZE_MB
---------------------------------------- ----------
shared pool 212
large pool 4
java pool 4
streams pool 4
DEFAULT buffer cache 564
SQL>Vamos ver o ASMM funcionando. Para isso, criarei uma tabela e irei inserir alguns dados:
SQL> create table tab_demo as select * from dba_segments;
Table created.
SQL> select count(*) from tab_demo;
COUNT(*)
----------
7053
SQL> insert into tab_demo select * from tab_demo;
7053 rows created.
SQL> insert into tab_demo select * from tab_demo;
14106 rows created.
SQL> insert into tab_demo select * from tab_demo;
28212 rows created.
SQL> commit;
Commit complete.
SQL> analyze table tab_demo compute statistics;
Table analyzed.
SQL> select count(*) from tab_demo;
COUNT(*)
----------
56424Agora vamos reiniciar o banco de dados:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL> startup
ORACLE instance started.
Total System Global Area 939495424 bytes
Fixed Size 2232088 bytes
Variable Size 335544552 bytes
Database Buffers 595591168 bytes
Redo Buffers 6127616 bytes
Database mounted.
Database opened.Vejamos agora o que o Oracle precisou fazer para ajustar as áreas de memória:
SQL> set lines 10000
column component format a20
select component , round(current_size/1024/1024,2) size_mb,
LAST_OPER_TYPE, OPER_COUNT, MIN_SIZE, MAX_SIZE
from v$sga_dynamic_components
where component like '%pool'
OR component ='DEFAULT buffer cache';
COMPONENT SIZE_MB LAST_OPER_TYP OPER_COUNT MIN_SIZE MAX_SIZE
-------------------------------- --------------- -------------------------- ---------------------- ------------------ -----------------
shared pool 212 STATIC 0 222298112 222298112
large pool 4 GROW 1 0 4194304
java pool 4 STATIC 0 4194304 4194304
streams pool 4 GROW 1 0 4194304
DEFAULT buffer cache 564 SHRINK 2 591396864 595591168
SQL>A coluna: oper_count demonstra que houve uma operação ou seja, foi realizada uma realocação de memória e a coluna last_oper mostra qual operação foi realizada.
Assim termino esta breve explicação sobre ASMM.
