11gr2 SQL result cache test notes
September 3, 2011 3 Comments
- SQL gets very good performance if the SQL is cached
- Transparent to app
- Low cost: only need several MB memory and no extra license
- Any DML will invalidate the SQL result caches (have table level option in 11gR2).
- Unstable SQL response time: app don’t like it
- OLTP favor: expensive query and few DML
- Concurrence/Scalability issues: only protected by 3 latches in 11gR2. Reference: Oracle 11G Result Cache in the Real World and 11GR2 Result Cache Scalability
- not suitable for busy OLTP db.
- I have another blog entry to go through Result Cache : Result cache in oracle 11g
- A very good ppt by Julian Dyke: Result Cache Internals
- If a session is building a result cache, the other session may wait 10 seconds (default) to timeout. It is controlled by parameter hidden “_result_cache_timeout”.