Result Cache: RC Latch – Troubleshooting

Most of us know the result cache is introduced in 11g initially with pl/sql result cache and then the query result cache to store the results of query in a separate memory structures in shared pool.

As I do not want to get into how it works, what it do, may be the best start to read is documentation.

However, the results and use cases are vary from case to case for each environment.

But when you (actually developers want to use it more ) want to use you and need to know how much size for cache size is required.

By default the size is allocated automatically , from the documentation,

• Automatic memory management

  If you are using the MEMORY_TARGET initialization parameter to specify memory allocation, Oracle Database allocates 0.25% of the value of the MEMORY_TARGET parameter to the result cache.

• Automatic shared memory management

  If you are managing the size of the shared pool using the SGA_TARGET initialization parameter, Oracle Database allocates 0.50% of the value of the SGA_TARGET parameter to the result cache.

• Manual shared memory management

  If you are managing the size of the shared pool using the SHARED_POOL_SIZE initialization parameter, then Oracle Database allocates 1% of the shared pool size to the result cache.

And the parameter that controls result cache on/off is RESULT_CACHE_MODE= FORCE/MANUAL and the later as default. And you can also specify how much result cache you want to keep by using RESULT_CACHE_MAX_SIZE parameter

One of the biggest issue with Result Cache is the sessions waiting for or AWR report top events shows, Result Cache: RC Latch which can degrade performance queries/database. The two reasons mostly.

• Undersized result cache 
• Concurrency of users using the same result everytime (as single Latch is covering whole result cache pool, for which the concurrent sessions reuse the same object/block in result cache can lead to greater contention)

Lets look at, Undersized Result Cache, Lets start with real example which we have recently came across (manipulated example).

A result cache size of 150MB, viewed in result cache report.

SQL>   set serveroutput on

  SQL>   exec 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                                                                                   --> The size of each buffer in result cache is 1k

  Maximum Cache Size  = 257312K bytes (157312 blocks)                                           --> Maximum Cache size 250MB

  Maximum Result Size = 6665K bytes (7865 blocks)                                                   --> Maximum of size a result 6MB

  [Memory]                                                          

  Total Memory = 261687288 bytes [1.004% of the Shared Pool]                                 --> Telling the 1.004% used , as we have manual memory management

  ... Fixed Memory = 247152 bytes [0.001% of the Shared Pool]      

  ... Dynamic Memory = 261540136 bytes [1.003% of the Shared Pool]  

  ....... Overhead = 452648 bytes                                  

  ....... Cache Memory = 257312K bytes (257312 blocks)              

  ........... Unused Memory = 0 blocks                              

  ........... Used Memory = 257312 blocks                          

  ............... Dependencies = 100 blocks (100 count)              

  ............... Results = 257295 blocks                                                                         --> The results cache has each 1k block 257295 blocks means 250MB

 

  ................... PLSQL   = 257295 blocks (257295 count)                                             --> And apparently most of results are using by PL/SQL objects not query results.

SQL>   select name, value from v$result_cache_statistics; 

NAME                           VALUE                               

------------------------------ --------------------               

Block Size (Bytes)             1024                               

Block Count Maximum            257312      ====>    250MB           

Block Count Current               257312         ====>   250MB 

Result Size Maximum (Blocks)   7865                 

Create Count Success           427809      ====> No of times the result cache created a new results thats about 427809*1k=420MB              

Create Count Failure           0                     

Find Count                     7101               ====> No of times the queries used the result cache this will tell how effective your queries using result cache, the number 7101 out of 157312 is                                                                               less than 5% used                                                              

Invalidation Count             0                            

Delete Count Invalid           2                 ====> No of times that blocks have been deleted becoz of underlying data changes the higher the  number the inefficient usage of result cache.

Delete Count Valid             70260           ====> This will tell you number of times that result cache has deleted the blocks from RC although the blocks were valid, this tells you the undersized                                                                         result cache size.

As see above my result cache has more create counts and delete counts than find count. This is not at all a efficient usage of result cache, lets park what is stored in this result cache for a while and what the object is, lets think regardless of efficient usage of result cache I want to size it properly. This is required because , if the objects are waiting for result cache creating it blocks they may see degraded performance waiting for "Result Cache: RC Latch". Now we have to determine the size result cache for my database.

Size of block 1K

Count of Creation blocks sucessfully, 427809

Deleted Count of Valid blocks due to insufficient size:70260

Find count (reuse of cache) 7101

Taking consideration, 427809-7101+70260 = 490968 i.e around 420MB + 250MB current = 650MB approx. (In my example above the result_cache_max_size is not set and its used default)

The second one, concurrency of the result cache lead to "Result Cache: RC Latch", how to know, The parent address of the object in result cache tells us.

v$result_cache_objects give the name of the object and count but not the concurrent users accessing the object. indeed v$sql can provide a clue.

SQL> select sql_id,executions,elapsed_time,users_opening,users_executing where sql_id='';

The more the users_executing the contention / concurrency it has. To resolve the latch that is due to concurrency you may need to consider reducing the users executing and resulting with same package.

Finally the result cache objects can be found from. V$result_cache_objects, as you see the name contains the name of object with parent address as the parent address is same for all rows in my case.

NAMESPACE  STATUS  NAME                    NUMBER_OF_RESULTS AVG_SCAN_CNT   MAX_SCAN_CNT TOT_BLK_CNT

  PLSQL       Published   "XXXX"."XXX_PKG"::11."FUNCTIONNAME"#s43556wdfdfg#140           157042                           0                      5      257042

  PLSQL       Results   "XXXX"."XXX_PKG"::11."FUNCTIONNAME"#s43556wdfdfg#140           157042                            0                     5      257042

  PLSQL       Results   "XXXX"."XXX_PKG"::11."FUNCTIONNAME"#s43556wdfdfg#140           157042                            0                     5      257042

  PLSQL       Results   "XXXX"."XXX_PKG"::11."FUNCTIONNAME"#s43556wdfdfg#140           157042                             0                     5      257042

Hope this helps.

GeekDBA