GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

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 0

A 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 0

Nisso 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 800m

Irá 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 0
Apó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 208M

Agora 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 0

Desabilito 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!

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *