Complete Reference To 11g New Feature : SQL Query Result Cache

Oracle   new technology, Oracle 11g新特性, result cache  

2 comments to Complete Reference To 11g New Feature : SQL Query Result Cache

• admin

  September 6, 2010 at 1:29 PM · Reply

  Result Cache Can Not Be Enabled

  Applies to:

  Oracle Server – Enterprise Edition – Version: 11.1.0.6.0
  This problem can occur on any platform.

  Symptoms

  Result Cache does not work. When changing the result_cache_max_size parameter and restarting the
  database, the value of the parameter is still 0 which means that Result Cache is disabled:

  sys@abc> show parameter result_cache_max_size

  NAME TYPE VALUE
  ———————————— ———– ——————————
  result_cache_max_size big integer 0

  However the size in the spfile is set to a non-zero value:

  sys@abc> !strings spfileELF.ora |grep result_cache
  *.result_cache_max_size=5242880

  Running dbms_result_cache.status() shows that the Result Cache status is BYPASS while it should have been ENABLED:

  sys@elf> SELECT dbms_result_cache.status() FROM dual; 
  DBMS_RESULT_CACHE.STATUS() 
  —————————————————————————————————- 
  —————————————- 
  BYPASS

  Changes

  Using SGA_TARGET:

  *.sga_target=584M
  *.db_cache_size=0
  *.shared_pool_size=0
  *.large_pool_size=0
  *.java_pool_size=0

  Cause

  A minimum value was not set for the shared_pool_size. The Result Cache memory area is located in the Shared Pool so, the value of result_cache_max_size is consumed from the Shared Pool size.

  Solution

  Setting a minimum value for shared_pool_size resolves the problem.

  *.result_cache_max_size=5242880
  *.shared_pool_size=7000000

  SQL> show parameter result_cache_max_size

  NAME TYPE VALUE
  ———————————— ———– ——————————
  result_cache_max_size big integer 5M

  SQL> SELECT dbms_result_cache.status() FROM dual;

  DBMS_RESULT_CACHE.STATUS()
  ——————————————————————————–
  ENABLED 

• admin

  September 6, 2010 at 1:33 PM · Reply

  What v$ table tracks usage of the new Result Cache feature In 11g

  Applies to:

  Oracle Server – Enterprise Edition – Version: 11.1.0.7
  Information in this document applies to any platform.
  11g

  Goal

  What database tables do track the 11g client result cache objects ?

  Solution

  There are 2 v$ tables which may be interesting:
    
  client_result_cache_stats$ :tracks cache statistics
    
  v$result_cache_objects: tracks objets used in the result chache

  You can use the following  query below to get a list of all sql statements currently used in the result cache : 
    
  [ Not the following Output is generated by running cdemoqc.c from $OH/rdbms/demo directory ]

  SQL> select name , type, ROW_COUNT from v$result_cache_objects;
  NAME
  ——————————————————————————–
  TYPE ROW_COUNT
  ———- ———-
  OCITEST.QCTABLE
  Dependency 0
  SELECT /*+ result_cache */ empno, ename, sal FROM qctable
  Result 1000

  ---

   

Copyright © 2012 Oracle Clinic – Maclean Liu的个人技术博客 - All Rights Reserved
Powered by WordPress & Atahualpa