Handling Oracle Database Result Cache Corruption

 


Result Cache,   can be used to cache query and function results in memory. The cached information is stored in a dedicated area inside the shared pool 
where it can be shared by other PL/SQL programs that are performing similar calculations. 

 

The Result Cache is set up using the result_cache_mode initialization parameter with one of these three values:

1.       auto:  The results that need to be stored are settled by the Oracle optimizer
2.       manual:  Cache the results by hinting the statement using the result_cache|no_result_cache hint
3.       force:  All results will be cached



At  times we  come across   result cache Contention  or Corruption  issues  which  can be easily fixed by disabling and re-enabling result cache .    "ORA-600 [qesrcro_dol2ro] / result cache corruption"   can be seen in alert log in case of   corruption issues .





##############  how to check result cache corruption ##############  

SQL> SELECT dbms_result_cache.status() FROM dual; 

 DBMS_RESULT_CACHE.STATUS()
 --------------------------------------------------------------------------------
 CORRUPT



--> Generate Result Cache Report 

SQL> SET SERVEROUTPUT ON 
SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT 





##############   Flushing Result Cache  ##############  

--Flush retaining statistics (default for both are FALSE)
begin
dbms_result_cache.flush (
   retainmem => FALSE,
   retainsta => TRUE);
end;
/

--Flush Retaining memory (default for both are FALSE)
begin
dbms_result_cache.flush (
   retainmem => TRUE,
   retainsta => FALSE);
end;
/

--Flush memory and statistics globally
begin
dbms_result_cache.flush(
   retainmem => TRUE,
   retainsta => FALSE,
   global => TRUE);
end;
/



##############   Manually Use Result Cache Using Hint   ##############  


--Use the result_cache hint because manual mode is being used for this instance

select /*+ result_cache */ 





------------------------------------------
| Id  | Operation                        |
------------------------------------------
|  11 |  COUNT STOPKEY                   |
|  12 |   VIEW                           |
|  13 |    RESULT CACHE                  |
------------------------------------------




##############   Disable and re-enable  the result cache  ##############  

 alter system set RESULT_CACHE_MAX_SIZE=0;

 alter system set RESULT_CACHE_MAX_SIZE=0; 
 alter system set RESULT_CACHE_MAX_SIZE=125856K




##############  other views ##############  

select * from GV$RESULT_CACHE_DEPENDENCY;
select * from GV$RESULT_CACHE_MEMORY;
select * from GV$RESULT_CACHE_OBJECTS;
select * from GV$RESULT_CACHE_STATISTICS;

show parameters result





Set line 400 

--Check result cache parameters

col name for a30
col value for a30
select
   name,
   value
from
   v$parameter
where
   name like '%result%';


--Query the v$result_cache_objects to check if there is any cached object

select
   count(*)
from
   v$result_cache_objects;



col "Space Over" for a30
col "Space Unused" for a30
col "Obj_Name_Dep" for a30
select
   type "Type",
   name "Name",
   namespace "SQL|PL/SQL",
   creation_timestamp "Creation",
   space_overhead "Space Over",
   space_unused "Space Unused",
   cache_id "Obj_Name_Dep",
   invalidations "Invds"
from
   gv$result_cache_objects;  




--Check the Result Cache Setting and Statistics

select
   name "Name",
   value "Value"
from
v$result_cache_statistics;


 

--Check objects cached

select
   o.owner "Owner",
   o.object_id "ID",
   o.object_name "Name",
   r.object_no "Obj Number"
from
   dba_objects o,
   gv$result_cache_dependency r
where
   o.object_id = r.object_no;



--Checking memory blocks and their status in Result Cache Memory

select   *  from
   gv$result_cache_memory;



Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more