Identifying Dependencies for the Result Cache in 11g Oracle 11g New Features Tips by Donald BurlesonJuly 17, 2015

Oracle 11g New Features Tips

As shown in the example it is important to identify the dependencies for the result cache if you with to have current data.  And since all sessions can and will utilize the result cache, keeping it current with the data is critical.

Because the result cache uses the PL/SQL signature (name, passed variables and return type) to uniquely identify cached result sets there are some restriction on what can be passed as variables. 

• No IN OUT or OUT variable parameter.

• No invoker rights or anonymous blocks.

• No pipelined table functions.

• No IN variable parameters of type BLOB, CLOB,NCLOB, REF CURSOR, Collection, Object or Record.

• No RETURN type of BLOB, CLOB, NCLOB, REF CURSOR, Object, Collection or Record.

The result cache will consider a request a cache hit if the function and all parameter are bit-wise identical.  The characters ?AA? and ?AA  - are not bit-wise identical and will not result in a result cache hit.

If the function throws an unhandled exception, the results are not stored.

In a RAC environment, each instance will maintain its own result cache but cache invalidation will propagate to all instances. This allows for caching for applications affiliated with that instance but still invalidates that result cache if the dependent object is changed by any instance.

There are 4 new database initialization parameters that are used to support the result cache.

RESULT_CACHE_MAX_SIZE 

This parameter will define the max amount of SGA memory the result cache can use.  If set to 0 then the result cache is disabled.

RESULT_CACHE_MAX_RESULT

This parameter defines the max memory that one result set can access.  It is a percentage of the RESULT_CACHE_MAX_SIZE.

RESULT_CACHE_MODE = FORCE|MANUAL

This parameter works on SQL statements discussed in the next chapter.  If set to FORCE, all valid SQL statement results are cached.  If this parameter is set to MANUAL, only SQL containing the resultcache hint will be cached.

RESULT_CACHE_REMOTE_EXPIRATION

This parameter defines the number of minutes that a result based on a remote object will remain valid.  Since the remote object cannot be monitored for changes, this parameter is used to invalidate those cached objects.

There is a new PL/SQL package added to the database to support using the result cache called DBMS_RESULT_CACHE.  Below is an edited description of the package.

SQL> desc DBMS_RESULT_CACHE

PROCEDURE BYPASS
FUNCTION FLUSH RETURNS BOOLEAN
PROCEDURE FLUSH
FUNCTION INVALIDATE RETURNS NUMBER
PROCEDURE INVALIDATE
FUNCTION INVALIDATE RETURNS NUMBER
PROCEDURE INVALIDATE
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
PROCEDURE INVALIDATE_OBJECT
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
PROCEDURE INVALIDATE_OBJECT
PROCEDURE MEMORY_REPORT
FUNCTION STATUS RETURNS VARCHAR2

The procedure BYPASS turns the result cache functionality on and off.  If set to true the result cache is turned off, and not used.  The cache is not flushed by using this procedure.  The example below turns off the result cache.

SQL> exec dbms_result_cache.bypass (true);

Running this command with a false parameter will turn on the result cache and any previously cached data will be returned.

The STATUS function will return the current status of the result cache functionality.

SQL> select dbms_result_cache.status from dual;

STATUS
------------------------------------------------
ENABLED

The MEMORY_REPORT procedure returns a report on the current memory used by the result cache.

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
Maximum Cache Size  = 2112K bytes (2112 blocks)
Maximum Result Size = 105K bytes (105 blocks)
[Memory]
Total Memory = 103536 bytes [0.069% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.065% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 7 blocks
........... Used Memory = 25 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 23 blocks
................... PLSQL   = 10 blocks (10 count)
................... Invalid = 13 blocks (13 count)

PL/SQL procedure successfully completed.

The report covers the functions discussed earlier in the chapter which have a Boolean result so the memory use is very small.  When functions return larger values, this report can assist you with tuning memory allocations.

The above report is the normal or default report.  A more detailed report is available by passing a 'true? parameter to the procedure.

The remaining functions and procedures in the package are used to invalidate results or flush the cache.  The package provides a procedure and a function for each operation, either can be used as they both execute with the same results.

The FLUSH operation will remove all results from the cache.  It has two Boolean parameters; the first determines if the memory is maintained in the cache or released to the system.  The second parameter determines if the cache statistics are maintained or also flushed.  The following example uses the defaults and will flush the cache, releasing the memory to the system and clearing the statistics.

SQL> exec dbms_result_cache.flush;

If the memory report is re-run the dynamic memory will report 0, indicating that the cache released the memory.

Total Memory = 5140 bytes [0.003% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

The INVALIDATE operation invalidates all results in the cache that are dependent on an object.  These functions are overloaded so you can identify the object by owner and name or by object_id.

SQL> exec dbms_result_cache.invalidate('PUBS','SALES');

The function will return a number indicating the number of results invalidated by the command.

The INVALIDATE_OBJECT operation invalidates specific result objects.  You find result objects from V$RESULT_CACHE_OBJECTS dynamic performance view.

SQL> select id, name from v$result_cache_objects order by 1; 

 ID  NAME
--- ----------------------------------------------------------
  0 PUBS.SALES
  1 PUBS.ABOVE_AVG
  2 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  3 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  4 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  5 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  6 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  7 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  8 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
  9 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
 10 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1
 11 "PUBS"."ABOVE_AVG"::8."ABOVE_AVG"#48348295b9e89e56 #1

12 rows selected.

To invalidate object 11 use the command below.

SQL> exec dbms_result_cache.invalidate_object(11);

The cross-session result cache can provide an incredible performance boost by not constantly execution the same function again and again.  Many times a batch job will execute hundreds of queries on each pass that execute the same function for each query.  By adding the result cache, the function is executed once at the beginning of the run and the result will be used by all the other queries in that run.  This can make a huge difference in the execution performance.  The result cache is also available to a SQL query using a hint.  If you have a large number of queries that all execute the same subquery, placing the result of the subquery in the result cache can make the results available to all the other queries.  This is discussed in the next chapter covering SQL.

Conclusion

In this chapter, we covered the enhancements to the PL/SQL Language introduced in the 11g database. These included:

PL/SQL Sequence access allowing a more efficient method to get the nextval or curval of a sequence without using a query.  This new format is also easier to read making the code more manageable.

The SIMPLE number type is used when there is no need to check bounds or null values.  The SIMPLE integer is more efficient to use and executes in the compiler.

The CONTINUE clause provides a method to terminate the current iteration of a loop and start the next iteration.

PL/SQL Code Inlining provides compile time code optimization.

Compound Triggers provide the ability to perform multiple actions at different timing points in one trigger.  This keeps the trigger code in the trigger and still provides modularity. 

The Cross-Session PL/SQL Result Cache is a powerful new feature that save function results in a cache and will reuse the cached result if the function is called with the same parameters instead of repeatedly executing the function.

The next chapter will introduce some complementary SQL new features that integrate completely into PL/SQL.