`

SQL RESULT CACHE

 
阅读更多

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

分享到：
| fread没有读完整个文件
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    SHOUG文档分享-11g性能优化新技术-SQL-Query-Result-Cache-SHOUG成员罗敏1

    SQL Query Result Cache是Oracle 11g引入的一种性能优化新技术，旨在提高数据库系统的查询效率，减少不必要的计算和I/O操作。这一特性通过缓存SQL查询的结果来避免重复执行相同的查询，从而节省系统资源。 7.1 ...

    用缓存改善性能 页PPT学习教案.pptx

    总的来说，本课程旨在使学员掌握如何利用缓存技术优化SQL查询性能，包括理解结果缓存的工作原理，调整缓存大小，以及如何通过DBMS_RESULT_CACHE包和PL/SQL函数实现查询缓存。通过学习这些内容，学员能够有效地提高...

    High Performance PL/SQL from Steven Feuerstein

    - 使用`PRAGMA RESULT_CACHE`来声明函数结果可被缓存。 - 使用`RESULT_CACHE`提示语句在函数内部标记结果缓存。 - **注意事项**：确保被缓存的函数不依赖于会变动的状态（如当前日期等）。 ##### 2. BULK COLLECT...

    oracle-pl-sql-programming-5th-edition

    including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, sequences in PL/SQL expressions, supertype invocation ...

    SQL语句映射文件

    ### SQL语句映射文件详解 #### 一、概述 SQL映射文件是MyBatis框架的核心组件之一，主要用于存放数据库操作的SQL语句。它不仅简化了开发过程中的SQL编写工作，还提供了灵活的数据访问层实现方式。通过将SQL语句与...

    Oracle11g中一种提高查询速度的新技术.pdf

    Server Result Cache 技术在 Oracle 11g 中首次引入，它扩展了之前版本的Share Pool功能，Share Pool允许SQL解析结果在不同session间共享，减少了解析开销。而Server Result Cache更进一步，它直接缓存查询执行后的...

    SAP_HANA_SQL_Script_Reference_en.pdf

    文件还提到了过程结果缓存（Procedure Result Cache），这是SAP HANA提供的一个特性，用于优化存储过程的执行速度，通过缓存结果来提高性能。 用户自定义函数（User-Defined Function）允许用户创建自己的函数来...

    Mybatis-03 SQL映射文件

    MyBatis也提供了缓存机制，可以在`<mapper>`标签中启用二级缓存，通过`cache`标签进行配置，以提高数据访问性能。 总之，MyBatis的SQL映射文件是实现数据库操作的核心部分，通过灵活的配置，可以满足各种复杂的...

    微软内部资料-SQL性能优化2

    Assuming the page reference does not result in an access violation, a page fault can be either hard or soft. A hard page fault results in a read from disk, either a page file or memory-mapped file. A...

    1Z0-146 真题

    SQL> ALTER SYSTEM SET result_cache_max_size = 1056k SCOPE = BOTH; ``` **问题：**在这种情况下哪个陈述是正确的？ - A. 分配了1056KB用于结果缓存，并启用了结果缓存。 - B. 分配了1056KB用于结果缓存，但结果...

    Mysql利用profiles来查看SQL语句执行计划.doc

    - **storing result in query cache**：将结果存储在查询缓存中（如果适用）。 - **logging slow query**：记录慢查询日志。 - **cleaning up**：清理过程。 #### 使用 `SHOW PROFILE` 命令的高级选项 - **类型...

    spark sql优化.doc

    在示例代码中，`result.cache()` 将 SQL 查询的结果缓存，以供后续使用。 #### 1.2 Kryo 序列化与缓存级别 默认情况下，Spark 使用 Java 的序列化机制，但其效率较低。Kryo 序列化库提供了更快的序列化和反序列化...

    Go-基于Go的sql包AWQL驱动器

    这可以通过实现缓存层或者利用Go的缓存库（如`github.com/patrickmn/go-cache`）来完成。 8. **安全性**： 使用预编译语句（`Prepare`和`ExecContext`）防止SQL注入攻击，同时确保敏感数据（如密码）在传输时被...

    一次SQL Tuning引出来的not in , not exists 语句的N种写法2

    此外，随着数据库技术的发展，比如Oracle 11g引入的新特性，如服务器结果缓存（Server Result Cache）和自动内存管理等，这些工具和技术可以进一步优化查询性能。服务器结果缓存允许数据库存储查询结果，避免重复...

    Oracle 11g体系-全面详解

    - **Result Cache**: 存储服务器端的SQL结果集及PL/SQL函数返回值。 - **User Global Area (UGA)**: 在共享服务器模式下，如果没有配置large pool，则UGA属于SGA的Shared Pool;在专用连接模式时，UGA属于PGA。 - *...

Magicbox
Global site tag (gtag.js) - Google Analytics