Optimizing Oracle11g with Result_Cache

Oracle11g comes with a lot of new features to help improve database performance. One of the feature that can be used to drastically improve performance is the Result_Cache option.

Used wisely, the Result_Cache can improve queries to commonly used tables such as Cities and Countries lookup tables. On one of our project, we used Result_Cache option to retrieve a list of active medical doctors and we were able to achieve 30X performance improvement compared to not using that option.

The Result_Cache option is an indicator to command Oracle to store the result set in memory (SGA) as long as there is no change in the data. The initial query will take longer to return data compared to subsequent queries which will fetch the result set from memory.

Oracle11g Rel2 has some minor enhancement made to the command issued to handle the Result_Cache. Oracle has simplified the command and developers do not have to worry about having to keep track of the specific tables used in functions. Namely the Relies_On command is no longer required in Rel2.

Sample Oracle functions and SQL statements can be reviewed from Oracle’s AskTom http://www.oracle.com/technology/oramag/oracle/07-sep/o57asktom.html

or on ToadWorld’s Steven Feuerstein blog http://www.toadworld.com/Experts/StevenFeuersteinsPLSQLExperience/TrainingandPresentations/PLSQLNewFeatures/tabid/161/Default.aspx#Oracle11g

If you would like to comment on this blog or contact me personally, I can be reached at mike_blogger@optimal.ca