Result Cache Can Not Be Enabled

于 2017-09-08 11:28:30 发布
阅读量101 收藏
点赞数
文章标签： 数据库
介绍Oracle 11g中引入的结果缓存特性，该特性通过在共享池中缓存查询结果来提高频繁运行且变化不大的查询性能。文章详细探讨了结果缓存的好处、限制条件、配置方法及如何使用提示和表注释控制结果缓存。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

   Result Cache is a feature introduced in Oracle 11g which caches the results of a query in the shared pool.  It is ideal for a query that is used frequently and rarely changes.  Once the initial query has been cached, Oracle retrieves the result of the query from the cache, improving its performance. When a query executes, the database looks in the cache memory to determine whether the result exists in the cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.

Benefits of Using the Server Result Cache:

1. "The benefits of using the server result cache depend on the application. OLAP applications can benefit significantly from its use. Good candidates for caching are queries that access a high number of rows but return a small number, such as those in a data warehouse. For example, you can use advanced query rewrite with equivalences to create materialized views that materialize queries in the result cache instead of using tables."

2. As queries using result cache bypass buffer cache and the result is in the cache, there is less CPU consumption.

3. There is no physical I/O, as the result is available again in the cache.

Other SQL Query Result Cache Considerations

  •    Result cache is disabled for queries containing:
    •   1  Temporary or Dictionary tables
    •   2  Non-deterministic PL/SQL functions
    •   3  Sequence CURRVAL and NEXTVAL
    •   4  SQL functions CURRENT_DATE,SYSDATE,SYS_GUID, and so on
  •   DDL/DML on remote database does not expire cached results
  •   Flashback queries can be cached
  •   Result Cache does not automatically release memory
    •   1  It grows until maximum size is reached
    •   2  DBMS_RESULT_CACHE.FLUSH purges memory
  •   Bind variables
    •   1  Cached result is parameterized with variable values
    •   2  Cached results can only be found for the same variable values
  •   Cached result will not be build if:
    •    1  Query is build on a non-current version of data(read consistency enforcement)
    •    2  Current session has outstanding transaction on tables in query

Requirements for the Result Cache

Also enabling result cache does not guarantee that result of the query will be in the cache. There are certain requirements before result cache can be used:

  • Read Consistency Requirement

  • Query Parameter Requirements

  • Restrictions for Result Cache
    Results cannot be cached when the following objects or functions are in a query:

    • 1 Temporary tables

    • 2 External tables

    • 3 Tables in the SYS or SYSTEM schemas

    • 4 Sequence CURRVAL and NEXTVAL pseudo columns

    • 5 SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, and SYS_TIMESTAMP

      A   if Result Cache Can Not Be Enabled,first use index hints:

      [oracle@ORACLERAC2 ~]$ cd $ORACLE_HOME/dbs
      [oracle@ORACLERAC2 dbs]$ strings spfilePROD1.ora | grep result_cache
      *.result_cache_mode='MANUAL'
      [oracle@ORACLERAC2 dbs]$

SQL> show parameter  result_cache_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 4608K
SQL>  SELECT dbms_result_cache.status() FROM dual;  

DBMS_RESULT_CACHE.STATUS()
-----------------------------------------------
ENABLED
SQL> alter system set result_cache_max_size=15m;

System altered.
SQL> sho parameter  result_cache_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      MANUAL
SQL>  show parameter  result_cache_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 15M

SQL> SET SERVEROUTPUT ON
SQL>  EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 15M bytes (15K blocks)
Maximum Result Size = 768K bytes (768 blocks)
[Memory]
Total Memory = 340280 bytes [0.159% of the Shared Pool]
... Fixed Memory = 12144 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 328136 bytes [0.153% of the Shared Pool]
....... Overhead = 131528 bytes
....... Cache Memory = 192K bytes (192 blocks)
........... Unused Memory = 27 blocks
........... Used Memory = 165 blocks
............... Dependencies = 29 blocks (29 count)
............... Results = 136 blocks
................... SQL     = 117 blocks (117 count)
................... Invalid = 19 blocks (19 count)

PL/SQL procedure successfully completed.

No hints,No result cache:
SQL> SELECT  department_id, AVG(salary)
  2  FROM     hr.employees
  3  GROUP BY department_id;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    11 |    77 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed


hints and result cache:
SQL> SELECT   /*+ result_cache*/  department_id, AVG(salary)
  2  FROM     hr.employees
  3  GROUP BY department_id;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 7uc8hravqsvzc3u63m12dkt8pf |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT   /*+ result_cache*/  department_id, AVG(salary)
FROM     hr.employees
GROUP BY department_id"
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL>  select name,status from v$result_cache_objects;
……
SELECT   /*+ RESULT_CACHE */ department_id, AVG(salary)                                                                          Published
FROM     hr.employees
GROUP BY department_id
……

B    All results are stored in the result cache. If a query result is not in the cache, then the database executes the query and stores the result in the cache. Subsequent executions of the same statement, including the result cache hint, retrieve data from the cache.Sessions uses these results if possible. To exclude query results from the cache, you must use the /*+ NO_RESULT_CACHE */ query hint.
SQL>
SQL>  alter system set result_cache_mode= FORCE;
System altered.
SQL> sho parameter  result_cache_mode
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      FORCE
SQL>
SQL> SELECT    department_id, AVG(salary)
  2  FROM     hr.employees
  3  GROUP BY department_id;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 7uc8hravqsvzc3u63m12dkt8pf |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT    department_id, AVG(salary)
FROM     hr.employees
GROUP BY department_id"
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> SELECT   /*+ NO_RESULT_CACHE */  department_id, AVG(salary)
  2  FROM     hr.employees
  3  GROUP BY department_id;

12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    11 |    77 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

C You can use table annotations to control result caching. Table annotations are in effect only for the whole query, not for query segments. The primary benefit of these annotations is avoiding the necessity of adding result cache hints to queries at the application level.

A table annotation has a lower precedence than a SQL hint. Thus, you can override table and session settings by using hints at the query level. Permitted values for the RESULT_CACHE table annotation are as follows:

  • DEFAULT

    If at least one table in a query is set to DEFAULT, then result caching is not enabled at the table level for this query, unless the RESULT_CACHE_MODE initialization parameter is set to FORCE or the RESULT_CACHE hint is specified. This is the default value.

  • FORCE

    If all the tables of a query are marked as FORCE, then the query result is considered for caching. The table annotation FORCE takes precedence over the RESULT_CACHE_MODE parameter value of MANUAL set at the session level.

SQL> alter table hr.employees RESULT_CACHE (MODE DEFAULT);
Table altered.
SQL> SELECT   department_id, AVG(salary)
  2  FROM     hr.employees
  3  GROUP BY department_id;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 119216990
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    11 |    77 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed
SQL> alter table hr.employees RESULT_CACHE (MODE  FORCE);
Table altered.
SQL> SELECT   department_id, AVG(salary)
  2  FROM     hr.employees
  3  GROUP BY department_id;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 7uc8hravqsvzc3u63m12dkt8pf |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT   department_id, AVG(salary)
FROM     hr.employees
GROUP BY department_id"
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed
SQL> SELECT  /*+ NO_RESULT_CACHE */ department_id, AVG(salary)
  2  FROM     hr.employees
  3  GROUP BY department_id;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    11 |    77 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        853  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed




来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-2144686/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-2144686/

cppg67031
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值