Entendendo o gerenciamento de memória do Oracle
Olá pessoal, hoje irei abordar o tema do gerenciamento de memória do Oracle usando o método de dedicated server. Como muitos sabem o Oracle possui duas áreas em sua memória, PGA (Program Global Area) e SGA (System Global Area) além dos backgrounds process.
A arquitetura de um single-instance pode ser resumida em:
1° Usuário interage com um user process
2° Um user process interage com um server process.
3° Um server process interage com a instância.
4° A instância interage com o database.
É impossível que um processo no client-side tenha contato com o database. Todos os acessos devem ser intermediados por um processo no server-sid.
O client-server é dividido entre user process (quem gera o SQL) e o server process (quem o executa). As estruturas da SGA que são obrigatórias são database buffer cache, shared pool e log buffer, a large pool, java pool e strems são opcionais.
SGA (System Global Area) – é compartilhada por todos os server e background process.
PGA (Program Global Area) – Não é compartilhada e possui dados de controle exclusivo de um Oracle process. A PGA é criada quando um processo Oracle é iniciado. Uma PGA existe para cada server process e background process. Um conjunto de PGAs é chamado de total instance PGA ou instance PGA. O valor setado para a PGA (parâmetro) é setado para a instancia e não para cada PGA individual.
UGA (User Global Area) – é a memória associada a uma sessão de usuário.
Software Code Areas – São porções de memória usadas para armazenar códigos que estão sendo executados ou que podem ser executados.
Gerenciamento de memória
No Oracle temos três tipos de gerenciamento:
Automatic memory management (AMM): O próprio Oracle faz o tuning automaticamente do tamanho da memória redistribuindo memória conforme necessário entre SGA e instancia PGA.
Automatic shared memory management (ASMM): É parcialmente automática. Você seta um valor para SGA e tem a opção de configurar um valor certo para a PGA se auto gerenciar ou gerenciar as áreas da PGA individualmente.
Manual memory management (MMM): É o gerenciamento estático. O DBA seta o valor de cada pool da SGA e PGA individualmente.
Quando você cria um database via DBCA ele usa AMM por default.
UGA (User Global Area) – É a session memory que é a memória alocada para as variaveis da session, como informações de logon e outras informações necessárias para uma database session. Essencialmente a UGA armazena o estado da sessão.
Se uma sessão lê uma package para dentro da memória a então a UGA vai armazenar o estado da package que nada mais é que o conjunto de variáveis armazenados naquela package naquele momento específico. O estado da package muda quando um subprogram da package muda as variáveis. Por default as variáveis da package são únicos e persistem durante toda a vida da sessão. O OLAP POOL armazena páginas de dados que são equivalentes aos blocos de dados. Esse pool é iniciado no momento que a sessão é iniciada e é finalizado quando a sessão termina. Uma OLAP session é aberta automaticamente quando um usuário usa um objeto de um cubo.
A UGA reside dentro da PGA quando o banco está usando DEDICATED SERVER CONNECTION. Por conta da PGA ser específica para cada single process a UGA é armazenada na SGA quando o banco está em SHARED SERVER fazendo com que qualquer process possa acessa-la.
PGA (Program Global Area) – A PGA é uma memória específica para um process ou thread que não é compartilhado com outros process ou threads. Ela NUNCA é alocada na SGA.
Componentes da PGA
Private SQL Area: Contém informações sobre comandos SQL já analisados (parsed) e outras informações espcíficas da sessão para processamento. Quando um server process executa um SQL ou PL/SQL o próprosito dessa área é armazenar os valores das BIND VARIABLES, informações do estado de execução da query e área de execution work. Não confunda a Private SQL area que fica na UGA com a Shared SQL Area que armazena executions plans na SGA. Multiplas Private SQL Areas na mesma ou diferentes sessões podem usar um mesmo execution plan na SGA. Por exemplo 20 execuções de SELECT * FROM EMPLOYEES em uma sessão é 10 execuções da mesma query em outra sessão podem usar o mesmo execution plan. A Private SQL Area para cada execução não são compartilhadas e podem conter valores e dados diferentes.
O CURSOR é o nome ou identificador que é dado para uma Private SQL Area específica. Você pode pensar que um cursor é como um ponteiro no lado do client assim como um estado no lado server.
A Private SQL Area é dividida em duas partes:
Run Time Area: Essa área contém informações do estado da execução da query. Por exemplo, a run-time area rastreia o número de linhas retornadas até o momento em um full table scan. O Oracle cria a Run Time Area como o primeiro processo de execução de uma requisição. Para comandos DML a Run Time Area é liberada quando o comando SQL é fechado.
Persistent Area: Essa possui os valores das bind variables. A persistent area é liberada somente quando o cursor é fechado. O client process é responsável por gerenciar as Private SQL Areas. A alocação e desalocação de Private SQL Areas depende da aplicação. Entretanto, o número de Private SQL areas que um client process pode alocar é limitado pelo parâmetro open_cursors.
SQL Work Areas: A Work Area é uma área privada alocada pela PGA usada para operações intensivas na memória. Por exemplo, uma operação de sort usa a sort area para ordernar um conjunto de linhas. Similar, uma operação de hash join usa a hash area para construir uma hash table e um bitmap merge usa a bitmap merge area para fundir os dados através de leituras de vários bitmap indexes.
Nesse exemplo a Run Time Area rastreia o progesso do full table scan. A sessão faz um hash join na hash join area para combinar as linhas das duas tabelas e o ORDER BY ocorre na sort area. Se a quantidade de dados processados pelos operadores não couber dentro de uma work area então o Oracle divide os dados de entrada em pedaços pequenos, nesse caso o database processa alguns pedaços dos dados na memória enquanto escreve o resto para o disco temporariamente para processor mais tarde (depois de processor o que já estiver na memória e for liberado). Quando work area é muito pequena o response time aumenta porque parte dos dados de entrada devem ser armazenados em disco. Se a work é extremamente pequena comparada com o tamanho dos dados o database precisa fazer vários multiple pass nos blocos de dados aumentando drasticamente o response time.
|
Área de Memória |
Dedicated Server |
Shared Server |
|
Natureza da session memory |
Private |
Shared |
|
Persistent Area |
PGA |
SGA |
|
Run-time Area (DML/DDL) |
PGA |
PGA |
Agora que sabemos a teoria, vamos colocar a mão na massa!
AMM
[oracle@manchester u01 lab158]$sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 16 14:37:41 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1107296256 bytes
Fixed Size 2923488 bytes
Variable Size 788530208 bytes
Database Buffers 301989888 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter memory_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 1056M
memory_target big integer 1056M
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1056M
sga_target big integer 0A minha instancia está usando o AMM (Automatic memory management) pois temos os parâmetros:
MEMORY_TARGET 1056M
MEMORY_MAX_SIZE 1056M
SGA_MAX_SIZE 1056M
SGA_TARGET 0Nisso ela sempre irá usar o AMM podendo variar o tamanho entre SGA e PGA conforme sua necessidade mais os seus componentes.
ASMM
SQL> alter system set memory_target=0 scope=spfile;
System altered.
SQL> alter system set memory_max_target=0 scope=spfile;
System altered.
SQL> alter system set sga_target=1056m scope=spfile;
System altered.
SQL> create pfile from spfile;
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1107296256 bytes
Fixed Size 2923488 bytes
Variable Size 419431456 bytes
Database Buffers 671088640 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 0
memory_target big integer 0
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1056M
sga_target big integer 1056M
MEMORY_TARGET 0
MEMORY_MAX_SIZE 0
SGA_MAX_SIZE 1000m
SGA_TARGET 800mIrá usar o ASMM onde não irá variar o valor da SGA e da PGA, mas os componentes da SGA sim, ou seja, os buffers dentro da SGA são redimensionados conforme a necessidade do Oracle, mas se a SGA como um todo precisar de mais memória, ela não irá pegar memória da PGA, o valor total da SGA será sempre o mesmo.
MMM
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 2923096 bytes
Variable Size 192939432 bytes
Database Buffers 50331648 bytes
Redo Buffers 5464064 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 240M
sga_target big integer 0
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 0
memory_target big integer 0Após eu definir o valor da sga_target como 0 ele iniciou a minha instancia com apenas 256M, por que será que isso aconteceu? Eu continuo usando meu SPFILE normalmente.
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.1.0
.2/dbhome_1/dbs/spfilelab158.o
ra
SQL> alter system set sga_max_size=1056m scope=spfile;
System altered.
SQL> alter system set db_cache_size=200m scope=spfile;
System altered.
SQL> alter system set log_buffer=100m scope=spfile;
System altered.
SQL> alter system set shared_pool_size=200m scope=spfile;
System altered.
SQL> alter system set pga_aggregate_target=500m scope=spfile;
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1107296256 bytes
Fixed Size 2923488 bytes
Variable Size 771752992 bytes
Database Buffers 218103808 bytes
Redo Buffers 114515968 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1056M
sga_target big integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 500M
SQL> show parameter cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 192M
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 208MAgora sim, os valores estão conforme setamos no spfile. Pelo fato de não termos indicado nenhum valor para a SGA inicialmente, ele subiu com o valor mínimo dele de 256M.
No caso os parâmetros estão na seguinte configuração:
MEMORY_TARGET 0
MEMORY_MAX_SIZE 0
SGA_MAX_SIZE 1056m
SGA_TARGET 0Desabilito tanto o AMM quanto o ASMM e passo a administrar eu mesmo a distribuição da SGA para a PGA e os componentes da SGA, via MANUAL MEMORY MANAGEMENT (MMM).
Recomendações
Se você possui um ambiente misto (OLTP no horário comercial e OLAP durante a madrugada) use o AMM, pois a instancia vai alternando entre a PGA e SGA conforme sua necessidade.
Se você possui um ambiente que é exclusivo OLTP ou OLAP use o ASMM, assim a SGA e PGA são fixas variando apenas os pools internos de cada uma delas evitando o mau redimensionamento durante picos de utilização de uma das áreas.
Se você possui um ambiente que é muito sensível a qualquer alteração nos pools de memória tanto da PGA como da SGA, use o MMM. Vale ressaltar que utilizar o MMM não é uma boa prática, é sempre bom deixar o próprio Oracle decidir como será feita o gerenciamento da memória, afinal de contas você não gostaria que outra pessoa definisse a quantidade de comida que vai no seu prato quando é você que está faminto, não é mesmo?! De fato, utilizar o MMM requer uma atenção muito maior do DBA para o gerenciamento de memória e errar na definição dos tamanhos dos pools pode ser um tiro no próprio pé.
Espero ter ajudado, até logo!
