Forum Stats

  • 3,872,013 Users
  • 2,266,363 Discussions
  • 7,911,025 Comments

Discussions

Share Package Variable among sessions

Nimish Garg
Nimish Garg Member Posts: 3,185 Gold Trophy
edited Jan 11, 2016 5:42PM in Database Ideas - Ideas

There are times when we populate a package level collection and use that in multiple times for in-memory access and performance tuning. Sometimes that collection is same for all the users, like I have one for my app_config data, but I need to populate it for every time for every session which consume pga unnecessary and also consume other-resources.

There should be a way to share package level collection/variable in some way. It will solve alot of hassle

BPeaslandDBANimish Garg
5 votes

Active · Last Updated

Comments

  • Gerald Venzl-Oracle
    Gerald Venzl-Oracle Member, Moderator Posts: 85 Employee

    Hi Nimish,

    This can already be achieved since 11g with PL/SQL function result cache: http://docs.oracle.com/database/121/LNPLS/subprograms.htm#LNPLS00817

    You can initialize a collection in a PL/SQL function cached package function. At this stage the collection is cached in the SGA and other package function calls from different sessions will access that cache.

    Same is true for Result Set Cache in SQL if, for example, you have a table for the app_config data: http://docs.oracle.com/database/121/TGDBA/tune_result_cache.htm#TGDBA646

    berx
  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    My feeling is that the hassle is just because that data is not in a table were it belongs. Table blocks are shared between sessions. Result caches are shared by default between session (can now be session specific if I remember right).

    In the case of app config data, why not simply pin this table?

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    There exists also already a construct to share data between sessions. It is called a global application context. See: https://docs.oracle.com/database/121/DBSEG/app_context.htm#DBSEG206

    CREATE OR REPLACE CONTEXT myglobalInfo ...  ACCESSED GLOBALLY;

    And you can access this in a very performant way via the SYS_CONTEXT function.

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    result cache is really a nice feature. But I am looking for something by which we can cache a plsql collection containing complex structure. That collection may be dependent of some business logic and may not contain all the values of a table. For now I am using a MVIew on that table and pinning it's table. But if we can share a pl/sql collection that would be far better approach i think.

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    result cache is really a nice feature. But I am looking for something by which we can cache a plsql collection containing complex structure. That collection may be dependent of some business logic and may not contain all the values of a table. For now I am using a MVIew on that table and pinning it's table. But if we can share a pl/sql collection that would be far better approach i think.

    A variable by its definition is a highly volatile thing. Just consider the implications / serialization issues with that.

    For example one session currently runs a loop over the collection using

    for i in 1..myGlobalCollection.count loop

    ...

    end loop;

    While this is running another session deletes a row from this collection.

    How should the first session behave? Will it give an error when accessing the deleted element? Or should the second session just wait for the first one to finish? This might introduce the same locking conflicts, wait event logic as we have it already in place for tables.

    I think your approach with a materialized view is an excellent one. It seems as your logic requires some initialization of data. But after that this data is not variable anymore but fairly constant. A MV seems like a suitable workaround for such a requirement.

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    A variable by its definition is a highly volatile thing. Just consider the implications / serialization issues with that.

    For example one session currently runs a loop over the collection using

    for i in 1..myGlobalCollection.count loop

    ...

    end loop;

    While this is running another session deletes a row from this collection.

    How should the first session behave? Will it give an error when accessing the deleted element? Or should the second session just wait for the first one to finish? This might introduce the same locking conflicts, wait event logic as we have it already in place for tables.

    I think your approach with a materialized view is an excellent one. It seems as your logic requires some initialization of data. But after that this data is not variable anymore but fairly constant. A MV seems like a suitable workaround for such a requirement.

    thanks for your feedback

  • Gerald Venzl-Oracle
    Gerald Venzl-Oracle Member, Moderator Posts: 85 Employee

    Based on the feedback from the community there already are several ways of achieving similar functionality with other technologies.

    Does anybody know of any use cases that couldn't be addressed with above mentioned technologies?

  • Mike Kutz
    Mike Kutz Member Posts: 6,251 Gold Crown

    Based on the feedback from the community there already are several ways of achieving similar functionality with other technologies.

    Does anybody know of any use cases that couldn't be addressed with above mentioned technologies?

    Gerald Venzl-Oracle wrote:
    
    Based on the feedback from the community there already are several ways of achieving similar functionality with other technologies.
    Does anybody know of any use cases that couldn't be addressed with above mentioned technologies?
    

    I'd say "a stack" or "FIFO"... but AQ might be a better fit for that.

    Being able to store values in /dev/shm sounds interesting, but that might be more of "a solution looking for a problem".

    Beyond that, with respect to data sets, creating an MV in the IN-MEMORY DATABASE might be a better solution.