Storing PLSQL stored-procedure values in Oracle memory caches for extended periods
- by Ira Baxter
I am collecting runtime profiling data from PLSQL stored procedures. The data is collected as certain stored procedures execute, but it needs to accumululate across multiple executions of those procedures.
To minimize overhead, I'd like to store that profiling data in some PLSQL-accessable Oracle memory-resident storage somewhere for the duration of the data collection interval, and then dump out the accumulated values. The data collection interval might be seconds or hours; its ok not to store this data across system boots. Something like session state in web servers would do.
What are my choices for storing such data?
The only method I know about are contexts in dbms_sessions:
procedure set_ctx (value in varchar8) as
begin
dbms_session.set_context ( 'Test_Ctx', 'AccumulatedValue', value, NULL, 'ProfilerSessionId' );
end set_ctx;
This works, but takes some 50 milliseconds(!) per update to the accumulated value.
What I'm hoping for is a way to access/store an array of values in some Oracle memory using vanilla PLSQL statements, with access times typical of array accesses made to package-local arrays.
