Este artigo tem como objetivo explicar como funciona o Result Cache introduzido no Banco de Dados Oracle 11g.
O “result cache” é um pool de memória dentro da “Shared Pool”. Esta pool possui um Cache do resultado de um comando SQL, que armazena o resultados dos comandos SQL e funções PL/SQL, que armazena valores retornados pelas funções PL/SQL.
Como que o Result Cache funciona?
Quando um comando SQl executa, o banco de dados procura no cache da memória para determinar se o resultado existe no cache. Se o resultado existe, então o banco de dados retira os resultados da memória em vez de executar a query. Se o resultado não está em cache, então o banco de dados executa a query retornando o resultado e armazena o resultado no “result cache”.
Quando usuários executam os comandos SQL e funções repetitivamente, o banco de dados retira linhas do cache, diminuindo assim o tempo de resposta. Resultados em Cache vem a ser inválidos quando dados de objetos dependentes são modificados.
Mais informações sobre “Result Cache” acesse Documento Oracle.
Vamos iniciar nosso teste configurando os parâmetros do result cache.
Certificar se os parâmetros client_result_cache_size > 0, result_cache_max_result > 0, result_cache_max_size > 0 e result_cache_mode MANUAL | FORCE.
RESULT_CACHE_MODE
MANUAL
– O operador ResultCache é adicionado como hint no comando SQL.
FORCE
– O operador ResultCache é adicionado como root para todos os comandos SQL sem colocar hints no comando SQL.
Vamos realizar nosso teste. No terminal 1 vamos conectar com o usuário “SYS” e no Terminal 2 vamos conectar com o usuário “MASTER”.
No terminal 1 vamos visualizar os parâmetros do “result cache”.
Terminal 1
SQL> show user USER is "SYS" SQL> SQL> show parameter result_cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 6240K result_cache_max_result integer 5 result_cache_max_size big integer 6240K result_cache_mode string MANUAL result_cache_remote_expiration integer 0 SQL>
Podemos perceber acima que os parâmetros do “result cache” estão configurados.
Para saber mais sobre cada parâmetro do “result cache” acesse Documentação Oracle 11g.
Vamos verificar a memória “Result Cache”.
SQL> SET SERVEROUTPUT ON SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 6240K bytes (6240 blocks) Maximum Result Size = 312K bytes (312 blocks) [Memory] Total Memory = 9440 bytes [0.001% of the Shared Pool] ... Fixed Memory = 9440 bytes [0.001% of the Shared Pool] ... Dynamic Memory = 0 bytes [0.000% of the Shared Pool] PL/SQL procedure successfully completed. SQL>
Podemos perceber acima que a memória não está sendo utilizada.
Vamos utilizar a mesma colocando no comando SQL o hint /*+ RESULT_CACHE */ para colocá-lo na memória Oracle.
SQL> select /*+ RESULT_CACHE */ count(*) from master.product where prd_name like '%' and prd_id between 3000 and 6000;
COUNT(*)
----------
3001
SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 6240K bytes (6240 blocks)
Maximum Result Size = 312K bytes (312 blocks)
[Memory]
Total Memory = 107836 bytes [0.017% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.015% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
SQL>
Conforme acima, o comando SQL já usando o “Result Cache”.
Vamos realizar um teste com o Hint /*+ NO_RESULT_CACHE */ .
SQL> select /*+ NO_RESULT_CACHE */ count(*) from master.product where prd_name like '%' and prd_id between 3000 and 6000;
COUNT(*)
----------
3001
SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 6240K bytes (6240 blocks)
Maximum Result Size = 312K bytes (312 blocks)
[Memory]
Total Memory = 107836 bytes [0.017% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.015% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
SQL>
Conforme acima, não houve mudança na memória pois apenas 1 comando SQL está na mesma.
Vamos verificar o plano de acesso do comando SQL como o “Result Cache” e sem o “Result Cache”.
SQL> explain plan for select /*+ RESULT_CACHE */ count(*) from master.product where prd_name like '%' and prd_id between 3000 and 6000;
Explained.
SQL> set lines 155
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1342556608
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 39 (0)| 00:00:01 |
| 1 | RESULT CACHE | 43as529bjbjyzdajrv9804s8jv | | | | |
| 2 | SORT AGGREGATE | | 1 | 40 | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 11977 | 467K| 39 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PRD_ID_PK | 11977 | | 25 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PRD_NAME" IS NOT NULL AND "PRD_NAME" LIKE '%')
4 - access("PRD_ID">=3000 AND "PRD_ID"<=6000)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(MASTER.PRODUCT); attributes=(single-row, ordered); parameters=(nls); name="select /*+ RESULT_CACHE */ count(*) from ma
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
ster.product where prd_name like '%' and prd_id between 3000 and 6000"
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
SQL> explain plan for select /*+ NO_RESULT_CACHE */ count(*) from master.product where prd_name like '%' and prd_id between 3000 and 6000;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1342556608
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 39 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 11977 | 467K| 39 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PRD_ID_PK | 11977 | | 25 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PRD_NAME" IS NOT NULL AND "PRD_NAME" LIKE '%')
3 - access("PRD_ID">=3000 AND "PRD_ID"<=6000)
Note
-----
- dynamic sampling used for this statement (level=2)
20 rows selected.
SQL>
Conforme acima, o comando SQL com “Result Cache” e sem o “Result Cache” possui o mesmo plano de acesso “Plan hash value: 1342556608” porém com os resultados na memória, a busca pelos resultados são mais rápidos.
Vamos visualizar o relatório da memória “Result Cache”.
SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 6240K bytes (6240 blocks) Maximum Result Size = 312K bytes (312 blocks) [Memory] Total Memory = 107836 bytes [0.017% of the Shared Pool] ... Fixed Memory = 9440 bytes [0.001% of the Shared Pool] ... Dynamic Memory = 98396 bytes [0.015% of the Shared Pool] ....... Overhead = 65628 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 30 blocks ........... Used Memory = 2 blocks ............... Dependencies = 1 blocks (1 count) ............... Results = 1 blocks ................... SQL = 1 blocks (1 count) PL/SQL procedure successfully completed. SQL>
Vamos limpar a memória “Result Cache” com o pacote DBMS_RESULT_CACHE.
SQL> EXECUTE DBMS_RESULT_CACHE.FLUSH PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 6240K bytes (6240 blocks) Maximum Result Size = 312K bytes (312 blocks) [Memory] Total Memory = 9440 bytes [0.001% of the Shared Pool] ... Fixed Memory = 9440 bytes [0.001% of the Shared Pool] ... Dynamic Memory = 0 bytes [0.000% of the Shared Pool] PL/SQL procedure successfully completed. SQL>
Agora vamos realizar o novo teste porém com um comando SQL diferente.
SQL> set pages 1000
SQL> set time on
16:58:40 SQL> select /*+ RESULT_CACHE */ prd_id, prd_name from master.product where prd_name like '%' and prd_id between 3000 and 6000;
PRD_ID PRD_NAME
---------- --------------------------------------------------
3000 Product A2999
3001 Product A3000
3002 Product A3001
3003 Product A3002
3004 Product A3003
.
.
.
5999 Product A5998
6000 Product A5999
3001 rows selected.
16:58:50 SQL>
16:59:10 SQL> select /*+ RESULT_CACHE */ prd_id, prd_name from master.product where prd_name like '%' and prd_id between 3000 and 6000;
PRD_ID PRD_NAME
---------- --------------------------------------------------
3000 Product A2999
3001 Product A3000
3002 Product A3001
3003 Product A3002
.
.
.
5999 Product A5998
6000 Product A5999
3001 rows selected.
16:59:21 SQL> set time off
SQL>
Vamos gerar o relatório da memória “Result Cache”.
SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 6240K bytes (6240 blocks) Maximum Result Size = 312K bytes (312 blocks) [Memory] Total Memory = 157060 bytes [0.025% of the Shared Pool] ... Fixed Memory = 9440 bytes [0.001% of the Shared Pool] ... Dynamic Memory = 147620 bytes [0.023% of the Shared Pool] ....... Overhead = 82084 bytes ....... Cache Memory = 64K bytes (64 blocks) ........... Unused Memory = 6 blocks ........... Used Memory = 58 blocks ............... Dependencies = 1 blocks (1 count) ............... Results = 57 blocks ................... SQL = 57 blocks (1 count) PL/SQL procedure successfully completed. SQL>
Vamos verificar o plano de acesso do comando SQL.
SQL> explain plan for select /*+ RESULT_CACHE */ prd_id, prd_name from master.product where prd_name like '%' and prd_id between 3000 and 6000;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2629360339
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11977 | 467K| 39 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8gnxr5d4m18cgg517pywq48hzg | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 11977 | 467K| 39 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PRD_ID_PK | 11977 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PRD_NAME" IS NOT NULL AND "PRD_NAME" LIKE '%')
3 - access("PRD_ID">=3000 AND "PRD_ID"<=6000)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(MASTER.PRODUCT); attributes=(ordered); parameters=(nls); name="select /*+ RESULT_CACHE */ prd_id, prd_name from master
.product where prd_name like '%' and prd_id between 3000 and 6000"
Note
-----
- dynamic sampling used for this statement (level=2)
25 rows selected.
SQL>
Podemos perceber acima, que o Name do Result Cache mudou de numeração, pois cada resultado do comando SQL no Result Cache é único porém compartilhado com outros usuários.
No terminal 2 com o usuário “MASTER” vamos realizar o mesmo teste.
Terminal 2
SQL> show user USER is "MASTER" SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 6240K bytes (6240 blocks) Maximum Result Size = 312K bytes (312 blocks) [Memory] Total Memory = 157060 bytes [0.025% of the Shared Pool] ... Fixed Memory = 9440 bytes [0.001% of the Shared Pool] ... Dynamic Memory = 147620 bytes [0.023% of the Shared Pool] ....... Overhead = 82084 bytes ....... Cache Memory = 64K bytes (64 blocks) ........... Unused Memory = 6 blocks ........... Used Memory = 58 blocks ............... Dependencies = 1 blocks (1 count) ............... Results = 57 blocks ................... SQL = 57 blocks (1 count) PL/SQL procedure successfully completed. SQL>
Conforme acima, há resultados no Result Cache na memória Shared Pool.
Vamos tirar o plano do comando SQL que executamos por último no Terminal 1.
SQL> explain plan for select /*+ RESULT_CACHE */ prd_id, prd_name from master.product where prd_name like '%' and prd_id between 3000 and 6000;
Explained.
SQL> set lines 155
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2629360339
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11977 | 467K| 39 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8gnxr5d4m18cgg517pywq48hzg | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 11977 | 467K| 39 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PRD_ID_PK | 11977 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PRD_NAME" IS NOT NULL AND "PRD_NAME" LIKE '%')
3 - access("PRD_ID">=3000 AND "PRD_ID"<=6000)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(MASTER.PRODUCT); attributes=(ordered); parameters=(nls); name="select /*+ RESULT_CACHE */ prd_id, prd_name from master
.product where prd_name like '%' and prd_id between 3000 and 6000"
Note
-----
- dynamic sampling used for this statement (level=2)
25 rows selected.
17:10:07 SQL> select /*+ RESULT_CACHE */ prd_id, prd_name from master.product where prd_name like '%' and prd_id between 3000 and 6000;
PRD_ID PRD_NAME
---------- --------------------------------------------------
3000 Product A2999
3001 Product A3000
3002 Product A3001
3003 Product A3002
3004 Product A3003
.
.
.
5998 Product A5997
5999 Product A5998
6000 Product A5999
3001 rows selected.
17:10:13 SQL> set time off
Conforme acima, ao executar o mesmo comando SQL com outro usuário, o mesmo buscou o resultado do Result Cache e no plano de acesso o campo Name do Result Cache é o mesmo, ou seja, “8gnxr5d4m18cgg517pywq48hzg”.
Portanto podemos concluir que o “Result Cache” é muito útil para retirar resultados de comandos SQL que são muito utilizados, porém devemos sempre verificar o tamanho da “Shared Pool”, pois esta, deve ter memória suficiente para armazenar os objetos PL/SQL, SQL, Dicionário de Dados e o “Result Cache”.
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.
