SQL RESULT CACHE

最新推荐文章于 2025-02-08 17:18:34 发布

henrybai

最新推荐文章于 2025-02-08 17:18:34 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE11G 文章标签： sql cache integer dependencies access table

本文链接：https://blog.youkuaiyun.com/henrybai/article/details/6091755

ORACLE11G 专栏收录该内容

96 篇文章

订阅专栏

本文通过实例演示了如何使用SQLRESULTCACHE特性来提高查询效率，包括其限制及使用技巧。

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

SQL RESULT CACHE in a word is the implementation in the memory of Materilarized view.

But it can not be applied to sys or system objects.

see the example.

conn / as sysdba

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select * from dba_objects;

表已创建。

SQL> set autot on
SQL> set timing on
SQL> select count(*) from t;

COUNT(*)
----------
     72102

已用时间: 00: 00: 00.34

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 | SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1030 consistent gets
       1027 physical reads
          0 redo size
        425 bytes sent via SQL*Net to client
        415 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> select /*+ result_cache */ count(*) from t;

COUNT(*)
----------
     72102

已用时间: 00: 00: 00.25

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 | SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1032 consistent gets
       1027 physical reads
          0 redo size
        425 bytes sent via SQL*Net to client
        415 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> /

COUNT(*)
----------
     72102

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 | SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1032 consistent gets
          0 physical reads
          0 redo size
        425 bytes sent via SQL*Net to client
        415 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      CHOOSE
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

SQL> show parameter result

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 2112K
result_cache_mode                    string      AUTO
result_cache_remote_expiration       integer     0
SQL> select /*+ result_cache */ object_name from t where object_name = 'DUAL';

OBJECT_NAME
--------------------------------------------------------------------------------
DUAL
DUAL

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT |      |
|* 1 | TABLE ACCESS FULL| T    |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='DUAL')

Note
-----
   - rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1033 consistent gets
          0 physical reads
          0 redo size
        468 bytes sent via SQL*Net to client
        415 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          2 rows processed

SQL> select /*+ result_cache */ count(*) from t;

COUNT(*)
----------
     72102

已用时间: 00: 00: 00.06

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 | SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1032 consistent gets
          0 physical reads
          0 redo size
        425 bytes sent via SQL*Net to client
        415 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> create user henry identified by henry;

用户已创建。

已用时间: 00: 00: 00.28
SQL> grant dba to henry;

授权成功。

已用时间: 00: 00: 00.10
SQL> conn henry/henry
已连接。
SQL> create table t as select * from dba_objects;

表已创建。

已用时间: 00: 00: 02.20
SQL> set autot on
SQL> select /*+ result_cache */ count(*) from t;

COUNT(*)
----------
     72104

已用时间: 00: 00: 00.40

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

----------------------------------------------------------
| Id | Operation           | Name                       |
----------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |
|   1 | RESULT CACHE       | 6rdd2hscn9jy244f5v17vbw0hg |
|   2 |   SORT AGGREGATE    |                            |
|   3 |    TABLE ACCESS FULL| T                          |
----------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; dependencies=(HENRY.T); attributes=(single-row); name="select /*+ result_ca

Note
-----
   - rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1031 consistent gets
       1028 physical reads
          0 redo size
        425 bytes sent via SQL*Net to client
        416 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> select /*+ result_cache */ count(*) from system.t;

COUNT(*)
----------
     72103

已用时间: 00: 00: 00.07

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   281   (1)| 00:00:04 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 72103 |   281   (1)| 00:00:04 |
-------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1032 consistent gets
          0 physical reads
          0 redo size
        425 bytes sent via SQL*Net to client
        416 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

Ssee the report:

SQL> execute dbms_result_cache.memory_report

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.01
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 = 100M bytes (100K blocks)
Maximum Result Size = 100M bytes (100K blocks)
[Memory]
Total Memory = 134936 bytes [0.043% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 125476 bytes [0.040% of the Shared Pool]
....... Overhead = 92708 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 28 blocks
........... Used Memory = 4 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 3 blocks
................... SQL     = 3 blocks (3 count)

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.26