Este artigo tem como objetivo medir basicamente a performance da memória PGA e realizar um “Tuning” básico nesta para ganho de performance.
A PGA é o buffer de memória que contém dados e informações de controle de uma sessão de um usuário.
A PGA é criada e alocada quando um novo processo é inicializado no servidor quando o processo estiver de modo dedicado.
As suas informações dependem da configuração do Banco de Dados Oracle.
Assim, existe uma área de memória PGA para cada usuário que está executando seus trabalhos no Oracle.
Dentro da PGA existem as seguintes estruturas: uma contendo um espaço para armazenar as variáveis e matrizes, outra contendo dados sobre a sessão do usuário e uma terceira com as informações dos cursores usados. A PGA não é compartilhada entre os usuários; ela é única para cada sessão.
Para iniciarmos devemos verificar o parâmetro abaixo.
SQL> show parameter workarea_size_policy NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ workarea_size_policy string AUTO SQL>
O parâmetro “workarea_size_policy” conforme acima deve estar como AUTO para o controlar as workareas automaticamente nas versões do Oracle 10g e 11g.
Sobre “workarea_size_policy” veja WORKAREA_SIZE_POLICY
Com a instância recem iniciada vamos verificar as workareas para a memória PGA e o “Hit Ratio” da mesma.
OBS: (“Acompanha a Performance do Teste da memória PGA também Imagens abaixo”).
SQL> PROMPT
PROMPT --Memoria na Execucao de SQL - Estatisticas
column "col1" format a35 heading "Estatistica"
column "col2" format 999,999,999,990 heading "Valor"
select
name "col1",
value "col2"
from
v$sysstat
where
name LIKE '%workarea%'
;
Estatistica Valor
----------------------------------- ----------------
workarea memory allocated 0
workarea executions - optimal 4,842
workarea executions - onepass 0
workarea executions - multipass 0
SQL> col "PGA Hit Ratio" format a13;
SQL> select to_char(round(value,4),'999.99') ||'%' "PGA Hit Ratio"
from sys.v_$pgastat
where name = 'cache hit percentage'; 2 3
PGA Hit Ratio
-------------
100.00%
SQL>
Abaixo está a memória PGA com 200M (Megabytes) e o parâmetro oculto do Oracle (_pga_max_size) de 100M.
OBS: Sobre parâmetros ocultos veja o artigo sobre Parâmetros Ocultos
SQL> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _pga_max_size big integer 100M pga_aggregate_target big integer 200M SQL>
Vamos ajustar nossa memória para 10M em ambos os parâmetros.
SQL> alter system set "_pga_max_size"='10M' scope=memory; System altered. SQL> alter system set pga_aggregate_target='10M' scope=memory; System altered. SQL> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _pga_max_size big integer 10M pga_aggregate_target big integer 10M SQL>
Neste momento abri 5 terminais e conectei no Banco de Dados Oracle executando os seguintes comandos:
1 – select * from dba_segments, dba_objects, dba_indexes, dba_tables order by 1 desc, 2 asc, 3 desc, 4 asc;
2 – select * from dba_segments order by 3 asc, 5 desc;
O comando 1 foi executado em 2 terminais e o comando 2 foi executado em 3 terminais.
Vamos acompanhar a performance da memória Oracle.
SQL> PROMPT
PROMPT --Memoria na Execucao de SQL - Estatisticas
column "col1" format a35 heading "Estatistica"
column "col2" format 999,999,999,990 heading "Valor"
select
name "col1",
value "col2"
from
v$sysstat
where
name LIKE '%workarea%'
;
Estatistica Valor
----------------------------------- ----------------
workarea memory allocated 8,146
workarea executions - optimal 3,014
workarea executions - onepass 0
workarea executions - multipass 1
SQL> col "PGA Hit Ratio" format a13;
SQL> select to_char(round(value,4),'999.99') ||'%' "PGA Hit Ratio"
from sys.v_$pgastat
where name = 'cache hit percentage'; 2 3
PGA Hit Ratio
-------------
88.04%
SQL> /
PGA Hit Ratio
-------------
76.21%
SQL> PROMPT
PROMPT --Memoria na Execucao de SQL - Estatisticas
column "col1" format a35 heading "Estatistica"
column "col2" format 999,999,999,990 heading "Valor"
select
name "col1",
value "col2"
from
v$sysstat
where
name LIKE '%workarea%'
;
Estatistica Valor
----------------------------------- ----------------
workarea memory allocated 653
workarea executions - optimal 4,378
workarea executions - onepass 3
workarea executions - multipass 5
SQL>
Podemos perceber acompanhando a performance da memória PGA que a mesma não é ideal para o processamento dos comandos SQL acima, devido ao “Hit Ratio” baixo e a “workarea executions – multipass” estar maior que 0.
Vamos verificar o advisor da PGA.
SQL> set line 155
SQL> SELECT PGA_TARGET_FACTOR, PGA_TARGET_FOR_ESTIMATE,
ESTD_PGA_CACHE_HIT_PERCENTAGE,
ESTD_EXTRA_BYTES_RW, ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE ORDER BY 1;
PGA_TARGET_FACTOR PGA_TARGET_FOR_ESTIMATE ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_EXTRA_BYTES_RW ESTD_OVERALLOC_COUNT
----------------- ----------------------- ----------------------------- ------------------- --------------------
1 10485760 75 50014208 11
1.2 12582912 75 50014208 11
1.4 14680064 75 50014208 11
1.6 16777216 75 50014208 11
1.8 18874368 75 50014208 11
2 20971520 75 50014208 11
3 31457280 75 50014208 11
4 41943040 75 50014208 11
6 62914560 75 50014208 2
8 83886080 86 23564288 0
10 rows selected.
SQL>
Podemos perceber acima pelo advisor da memória PGA que mesmo com 80M não seria o suficiente para ter uma boa performance na memória e o recomendável pela Oracle é que o mesmo esteja acima de 95%.
Vamos reiniciar o banco de dados e fazer novamente os testes porém com os valores dos parâmetros de memória PGA com 200M (Megabytes) e o parâmetro oculto do Oracle (_pga_max_size) de 100M.
SQL> startup force ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 1277824 bytes Variable Size 541065344 bytes Database Buffers 524288000 bytes Redo Buffers 7110656 bytes Database mounted. Database opened. SQL> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _pga_max_size big integer 100M pga_aggregate_target big integer 200M SQL> col "PGA Hit Ratio" format a13; select to_char(round(value,4),'999.99') ||'%' "PGA Hit Ratio" from sys.v_$pgastat where name = 'cache hit percentage';SQL> 2 3 PGA Hit Ratio ------------- 100.00% SQL>
Neste momento com os 5 terminais, conectei novamente no Banco de Dados Oracle executando os seguintes comandos:
1 – select * from dba_segments, dba_objects, dba_indexes, dba_tables order by 1 desc, 2 asc, 3 desc, 4 asc;
2 – select * from dba_segments order by 3 asc, 5 desc;
O comando 1 foi executado em 2 terminais e o comando 2 foi executado em 3 terminais.
Vamos acompanhar a performance da memória PGA de 200M.
SQL> PROMPT
SQL> PROMPT --Memoria na Execucao de SQL - Estatisticas
SQL>
SQL> column "col1" format a35 heading "Estatistica"
SQL> column "col2" format 999,999,999,990 heading "Valor"
SQL>
SQL> select
2 name "col1",
3 value "col2"
4 from
v$sysstat
5 6 where
7 name LIKE '%workarea%'
8 ;
Estatistica Valor
----------------------------------- ----------------
workarea memory allocated 98,809
workarea executions - optimal 2,442
workarea executions - onepass 0
workarea executions - multipass 0
SQL> /
Estatistica Valor
----------------------------------- ----------------
workarea memory allocated 1,203
workarea executions - optimal 2,829
workarea executions - onepass 0
workarea executions - multipass 0
SQL> col "PGA Hit Ratio" format a13;
select to_char(round(value,4),'999.99') ||'%' "PGA Hit Ratio"
from sys.v_$pgastat
where name = 'cache hit percentage';SQL> 2 3
PGA Hit Ratio
-------------
100.00%
SQL> PROMPT
PROMPT --Memoria na Execucao de SQL - Estatisticas
SQL> --Memoria na Execucao de SQL - Estatisticas
SQL>
SQL> column "col1" format a35 heading "Estatistica"
column "col2" format 999,999,999,990 heading "Valor"
SQL> SQL> SQL> select
2 name "col1",
3 value "col2"
4 from
5 v$sysstat
6 where
7 name LIKE '%workarea%'
8 ;
Estatistica Valor
----------------------------------- ----------------
workarea memory allocated 51,512
workarea executions - optimal 4,207
workarea executions - onepass 0
workarea executions - multipass 0
SQL> col "PGA Hit Ratio" format a13;
select to_char(round(value,4),'999.99') ||'%' "PGA Hit Ratio"
from sys.v_$pgastat
where name = 'cache hit percentage';SQL> 2 3
PGA Hit Ratio
-------------
100.00%
SQL>
Podemos perceber que mesmo executando novamente os comandos o “Hit Ratio” não diminuiu e permaneceu com 100% e o “workarea executions – multipass” com 0.
Vamos verificar o que o advisor da memória nos mostra.
SQL> set lines 155
SQL> set pagesize 1000
SQL> SELECT PGA_TARGET_FACTOR, PGA_TARGET_FOR_ESTIMATE, ESTD_PGA_CACHE_HIT_PERCENTAGE, ESTD_EXTRA_BYTES_RW, ESTD_OVERALLOC_COUNT FROM V$PGA_TARGET_ADVICE ORDER BY 1;
PGA_TARGET_FACTOR PGA_TARGET_FOR_ESTIMATE ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_EXTRA_BYTES_RW ESTD_OVERALLOC_COUNT
----------------- ----------------------- ----------------------------- ------------------- --------------------
.125 26214400 42 432259072 22
.25 52428800 42 432259072 21
.5 104857600 43 412733440 7
.75 157286400 73 114641920 4
1 209715200 100 0 0
1.2 251658240 100 0 0
1.4 293601280 100 0 0
1.6 335544320 100 0 0
1.8 377487360 100 0 0
2 419430400 100 0 0
3 629145600 100 0 0
4 838860800 100 0 0
6 1258291200 100 0 0
8 1677721600 100 0 0
14 rows selected.
SQL>
Podemos perceber pelo advisor que 200M é ideal para a execução dos comandos SQL nas workareas.
Portanto devemos sempre estar verificando se a memória PGA está com a memória de acordo.
Para maiores informance sobre a peformance desta memória entre na Documentação Oracle
-
- Performance do Teste com PGA de 10M
-
- Estimativa de Indicador das Workareas com o parâmetro de memória PGA com 40M.
-
- Estimativa de Indicador das Workareas com o parâmetro de memória PGA com 40M.
-
- Estimativa de Indicador das Workareas com o parâmetro de memória PGA com 40M.
-
- Adivsor PGA com 10M
-
- PGA Hit Ratio com o parâmetro de 10M
-
- Performance do teste com PGA de 200M
-
- Estimativa de Indicador das Workareas com o parâmetro de memória PGA com 100M.
-
- Estimativa de Indicador das Workareas com o parâmetro de memória PGA com 200M.
-
- Adivsor PGA com 200M
-
- PGA Hit Ratio com o parâmetro de 200M
Autor: Maycon Tomiasi
Formado em Tecnologia da Informação na FIPP (Faculdade de Informática de Presidente Prudente), Analista DBA Oracle pela Teiko Soluções em Tecnologia da Informação, residente em Blumenau/ SC, Certificado OCP 10g/11g/12c, OCS 11g Implementation, OCE 11g Performance Tuning, OCE 11g RAC & GRID e OPN Specialist. Conhecimentos em PHP.
