220.127.116.11 is not just a patchset of 11gR2. It is more like 11gR3. Last month, I presented my favorite top 10 new features for DBAs. However, the new table annotation feature deserves special attention.
After a new release of Oracle, it is always worthwhile taking a look into DBA_TABLES (or USER_TABLES). Now, the last new column is called RESULT_CACHE. It has for now two possible values: DEFAULT and FORCE.
FORCE means that the table is annotated. That is, if all table names in a given SQL query have this setting, then the result set returned by the query is always considered for caching unless the NO_RESULT_CACHE hint is specified for the query. If one or more tables named in the query are set to DEFAULT, then the effective table annotation for that query is DEFAULT. Which means the result set will not be cached.
Let us see how it works by first creating a table with 15000 rows and run a full table scan query:
If you want to flush the result cache, just run: exec DBMS_RESULT_CACHE.FLUSH;
Here are the objects related to the topic, it is worth checking them:
Caching is not guaranteed by Oracle even if all tables in the query are annotated. The actual caching depends on internal restrictions for client and server cache, query cache worthiness and space available in the cache. Try to run in a row for instance sql with SYSDATE in it and have a look:
Queries that are not Cached:
• Remote objects
• Complex types in the select list
• Snapshot-based or flashback queries
• Queries executed in a serializable, read-only transaction, or inside a flashback session
• Queries that have PL/SQL functions in them
• Queries that have VPD policies enabled on the tables
How many records can Oracle store? All depends on two init.ora parameters: RESULT_CACHE_MAX_SIZE & RESULT_CACHE_MAX_RESULT:
RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory that can be used by the result cache. Values of this parameter greater than 0 are rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the feature is disabled. The default value is derived from the values of SHARED_POOL_SIZE, SGA_TARGET, and MEMORY_TARGET. By default, the cache takes 0.025% of the memory_target parameter’s value. However, the max allocation cannot exceed 75% of the size of the shared pool. With my current memory_target of 384M, the result cache max size does not even reach 1M. For production systems, I would recommend that you set it to at least 32M.
RESULT_CACHE_MAX_RESULT specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use. The default value is 5% of the RESULT_CACHE_MAX_SIZE. This should be OK in most cases.
And finally, look at the execution plan when annotated tables are involved:
A good question is: so which tables from the database should I annotate? I would probably go through few AWR reports and try to find the repetitive queries run at least 100s of times. Then force annotate the tables used in the queries. And test first plus follow on regular basis what comes from V$RESULT_CACHE_OBJECTS! With Oracle at times you get the opposite of what is expected.
P.S. I often see in AWR reports how WebLogic runs 1000s of times per hour “select * from dual”. In a recent report, I saw the DUAL table as number one segment in terms of logical reads. You can annotate even system tables:
SQL> alter table dual result_cache (mode force);