This discussion is archived
7 Replies Latest reply: Jan 4, 2013 1:31 PM by rp0428 RSS

Fast access to static data

user8897201 Newbie
Currently Being Moderated
Hi,

In the beginning of my stored procedure I need to read static data from parameters tables. This stored procedure is executed a lot of times. There's any good approach to ensure that static data is always in memory and access will be faster?

We are using Oracle 11gR2.

thanks
  • 1. Re: Fast access to static data
    sb92075 Guru
    Currently Being Moderated
    user8897201 wrote:
    Hi,

    In the beginning of my stored procedure I need to read static data from parameters tables. This stored procedure is executed a lot of times. There's any good approach to ensure that static data is always in memory and access will be faster?

    We are using Oracle 11gR2.

    thanks
    what happens when "static" data changes in parameter tables?
  • 2. Re: Fast access to static data
    rp0428 Guru
    Currently Being Moderated
    >
    In the beginning of my stored procedure I need to read static data from parameters tables. This stored procedure is executed a lot of times. There's any good approach to ensure that static data is always in memory and access will be faster?

    We are using Oracle 11gR2.
    >
    Put the static variables in a package spec and initialize them in the initialization (static) section of the package body. The initialization section is executed one time in a session when the package is first referenced so any package variables initialized in the package spec itself or in the initialization section will always be in memory for the duration of the session.
    create or replace package test_vars is
      myState VARCHAR2(30);
    end;
    /
    create or replace package body test_vars is
    begin
      myState := 'California';
    end;
    /
    You would replace the static assignment of 'myState' with a query to read your parameter table and init your variables.

    Then your session (or your procedure) just needs to make a reference to the package and that will load the package and execute the initialization section loading those variables and making them available for the rest of the session.
  • 3. Re: Fast access to static data
    user8897201 Newbie
    Currently Being Moderated
    The issue is that the stored procedure is executed through a job execution, i.e., everytime the job is executed I need to read again the table.
  • 4. Re: Fast access to static data
    Paul Horth Expert
    Currently Being Moderated
    user8897201 wrote:
    The issue is that the stored procedure is executed through a job execution, i.e., everytime the job is executed I need to read again the table.
    Don't bother doing anything. If the data really is read frequently it will remain in the Oracle buffer (memory) anyway.
  • 5. Re: Fast access to static data
    jeneesh Guru
    Currently Being Moderated
    To add to Paul, you can think of pinning the look up tables in KEEP pool..

    And you can think of IOT tables also, if required..
  • 6. Re: Fast access to static data
    jihuyao Journeyer
    Currently Being Moderated
    SQL result cache?
  • 7. Re: Fast access to static data
    rp0428 Guru
    Currently Being Moderated
    >
    The issue is that the stored procedure is executed through a job execution, i.e., everytime the job is executed I need to read again the table.
    >
    We can only respond based on the information you provide.

    You haven't provided ANY information about how much data you are talking about or how it will be used. Until, or unless, you do there isn't any way to provide solutions for your particular use case.

    You can always put the info into a global context where it will be available database wide to all sessions and users.

    See the examples in the 'CREATE CONTEXT' section of the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5002.htm
    >
    ACCESSED GLOBALLY
    This clause indicates that any application context set in namespace is accessible throughout the entire instance. This setting lets multiple sessions share application attributes.

    Examples

    Creating an Application Context: Example

    This example uses a PL/SQL package empno_ctx, which validates and secures the hr application. See Oracle Database Security Guide for the example that creates this application context. The following statement creates the context namespace hr_context and associates it with the package empno_ctx:

    CREATE CONTEXT hr_context USING empno_ctx;
    You can control data access based on this context using the SYS_CONTEXT function. For example, suppose the empno_ctx package has defined an attribute employee_id as a particular employee identifier. You can secure the base table employees by creating a view that restricts access based on the value of employee_id, as follows:

    CREATE VIEW hr_org_secure_view AS
    SELECT * FROM employees
    WHERE employee_id = SYS_CONTEXT('empno_ctx', 'employee_id');

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points