Result Cache

Result Cache (New Feature Oracle11g)

Essa melhoria do Oracle11g deve trazer ganhos de desempenhos consideráveis a aplicações, sem qualquer alteração em códigos PL/SQL.

Objetivo:
Demonstrar como esse recurso funciona

Ao longo das versões do RDBMS Oracle tem se expandido continuamente os mecanismos de compactação e utilização de memória (cache) mostrando uma contínua preocupação com desempenho. Todos estamos familiarizados com os conceitos data buffer cache, library cache, shared pool, large pool, entre outros e agora vamos entender um pouco mais sobre o RESULT CACHE essa nova feature do Oracle 11g.

O RESULT CACHE tem como objetivo armazenar o resultado de consultas para re-utilização, para dar uma idéia de como funciona o result cache, podemos pensar numa view materializada, no entanto ao invés das linhas (Result Set) ficarem armazenadas numa “tabela” ficarão armazenadas numa área de memória específica.

A figura1 (Result Cache) mostra a representação dessa área de memória:


Uma breve explicação sobre os parâmetros que deverão ser utilizados para a configuração dessa nova feature:

SQL> SELECT name, value, isdefault
  2  FROM   v$parameter
  3  WHERE  name LIKE 'result_cache%';

NAME                               VALUE              ISDEFAULT
---------------------------------- ------------------ ---------
result_cache_mode                  MANUAL             TRUE
result_cache_max_size              1081344            TRUE
result_cache_max_result            5                  TRUE
result_cache_remote_expiration     0                  TRUE
4 rows selected.

result_cache_mode: O valor default para esse parâmetro é manual isso significa que nós precisamos explicitamente acionar esse recurso. Para acionarmos podemos utilizar o hint /*+ RESULT_CACHE */.

Exemplo:

SQL> SELECT /*+ RESULT_CACHE */ E.LAST_NAME, E.SALARY, D.DEPARTMENT_NAME
2 FROM EMPLOYEES E, DEPARTMENTS D
3       WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
4         AND D.DEPARTMENT_ID = 110 ;

—————————————————————————-
| Id  | Operation                     | Name                       | Rows  |
—————————————————————————-
|   0 | SELECT STATEMENT              |                            |     2 |
|   1 |  RESULT CACHE                 | 0fbq9vds1zha715rc3pmz5n39z |       |
|   2 |   NESTED LOOPS                |                            |     2 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS                |     1 |
|*  4 |     INDEX UNIQUE SCAN         | DEPT_ID_PK                 |     1 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES                  |     2 |
|*  6 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX          |     2 |
—————————————————————————-

Podemos configurar esse parâmetro com o valor FORCE, isso significa que todas as instruções SELECTs válidas serão armazenadas nessa sub-área.

result_cache_max_size: é o tamanho máximo em bytes de resultados que ficarão armazenados nessa área de memória.

O result cache é alocado na shared_pool, mas é mantido separadamente. Isso significa que se for realizado um FLUSH SHARED_POOL os dados não são retirados dessa área. Vale lembrar que a SHARED_POOL é gerenciada pelo algoritmo LRU (least recently used), mais informações sobre esse algoritmo de gerenciamento de memória podem ser encontradas em [1].

Para que seja realizado uma limpeza das linhas armazenadas nessa sub-área de memória (RESULT CACHE) é necessário utilizar a package DBMS_RESULT_CACHE.

Exemplo:

Exec dbms_result_cache.flush ;

result_cache_max_result: especifica a maior pencentual que é capaz de ser usado por um único conjunto de resultados (o default é 5%).

result_cache_remote_expiration: especifica o número (em minutos) para que o conjunto de resultados baseado em um objeto remoto pode permanecer válido. O default é 0 e significa que os dados baseados em objetos remotos não são armazenados.

Todos os parâmetros mencionados acima são dinâmicos.

Comparativo Desempenho:

Configurações:

INSTANCE_NAME    VERSION           STATUS
—————- —————– ————
oracle11         11.1.0.6.0        OPEN

NAME                                            VALUE
——————————           ————————
result_cache_mode                       MANUAL
result_cache_max_size                 5242880
result_cache_max_result              5
result_cache_remote_expiration  10

create table t2
as select trunc(dbms_random.value(0,5000)) join1,
trunc(dbms_random.value(0, 40 )) v1, rpad(‘x’,100) padding
from all_objects where rownum <= 10000;

create table t1
as select trunc(dbms_random.value(0,4)) join1,
trunc(dbms_random.value(0, 40 )) v1,
rpad(‘x’,100) padding from
all_objects where rownum <= 10000;

execute dbms_stats.gather_table_stats(ownname=>’HR’, tabname=>’T2′,method_opt=>’for all columns size 1′);

execute dbms_stats.gather_table_stats(ownname=>’HR’, tabname=>’T1′,method_opt=>’for all columns size 1′);

10:37:54 SQL> select count(*) from t1 ;

COUNT(*)
———-
10000

10:37:55 SQL> select count(*) from t2 ;

COUNT(*)
———-
10000

Primeira Execucao:

SQL> select t1.join1, t1.v1, t2.join1, t2.v1 from t1, t2
2   where t1.join1 = t2.join1  ;

14967 linhas selecionadas.

