https://www.masiesdelpenedes.com/ug0xm60 Este artigo tem como objetivo explicar como funciona o Result Cache introduzido no Banco de Dados Oracle 11g.

go site 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.

https://audiopronews.com/headlines/rukemd75oc Como que o Result Cache funciona?

https://www.anonpr.net/fbxm9p6ocy 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”.

follow link
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.

https://semichaschaver.com/2025/04/03/h9oepwmvlr 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.

go site RESULT_CACHE_MODE
MANUAL
– O operador ResultCache é adicionado como hint no comando SQL.

http://jannaorganic.co.uk/blog/2025/04/03/tokzoex4 FORCE
– O operador ResultCache é adicionado como root para todos os comandos SQL sem colocar hints no comando SQL.

click here 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”.

get link Terminal 1

https://www.psychiccowgirl.com/o8wok26 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>

https://kirkmanandjourdain.com/g8wrevzf8 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”.

https://www.villageofhudsonfalls.com/b42lfjwjo 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>

https://reggaeportugal.com/krbfwz3co 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.

https://kanchisilksarees.com/gfv8vcy4z 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>

https://townofosceola.com/kfwc79po Conforme acima, o comando SQL já usando o “Result Cache”.
Vamos realizar um teste com o Hint /*+ NO_RESULT_CACHE */ .

https://faroutpodcast.com/3gokw3n7r 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>

https://mhco.ca/f93u4111un 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”.

click here 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>

https://faroutpodcast.com/7zuyr5tqnl 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”.

https://www.anonpr.net/0eq3fho675a 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>

https://lavozdelascostureras.com/llhdc8p3p Vamos limpar a memória “Result Cache” com o pacote DBMS_RESULT_CACHE.

Buy Ultram Tramadol Online 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>

https://kirkmanandjourdain.com/fuxyhz0 Agora vamos realizar o novo teste porém com um comando SQL diferente.

see 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>

Order Tramadol Mastercard Vamos gerar o relatório da memória “Result Cache”.

https://aalamsalon.com/9ta4k83rfa 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>

follow site Vamos verificar o plano de acesso do comando SQL.

https://www.masiesdelpenedes.com/8vuup241 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>

https://reggaeportugal.com/ppit6xmqqbz 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.

http://jannaorganic.co.uk/blog/2025/04/03/rslszr1 Terminal 2

follow site 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>

follow link 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.

https://www.villageofhudsonfalls.com/pl636eukyun 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

https://semichaschaver.com/2025/04/03/87i49v27qu 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

https://colvetmiranda.org/1hxj2w4622

enter site 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.

• 
•