Dontcheff

Table Annotations in 11.2.0.2

In DBA, Oracle database, PL/SQL, SQL on April 22, 2011 at 10:12

11.2.0.2 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);

Table altered.

Advertisements
  1. Is it supported to annotate dual table (you are changing the SYS schema) ?
    I think, the better option for Weblogic is to set “begin null; end;” as a command for keeping the sessions alive..

  2. “Oracle Database Performance Tuning Guide,
    11g Release 2(11.2) E16638-06”, dtd July 2011
    Page 7-62:
    You cannot cache results when the following objects or functions are in a query:
    ■ Temporary tables and tables in the SYS or SYSTEM schemas

    • The DUAL table is owned by SYS and is placed in the SYSTEM tablespace. And yes, the documentation says what Ken says. But the reallity is that the DUAL table can be annotated and the result cache is used when the table is involved in queries:

      PLAN_TABLE_OUTPUT
      ——————————————————————————–

      | 0 | SELECT STATEMENT | | 1 | 2 | 2
      (0)| 00:00:01 |

      | 1 | RESULT CACHE | 9p1ghjb9czx4w7vqtuxk5zudg6 | | |
      | |

      | 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2
      (0)| 00:00:01 |

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: