7 Replies Latest reply: Jan 4, 2013 3:31 PM by rp0428 RSS

    Fast access to static data

    user8897201
      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
          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
            >
            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
              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
                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
                  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
                    SQL result cache?
                    • 7. Re: Fast access to static data
                      rp0428
                      >
                      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');