caching query results in 11g

Cache consistency
 – consistency maintained by receiving notifications on subsequent roundtrip to server
 – in a lreatively idle client, cache can trail behind DB no more than CACHE_LAG milliseconds
 – changes invalidate affected cached results
 – cache bypassed if session has outstanding xactions on tables in query
 – free application from manually having to check for changes, poll the database, refresh result-sets
 – it is very difficult to program these changes because the database does not expose all the elements required.
 – you need the 11g client and the 11g server to make this work properly
 – you need to enable statement caching to make this work. It can be done in the client or on the mid-tier

OCI consistent client cache enabling
 – works with OCI-based drivers. Requires enterprise edition to make this work
 – on server set CLIENT_RESULT_CACHE_SIZE, must be non-zero upto 2G, CLIENT_RESULT_CACHE_LAG – 3000ms default. Setting LAG to zero disables lag
 – on client (set in sqlnet.ora)
    – OCI_RESULT_CACHE_MAX_SIZE (optional)
    – OCI_RESULT_CACHE_MAX_RSET_SIZE (optional)
    – OCI_RESULT_CACHE_MAX_RSET_ROWS (optional)
the client values override the server settings and can be done temporarily

the query requires /*+ result_cache */ hint in the code. This will be automated at a later date

look for candidate queries in AWR
 – frequent queries in Top SQL
 – identify candidate queries on r-o/r-mostly tables
 – sprinkle the hint on queries and measure
monitor usage
 – client_result_cache_stats$

