Home » SQL & PL/SQL » SQL & PL/SQL » Caching static data (Oracle 10g DB)
Caching static data [message #282881] Sat, 24 November 2007 10:36 Go to next message
vijaynairis
Messages: 2
Registered: September 2005
Location: Montreal
Junior Member
Hi All,

I have an End Of Day process consituting multiple PL/SQL Stored Procedures each of which requires lookups from a lot of static data tables...I have been able to get the cache lookup for a specific session i.e. each stored procedure while execution gets it own cache....I would like this data to be available throughout the EOD procedures i.e. across sessions..

Does anyone have any ideas how to go about this e.g. via contexts or via pinning the package ?

Thanks in advance

Regards...Vijay
Re: Caching static data [message #282883 is a reply to message #282881] Sat, 24 November 2007 11:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>...I have been able to get the cache lookup for a specific session
>each stored procedure while execution gets it own cache
Congratulations!
So what is the problem?


>I would like this data to be available throughout the EOD procedures i.e. across sessions..
Good for you.
What make you think it is NOT available now?

>Does anyone have any ideas how to go about this
See above it says you have a cache for each session. Right?

>
>via contexts or via pinning the package ?
Pinning the package hold the code in SGA; not the data itself.

What make you think you have a problem that needs to be solved by you
or that you know better than Oracle how best to manage the contents of the SGA?

Please provide metrics & reproduceable test cases so we can see for ourselves.
Re: Caching static data [message #282907 is a reply to message #282883] Sat, 24 November 2007 23:16 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Upgrade to 11g might help. It implements a results-cache whereby if you call the same stored function with the same arguments, it can avoid processing.

As for simply avoiding a disk access by caching blocks, Oracle already does this (yes, even between session) and has done for some time.

This would be a good article for you to read.

Ross Leishman
Previous Topic: how do form this string?
Next Topic: How to select records depending on date and time values?
Goto Forum:
  


Current Time: Thu Dec 08 05:53:23 CST 2016

Total time taken to generate the page: 0.08921 seconds