Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London

    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.

  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL


    You could try creating the MV with the CACHE option.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2002
    Reading, UK
    If you mean how to isolate the MV from the other tables (so the MV doesnt remove all the blocks belonging to other tables from the buffer cache when it is heavily accessed) which the OLTP is accessing you could use the different buffer pools so that the MV is in one (say recycle) and the other tables/indexes are in default or keep. There are also extra buffer pools for tablespaces created with different block sizes so you could put the MV in one blocksize and the rest in your default blocksize.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts