I recently stumbled on another 'new' feature - though in fact it was introduced way back in Oracle 9i. A questioner on the OTN database forum wanted to know how to invalidate cached package state derived from a table that might be updated from another session.
My first suggestion was to implement a time-to-live cache - treat the data as stale if it has been cached for longer than X seconds (using DBMS_UTILITY.get_time to record the load time). All sessions manage their own private caches; a cache can be inconsistent for up to X seconds (if the underlying data is updated immediately after the cache is loaded).
Vetaran OTN poster
Jens Petersen came up with what could be a much neater solution, which is to use a
global context. You can define your own namespace for use with the standard SYS_CONTEXT function, and arrange to get/set attribute name/value pairs - on a per-session or instance-global basis. Like a DIRECTORY, a CONTEXT isn't owned by a specific schema.
Creating the context
To be able to create a context the user must be granted CREATE ANY CONTEXT privilege.
create or replace context my_context
using testuser.my_context_pkg
accessed globally -- including this makes attribute values visible to all sessions
/
A package is linked to the context; this is the only interface that can be used to
set attribute values in the context. This coupling is a little inelegant, but it does mean that only users with EXECUTE permission on the package will be able to
set values.
create or replace package my_context_pkg
as
procedure set_context
(av_attribute in VARCHAR2
,av_value in VARCHAR2
);
end my_context_pkg;
/
create or replace package body my_context_pkg
as
procedure set_context
(av_attribute in VARCHAR2
,av_value in VARCHAR2
)
is
begin
DBMS_SESSION.SET_CONTEXT
( namespace=>'MY_CONTEXT'
, attribute=>av_attribute
, value=>av_value
);
end set_context;
end my_context_pkg;
/
Any API you like to define is supported; this example is the simplest, allowing a completely unconstrained setting of attributes pairs. Here's how you define and use an attribute:
my_context_pkg.set_context('Attribute Name', 'Attribute Value');
Now, what about that original problem - how to notify active sessions that data in their package state might be out of date?
Well, there are two approaches: replace the cache directly with (one or more) CONTEXTs, or construct a dirty cache notification scheme:
Replace cache with CONTEXT
Simply maintain the cache as attribute/values in a CONTEXT. This could be appropriate if the cache was relatively simple (eg a set of lookup codes and descriptions). The PL/SQL caching can be completely discarded; instead use SYS_CONTEXT to get the descriptions, and set up triggers on the lookup table that use MY_CONTEXT_PKG to update the context. The only minor snag is that the context values need to be set (at least once) on instance startup; this could be a scheduled job, or be incorporated into a logon trigger.
Dirty cache notification
Define a 'cache update sequence' context attribute; let's suppose it uses MY_CONTEXT with an attribute name of 'cacheseq'.
When the package initialises its cache, it should read and retain the latest value for 'cacheseq' (treating null as 0). Later, before using values from the cache, it should re-read the latest value for 'cacheseq'. If the numeric value is greater than the retained value, the cache must be dirty. Applications that update the underlying tables (and so invalidate any caches) must increment 'cacheseq' - normally by using a statement level trigger on insert/update/delete.
my_context_pkg.set_context
( 'cacheseq'
, nvl(to_number(SYS_CONTEXT('MY_CONTEXT','cacheseq')),0)+1
);
Updating a CONTEXT attribute is not transactional, and almost certainly not read consistent; if an update is rolled back, we may find our cache being refreshed unnecessarily. If two updates to the table are made concurrently, we might see 'cacheseq' increase by one only. But our dependent sessions should all see that there has been some update activity.
Performance
Before jumping in and using CONTEXTs, I wanted to be sure that the performance wouldn't be a limiting factor. I didn't want to find SYS_CONTEXT calls producing a hidden SELECT ... FROM DUAL as we all remember for SYSDATE, USER etc in the past. So here's a quick test:
declare
li_cached_integer integer := 0;
begin
for i in 1..100000 loop
li_cached_integer := nvl(to_number(sys_context('MY_CONTEXT','cacheseq')),0);
my_context_pkg.set_context('cacheseq',to_char(li_cached_integer+1));
end loop;
end;
Fetching the value 100,000 times (including char to number conversion) takes around 1 second; saving the modified value 100,000 times takes an additional 2 seconds (on an Intel Pentium 4 3.4Gb dual core running XP SP2 and Oracle XE).
Exporting Context Definitions
IMP/EXP won't import/export these objects, and I presume datapump won't either; so the easiest thing to do is create a SQL script using DBMS_METADATA package:
select DBMS_METADATA.GET_DDL(object_type, object_name)
from DBA_OBJECTS WHERE OBJECT_TYPE='CONTEXT';
specify a schema in the call to GET_DDL, if you do, you get:
ORA-31604: invalid NAME parameter "SCHEMA" for object type CONTEXT in function SET_FILTER
Dictionary Views
There are some dictionary views that can be useful:
| ALL_CONTEXT | lists contexts that have been activated (ie a value has been set since startup) |
DBA_OBJECTS | (where object_type = 'CONTEXT') |
| V$CONTEXT | lists attributes set in the current session (appears to ignore globally accessible attributes) |
I'm planning to come back to this topic once I've had a longer play with multiple sessions setting and reading locally and globally accessible context variables.