——————————————-
| Id  | Operation          | Name | Rows  |
——————————————-
|   0 | SELECT STATEMENT   |      | 22946 |
|*  1 |  HASH JOIN         |      | 22946 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 |
——————————————-

EstatÝstica
———————————————————-
1  recursive calls
0  db block gets
328  consistent gets
0  physical reads
0  redo size
245467  bytes sent via SQL*Net to client
11383  bytes received via SQL*Net from client
999  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
14967  rows processed

Segunda Execucao:

SQL> /

——————————————-
| Id  | Operation          | Name | Rows  |
——————————————-
|   0 | SELECT STATEMENT   |      | 22946 |
|*  1 |  HASH JOIN         |      | 22946 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 |
——————————————-

EstatÝstica
———————————————————-
0  recursive calls
0  db block gets
328  consistent gets
0  physical reads
0  redo size
245467  bytes sent via SQL*Net to client
11383  bytes received via SQL*Net from client
999  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
14967  rows processed

Vamos ao teste com o hint /*+ RESULT_CACHE */:

Primeira Execucao:

SQL> select /*+ RESULT_CACHE */ t1.join1, t1.v1, t2.join1, t2.v1 from t1, t2
2  where t1.join1 = t2.join1  ;

14967 linhas selecionadas.

——————————————————————
| Id  | Operation           | Name                       | Rows  |
——————————————————————
|   0 | SELECT STATEMENT    |                            | 22946 |
|   1 |  RESULT CACHE       | dd4wfm5nkrqf24ncm3g2uhfybd |       |
|*  2 |   HASH JOIN         |                            | 22946 |
|   3 |    TABLE ACCESS FULL| T1                         | 10000 |
|   4 |    TABLE ACCESS FULL| T2                         | 10000 |
——————————————————————

EstatÝstica
———————————————————-
1  recursive calls
0  db block gets
328  consistent gets
0  physical reads
0  redo size
245467  bytes sent via SQL*Net to client
11383  bytes received via SQL*Net from client
999  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
14967  rows processed

Segunda Execucao:

SQL> /

14967 linhas selecionadas.

——————————————————————
| Id  | Operation           | Name                       | Rows  |
——————————————————————
|   0 | SELECT STATEMENT    |                            | 22946 |
|   1 |  RESULT CACHE       | dd4wfm5nkrqf24ncm3g2uhfybd |       |
|*  2 |   HASH JOIN         |                            | 22946 |
|   3 |    TABLE ACCESS FULL| T1                         | 10000 |
|   4 |    TABLE ACCESS FULL| T2                         | 10000 |
——————————————————————

EstatÝstica
———————————————————-
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
245467  bytes sent via SQL*Net to client
11383  bytes received via SQL*Net from client
999  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
14967  rows processed

Na segunda execução com o hint /*+ RESULT_CACHE */ não há acesso a disco nem leitura de blocos. Como pode ser visto acima os consistents gets é igual a 0. Portanto a execução também fica mais rápida.

Para obter informações sobre o RESULT CACHE podemos consultar as seguintes views:
V$RESULT_CACHE_DEPENDENCY;
V$RESULT_CACHE_MEMORY;
V$RESULT_CACHE_OBJECTS; e
V$RESULT_CACHE_STATISTICS.

Exemplo:

SQL> SELECT ro.id
2      ,      ro.name
3      ,      wm_concat(do.object_name) AS object_names
4      FROM   v$result_cache_objects    ro
5                LEFT OUTER JOIN
6             v$result_cache_dependency rd
7                ON (ro.id = rd.result_id)
8                LEFT OUTER JOIN
9             dba_objects               do
10               ON (rd.object_no = do.object_id)
11     WHERE  ro.type = ‘Result’
12     GROUP  BY
13            ro.id
14     ,      ro.name;

ID NAME                                                            OBJECT_NAMES
———- ——————————————      —————————–
2 SELECT /*+ RESULT_CACHE */ E.L         DEPARTMENTS,EMPLOYEES
AST_NAME, E.SALARY, D.DEPARTME
NT_NAME
FROM EMPLOYEES E, DEPARTM
ENTS D
WHERE D.DEPARTMENT

5 select /*+ RESULT_CACHE */ t1.      T1,T2
join1, t1.v1, t2.join1, t2.v1
from t1, t2
where t1.join1 = t2.join1

Caso queiram se aprofundar mais no assunto recomendo a leitura de [2].

Referencias Bibliográficas:

[1] http://regulus.pcs.usp.br/~jean/so/AULA%2015%20-%20Algoritmos%20de%20Substitui%E7%E3o%20de%20P%E1ginas.pdf, acessado em 13/Mar/2011

[2] http://www.oracle-base.com/articles/11g/CrossSessionPlsqlFunctionResultCache_11gR1.php, acessado em 13/Mar/2011

Sobre oradeep

José Eduardo Fiamengui Júnior Graduação: Tecnologia em Informática pela Universidade Estadual de Campinas (Unicamp) Pós-Graduação: Administração em Banco de Dados Oracle pelo Instituto Brasileiro de Tecnologia Avançada (IBTA) Mba em Gestão Estratégica em TI pela FGV OCE Certified ITIL Certified Empresa Atual: Dba Oracle e Performance Specialist na Ccee Empresa Atual: Instrutor Oracle IBTA
Esta entrada foi publicada em Oracle. ligação permanente.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s