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.