Storing PLSQL stored-procedure values in Oracle memory caches for extended periods

Posted by Ira Baxter on Stack Overflow See other posts from Stack Overflow or by Ira Baxter
Published on 2010-06-08T22:52:55Z Indexed on 2010/06/08 23:02 UTC
Read the original article Hit count: 411

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about session

Related posts about cache