https://geneticsandfertility.com/064g4qp1 Este artigo tem como objetivo explicar como funciona o Result Cache introduzido no Banco de Dados Oracle 11g.
https://hymnsandhome.com/2024/07/25/pnx8a0eqh 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.
Get Tramadol Online Como que o Result Cache funciona?
Order Tramadol Online Canada 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”.
go
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://etbscreenwriting.com/5ke1ql46pxk 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.
follow link RESULT_CACHE_MODE
MANUAL
– O operador ResultCache é adicionado como hint no comando SQL.
https://gsaudemarketing.com.br/h1qpwdov4 FORCE
– O operador ResultCache é adicionado como root para todos os comandos SQL sem colocar hints no comando SQL.
https://www.pathwaysmagazineonline.com/cdfr00cf 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”.
Tramadol Illegal Order Online Terminal 1
Tramadol 180 Tabs Online 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>
Tramadol Online Overnight Cod 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”.
follow site 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>
follow link 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://autismwish.org/u0ohcayo 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://thefooduntold.com/food-science/33aju4fvxzk Conforme acima, o comando SQL já usando o “Result Cache”.
Vamos realizar um teste com o Hint /*+ NO_RESULT_CACHE */ .
https://brako.com/en/d3ue4081oyt 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://www.pathwaysmagazineonline.com/svgrctbgz 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”.
go 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://geneticsandfertility.com/8nhky2b9 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”.
Online Doctor Prescription Tramadol 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://www.techonicsltd.com/uncategorized/b4pvjzojkk Vamos limpar a memória “Result Cache” com o pacote DBMS_RESULT_CACHE.
https://www.insearchofsukoon.com/vjz9tm7 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://ict-pulse.com/2024/07/c1g4ubbxn75 Agora vamos realizar o novo teste porém com um comando SQL diferente.
https://etbscreenwriting.com/cmwzi04h 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>
https://gsaudemarketing.com.br/o95ike7z Vamos gerar o relatório da memória “Result Cache”.
https://www.adroitprojectconsultants.com/2024/07/25/5z8cx3n 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>
source site Vamos verificar o plano de acesso do comando SQL.
https://living4youboutique.com/te8zbsh 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://www.inaxorio.com/uiszpes7 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.
https://autismwish.org/gc7jtorq78 Terminal 2
https://ict-pulse.com/2024/07/b77qg62 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 site 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.
follow 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://bxscco.com/n4amiv1n7 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://www.pathwaysmagazineonline.com/62thozk2df
https://www.insearchofsukoon.com/g23d3ki43 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.
