Unanswered: Allocate 500MB of RAM for a data structure in Oracle
Our application contains a dedicated read process, consider a stock price retreival read, that is used by 90% of the traffic to the application. Within the database the stored procedure executes the read by accessing a Materialized view, a very simple four line select statement, though of course the query to populate the MV is far more complex.
The remaining 10% of application traffic access OLTP processes with an even balance of read, computations, reporting and update and inserts. The read process, however, is the most important aspect of the system and it is critical that response times for this process are kept at a minimum. It is acceptable for the other, 10% of the traffic, processes to incur a slight delay to allow the read process to maintain its fast access times.
With this as our goal, I would like to know if its possible within Oracle to allocate a fixed amount of memory to store this cached information. As I said above, write and updates to this data are not a concern - for if they were I wouldn't be writing this.
For a simple solution, I could create a C#, JAVA, etc, memory structure to hold this information and then direct each connection that needs to execute this information to the in-memory data structure, hash table for example, rather than go to the database. I would, however, prefer to just access the database directly through the current stored procedure and not have the need to maintain this extra code. Therefore, to compare to the C# solution, I feel there must be a method by which I can instruct Oracle to explicitly store ALL the blocks that comprise this MV, according to my condition, in physical RAM.
The reason for the consideration of this proposition, is to avoid situations where one or more large write processes may cause some of these read blocks to be swapped back out to disk. Another situation can occur when an increase in concurrency is experienced by the application, which would naturally result in an increase in overall memory allocation due to the construction of individual PGAs. We have the requirements, however, that this read query must respond with the fastest possible response times. It is acceptable for connections, not accessing this process, to have smaller PGA areas to ensure the fast response times.
Thanks for reading, and I look forward to hearing your responses.
Last edited by r123456; 09-10-07 at 05:28.
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.