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: 542
        
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