PIO e Oracle Cache

Para iniciar a explicação, é preciso entender que o LIO (Logical I/O) representa buffer cache e PIO (Phisycal I/O) representa uma leitura ao disco.
Como é do conhecimento de qualquer DBA, o segundo acesso e seguintes acessos a uma query deve fazer com que o PIO diminua,  pois parte dos dados já devem ter passado para o buffer cache. Existem no entanto outros factores que influenciam o facto de não ser possível efectuar o cache além do correcto tuning, como por exemplo o número de users concurrencais..

Com este dado como adquirido, decidi continuar e criar uma tabela para aquilo que pretendia na altura. O conteúdo da tabela não interessa para o caso..


SQL> create table f_test as SELECT dbms_random.value(0,10) N1, dbms_random.value(11,200) N2, dbms_random.string('A', 20) N3
from dual connect by level <= 1000000;

SQL> set autotrace traceonly statistics;

SQL> select N1, N3 from f_test;

1000000 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
75740 consistent gets
8298 physical reads
0 redo size
52464934 bytes sent via SQL*Net to client
733710 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed

 

Executando o comando uma segunda vez…


SQL> /

1000000 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
75740 consistent gets
8292 physical reads
0 redo size
52464934 bytes sent via SQL*Net to client
733710 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed

Parece que os physical reads praticamente não variaram, o que poderá significar que a nossa cache nesta máquina virtual instalada não se está a comportar como esperariamos. Vejamos:


SQL> show parameter db_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0

Aparentemente o parâmetro que define a cache, ou seja, o tamanho para o DEFAULT buffer pool está a definido a 0. Está a 0 pois o valor do SGA_TARGET está definido (estamos em 10g) e logo por omissão o valor da DB_CACHE_SIZE é internamente definido pelo Oracle como sendo 0. Caso o valor do SGA_TARGET não esteja devidamente definido o valor por omissão é 4Mb * Nº de CPUs no parâmetro DB_CACHE_SIZE.
A questão é que usar o SGA_TARGET (10g apenas) torna a vida bem mais simples, evitando muita confusão entre db_cache_size, shared_pool_size, large_pool_size, etc, e permitindo que a gestão do SGA (System Global Area) sejá automatizada. Vejamos o que temos definido para o SGA_TARGET:


SQL> show parameter sga_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 140M

 

Sendo que o valor do sga_target é dinâmico e pode ser esticado até ao valor definido no SGA_MAX_SIZE:


SQL> show parameter sga_max_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 140M

Estamos assim no máximo, sendo que se tentarmos definir um valor maior para o SGA_TARGET seremos deparados com um erro. É possivel alterar este valor, tendo como atenção que é necessário reiniciar a instância. É preciso também ter em consideração que em sistemas 32bits, o valor máximo para o SGA_MAX_SIZE deve ser 4GB.

Fazendo pequenas alterações 😉

 


SQL> alter system set sga_max_size = 280m scope=spfile;

 

Será necessário fazer restart da instância. Se tiverem privilégios para isso, o comando é startup force.

Executando a query pela segunda vez, depois de reiniciar a instância: 


SQL> select N1, N3 from f_test;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
75740 consistent gets
0 physical reads
0 redo size
52464934 bytes sent via SQL*Net to client
733710 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed

 

0 physical reads o que significa que a cache já está suficientemente capaz de ter alguma influência no PIO praticado, quando são executadas diversas querys exactamente iguais.
Este exemplo é meramente dado como demonstração, apesar de no tuning o objectivo será sempre reduzir os LIO e consequentemente os PIO, esta abordagem do tuning do SGA deve ser bastante cuidada. Não será boa prática, ajustar o SGA baseado apenas numa query exemplificativa, por isso reforço a ideia que este exemplo só serve para entender como as coisas funcionam e de que forma os vários factores influenciam os resultados que vemos.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s