233 thoughts on “caching query results in 11g”

  1. I enjoy you because of all your valuable hard work on this site. My aunt take interest in conducting investigation and it’s easy to understand why. My partner and i hear all regarding the dynamic form you convey efficient tips and tricks via the web site and inspire response from other individuals about this topic while our child is actually studying a lot of things. Take pleasure in the remaining portion of the new year. You have been doing a remarkable job.

  2. I really wanted to post a quick note to appreciate you for these nice tactics you are giving on this site. My extensive internet search has finally been compensated with good quality knowledge to go over with my close friends. I ‘d say that we site visitors actually are undeniably fortunate to be in a magnificent community with very many special professionals with very helpful techniques. I feel truly privileged to have used the website and look forward to many more cool times reading here. Thank you once more for all the details.

  3. Thanks so much for giving everyone an extraordinarily superb possiblity to read critical reviews from this web site. It’s usually so good plus stuffed with amusement for me personally and my office acquaintances to visit your blog at the very least thrice weekly to find out the new secrets you have got. And definitely, we are at all times satisfied with your terrific thoughts served by you. Some 1 points in this post are indeed the finest I’ve ever had.

  4. I’m just writing to let you be aware of what a wonderful encounter my child experienced visiting the blog. She discovered many things, which include how it is like to have an ideal teaching heart to let many others without problems grasp a variety of specialized topics. You truly did more than her expected results. I appreciate you for imparting these warm and friendly, safe, revealing and also cool tips about the topic to Tanya.

  5. Needed to send you this very little remark to be able to say thanks a lot as before on your awesome secrets you have shown in this case. This is strangely generous with you to deliver publicly exactly what most of us could possibly have made available as an e-book to make some bucks on their own, especially since you might have tried it in case you decided. Those tricks as well worked to become a fantastic way to be aware that the rest have the identical dream the same as my personal own to realize lots more with respect to this matter. I’m certain there are some more pleasant moments ahead for individuals who browse through your blog.

  6. I really wanted to compose a brief remark to be able to say thanks to you for all of the nice strategies you are posting on this site. My time consuming internet lookup has now been compensated with awesome facts to go over with my visitors. I ‘d claim that we readers actually are rather lucky to be in a fabulous place with so many awesome people with great things. I feel pretty blessed to have used your website and look forward to plenty of more brilliant times reading here. Thanks once again for all the details.

  7. I would like to point out my passion for your kind-heartedness giving support to individuals that really need guidance on in this concern. Your special commitment to getting the message all around came to be exceptionally interesting and has specifically empowered men and women much like me to reach their dreams. The interesting help implies a great deal to me and far more to my colleagues. Best wishes; from all of us.

  8. I enjoy you because of all your valuable effort on this website. My mother enjoys setting aside time for research and it’s really simple to grasp why. All of us learn all about the compelling ways you provide useful tips and hints via your web blog and even inspire participation from other people on this area so our favorite princess is always discovering so much. Take pleasure in the remaining portion of the year. You’re conducting a pretty cool job.

  9. I needed to post you this little note just to say thanks a lot the moment again for all the unique advice you have provided on this page. It was incredibly open-handed with people like you to provide publicly what a few individuals could possibly have sold as an e-book in making some cash on their own, mostly given that you might have tried it if you ever desired. These strategies in addition worked like a fantastic way to fully grasp someone else have the identical passion like my own to realize somewhat more regarding this matter. I think there are some more enjoyable occasions up front for many who scan your blog post.

  10. Thanks a lot for providing individuals with such a brilliant possiblity to read in detail from this blog. It really is so amazing and as well , jam-packed with amusement for me and my office acquaintances to visit your website minimum thrice in one week to read through the new things you will have. Not to mention, I am also actually amazed concerning the good secrets served by you. Certain two points in this posting are honestly the best I’ve had.

  11. I as well as my pals came digesting the great suggestions on your web page and then quickly developed a horrible suspicion I never thanked the site owner for those strategies. The ladies are already stimulated to read through them and have pretty much been taking pleasure in these things. I appreciate you for indeed being well considerate as well as for making a choice on variety of high-quality things millions of individuals are really eager to learn about. My personal honest apologies for not expressing appreciation to earlier.

  12. My wife and i felt quite comfortable Chris could deal with his web research via the ideas he grabbed out of the web site. It’s not at all simplistic to just be freely giving tips and hints the rest might have been selling. Therefore we figure out we’ve got the writer to appreciate because of that. The entire illustrations you’ve made, the straightforward site navigation, the friendships you can make it easier to create – it’s got many excellent, and it is assisting our son in addition to the family reckon that that matter is thrilling, and that is extraordinarily indispensable. Thanks for everything!

  13. My husband and i felt quite more than happy that Jordan managed to do his reports from the precious recommendations he got while using the blog. It’s not at all simplistic just to happen to be giving for free procedures many others may have been trying to sell. We know we’ve got the writer to appreciate for this. Those explanations you made, the easy blog menu, the friendships you can aid to promote – it’s everything wonderful, and it is making our son in addition to the family recognize that that article is satisfying, which is quite vital. Many thanks for the whole lot!

  14. I and my buddies were taking note of the excellent points located on the website and then at once I got a terrible suspicion I had not expressed respect to the website owner for those strategies. My young men became passionate to study all of them and have actually been taking pleasure in those things. Appreciation for truly being indeed kind and also for finding certain superior useful guides most people are really eager to learn about. My honest apologies for not saying thanks to you sooner.

  15. I must express my thanks to the writer for rescuing me from this challenge. Because of exploring throughout the world wide web and finding thoughts which are not powerful, I assumed my entire life was gone. Being alive minus the strategies to the difficulties you have sorted out by means of your main write-up is a critical case, and the kind that might have in a wrong way affected my career if I hadn’t noticed your web blog. Your personal expertise and kindness in taking care of every item was invaluable. I am not sure what I would have done if I hadn’t encountered such a step like this. I’m able to at this moment relish my future. Thanks a lot so much for your skilled and result oriented help. I won’t hesitate to propose the sites to anybody who will need direction on this area.

  16. I actually wanted to write down a brief message to be able to express gratitude to you for the splendid guidelines you are giving out at this site. My long internet research has now been rewarded with incredibly good details to write about with my best friends. I would suppose that most of us readers are undoubtedly blessed to exist in a wonderful site with many outstanding professionals with good secrets. I feel rather fortunate to have seen your web page and look forward to plenty of more excellent minutes reading here. Thanks a lot once more for a lot of things.

  17. I wanted to write down a quick message so as to say thanks to you for all of the fabulous tips you are giving here. My time intensive internet research has at the end been paid with incredibly good facts and strategies to share with my visitors. I would believe that most of us readers actually are extremely lucky to dwell in a good network with many marvellous people with very beneficial strategies. I feel very much happy to have seen the weblog and look forward to so many more fabulous moments reading here. Thanks a lot once again for all the details.

  18. I precisely wanted to say thanks again. I am not sure the things that I would’ve taken care of without the actual aspects shown by you over such a area. It had been the intimidating matter in my circumstances, nevertheless being able to view the specialised manner you treated the issue took me to leap over contentment. Now i am grateful for the work and then believe you comprehend what a great job your are accomplishing training others through a blog. I’m certain you haven’t met all of us.

  19. I wanted to draft you this very small note just to say thanks the moment again on the beautiful techniques you’ve shared in this case. It is certainly shockingly generous of you in giving publicly all that many of us could have made available as an ebook to generate some profit on their own, mostly since you could have done it in the event you wanted. The tips as well acted to provide a good way to understand that some people have similar eagerness just as my very own to see many more with regards to this condition. I know there are some more fun moments up front for those who take a look at your site.

  20. I precisely wished to thank you so much all over again. I do not know what I could possibly have created in the absence of these creative concepts shared by you relating to this situation. Certainly was a very alarming concern in my circumstances, nevertheless being able to view the well-written tactic you dealt with that took me to weep for joy. Now i’m happier for this work and even trust you know what an amazing job you have been accomplishing training many people via your webpage. Most probably you’ve never got to know any of us.

  21. I simply wished to say thanks all over again. I am not sure what I would’ve followed in the absence of the entire recommendations provided by you about such subject. It seemed to be an absolute frightful condition for me, but being able to see the very skilled form you treated the issue made me to weep with joy. I’m happy for your work and as well , pray you find out what an amazing job that you are undertaking instructing the others through the use of a web site. I am certain you haven’t got to know all of us.

  22. I am writing to let you be aware of what a extraordinary experience my friend’s child undergone going through your blog. She learned some things, including what it is like to have an awesome giving style to make many more without difficulty fully understand various problematic subject areas. You undoubtedly surpassed my expected results. I appreciate you for supplying such useful, healthy, informative and even fun tips on your topic to Gloria.

  23. Thank you a lot for providing individuals with remarkably spectacular chance to discover important secrets from here. It can be so enjoyable plus jam-packed with amusement for me personally and my office colleagues to visit your blog really 3 times in 7 days to see the fresh issues you have got. Of course, I am also at all times motivated considering the tremendous secrets you serve. Some 4 facts on this page are undeniably the most impressive we’ve ever had.

  24. I am commenting to let you understand what a cool discovery my wife’s girl encountered studying your web page. She picked up many issues, not to mention how it is like to possess a wonderful helping character to get many people with ease fully understand selected very confusing topics. You actually exceeded people’s expected results. Thank you for churning out those precious, trustworthy, revealing and even cool tips on your topic to Julie.

  25. I would like to convey my passion for your kind-heartedness for individuals who really want help with in this issue. Your special commitment to getting the solution across turned out to be incredibly insightful and have always encouraged workers just like me to realize their desired goals. Your amazing important publication denotes a whole lot to me and substantially more to my fellow workers. Thanks a lot; from each one of us.

  26. I enjoy you because of your whole efforts on this blog. Ellie takes pleasure in getting into investigation and it is obvious why. Most of us hear all concerning the lively ways you convey good guidelines by means of this web site and in addition cause contribution from other ones on this point then our own daughter is always being taught a great deal. Enjoy the remaining portion of the new year. Your doing a fabulous job.

  27. Thanks so much for providing individuals with such a pleasant possiblity to read articles and blog posts from here. It can be so awesome and also full of amusement for me and my office mates to visit your site nearly thrice per week to read the fresh guidance you have got. And lastly, I am also at all times amazed with the striking thoughts you serve. Selected 3 facts in this post are without a doubt the finest we have all ever had.

  28. I simply wanted to write a note to thank you for those unique items you are sharing here. My rather long internet search has at the end of the day been compensated with professional knowledge to talk about with my classmates and friends. I ‘d declare that many of us site visitors actually are quite fortunate to exist in a good site with so many brilliant individuals with very beneficial ideas. I feel very privileged to have encountered your weblog and look forward to so many more pleasurable moments reading here. Thanks once more for all the details.

  29. I together with my buddies appeared to be reading through the excellent procedures on the blog and instantly developed an awful feeling I had not expressed respect to the web blog owner for those tips. The guys happened to be so excited to study them and already have without a doubt been loving these things. Thanks for getting very considerate and also for figuring out varieties of nice useful guides most people are really desperate to be informed on. My sincere regret for not expressing appreciation to you sooner.

  30. My husband and i got very excited when Raymond could finish up his reports through your ideas he acquired from your very own web pages. It is now and again perplexing just to always be giving out solutions which often many people have been selling. We see we’ve got the writer to appreciate for this. The entire explanations you’ve made, the straightforward web site menu, the relationships your site make it easier to create – it’s got everything great, and it is helping our son and our family reason why this situation is exciting, and that’s pretty indispensable. Thanks for the whole lot!

  31. I definitely wanted to write a small remark to be able to express gratitude to you for those awesome ideas you are showing on this site. My particularly long internet lookup has at the end been honored with awesome details to share with my friends. I ‘d declare that most of us website visitors actually are truly lucky to dwell in a notable site with so many outstanding people with insightful solutions. I feel truly lucky to have come across your entire site and look forward to really more brilliant moments reading here. Thanks a lot once again for all the details.

  32. I not to mention my guys have already been checking the excellent pointers on your site and then at once got a terrible feeling I never thanked you for those techniques. My guys became totally stimulated to see all of them and now have without a doubt been taking pleasure in those things. We appreciate you genuinely considerably helpful as well as for settling on certain important themes most people are really eager to understand about. My honest apologies for not saying thanks to earlier.

  33. I must show some appreciation to this writer for bailing me out of this incident. As a result of checking through the search engines and meeting tips which were not productive, I was thinking my life was over. Existing without the presence of solutions to the problems you’ve resolved through your good report is a crucial case, and the kind which may have in a negative way damaged my entire career if I had not encountered your site. The talents and kindness in taking care of all things was important. I don’t know what I would have done if I had not encountered such a point like this. I’m able to at this moment relish my future. Thanks for your time very much for the professional and sensible help. I won’t hesitate to endorse your blog to anybody who should have direction about this subject matter.

  34. My husband and i felt relieved when Edward could conclude his inquiry through your ideas he acquired in your site. It’s not at all simplistic just to choose to be handing out guidelines which usually most people may have been making money from. And we all know we now have the writer to give thanks to for that. Most of the illustrations you made, the straightforward website menu, the friendships you can help to promote – it’s mostly astonishing, and it’s aiding our son and our family consider that this idea is interesting, and that is very pressing. Many thanks for all!

  35. My wife and i got really ecstatic when Albert managed to finish off his web research from your precious recommendations he made out of your blog. It’s not at all simplistic just to find yourself making a gift of secrets that many many people could have been trying to sell. Therefore we recognize we have the website owner to be grateful to for this. All of the explanations you have made, the straightforward blog menu, the relationships you aid to promote – it’s got everything remarkable, and it is facilitating our son in addition to our family know that this concept is enjoyable, which is certainly tremendously indispensable. Many thanks for the whole thing!

  36. Thank you for your own efforts on this web page. Debby delights in getting into investigation and it’s easy to see why. I know all concerning the lively ways you provide insightful thoughts through your website and therefore boost response from other people on the area while my child has been discovering a lot. Enjoy the remaining portion of the year. You are always conducting a powerful job.

  37. Thanks for each of your efforts on this website. My niece really likes managing internet research and it’s obvious why. I hear all about the lively tactic you offer very important solutions through the website and as well as cause contribution from some other people about this idea so our princess is really studying a whole lot. Take pleasure in the remaining portion of the new year. You have been performing a fantastic job.

  38. I would like to express thanks to this writer for bailing me out of this particular dilemma. After exploring throughout the world wide web and coming across methods that were not beneficial, I thought my life was over. Being alive without the presence of solutions to the issues you’ve solved through your good site is a crucial case, and the kind that might have in a wrong way affected my entire career if I hadn’t discovered the website. Your own personal natural talent and kindness in controlling the whole lot was invaluable. I’m not sure what I would’ve done if I hadn’t encountered such a point like this. I am able to at this moment look forward to my future. Thanks so much for this professional and result oriented guide. I won’t be reluctant to suggest your web site to anybody who would need support on this subject.

  39. Thanks for all your valuable work on this website. Ellie really loves getting into investigation and it is simple to grasp why. I notice all relating to the lively tactic you produce insightful tips and tricks on the web site and as well as inspire contribution from people on the subject matter plus our favorite child is without question becoming educated a whole lot. Enjoy the rest of the new year. You’re conducting a first class job.

Leave a Reply

Your email address will not be published. Required fields are marked *