`
wsql
  • 浏览: 11808917 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
文章分类
社区版块
存档分类
最新评论

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

分享到：
| The fuction of listagg.
评论
发表评论

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

相关推荐

    Oracle PL/SQL programming

    Take advantage of extensive code samples, from easy-to-follow examples to reusable packaged utilities, Optimize PL/SQL performance with features like the function result cache and Oracle utilities ...

    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 ...

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

    上海 Oracle 用户组 -- SHOUG -- ShangHai Oracle Users Group http://www.shoug.info/11g

    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 ...

    11g_plsql_user_guide_and_reference.pdf

    ■ PL/SQL Function Result Cache ■ Compound DML Triggers ■ More Control Over Triggers ■ Automatic Subprogram Inlining ■ PL/Scope ■ PL/SQL Hierarchical Profiler ■ PL/SQL Native Compiler Generates ...

    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性能优化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...

    mySQL数据库性能优化pdf.pdf

    针对面试经常问的sql优化问题,进行mySQL数据库性能优化

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

    SQL Server never asks for SIX locks directly, they are always the result of a conversion. For example, suppose a transaction scanned a page using an S lock and then subsequently decided to perform a ...

    MyBatis User Guide.rar

    MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and...

    ora分析脚本

    - bc: view contents of buffer cache - temp: view used space in temp tbs - asm: Show asm space/free space - space []: view used/free space in a given tbs - binds <sql_id> : display bind capture ...

    hibernate.properties

    ## to use the second-level cache with JTA, Hibernate must be able to obtain the JTA TransactionManager #hibernate.transaction.manager_lookup_class org.hibernate.transaction....

    mysql语言之SELECT

    Syntax: SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, ...

    MyBatis3_用户指南(附JavaDB实例)

    id, result元素.... 34 支持的JDBC类型....35 Constructor元素...35 Association元素...36 Collection元素.... 40 Discriminator元素......42 Cache元素.43 cache-ref元素..46 动态SQL（Dynamic SQL）.... 46 if元素...

    深入解析Oracle.DBA入门进阶与诊断案例

    6.2.1　Oracle 11g新特性：Result Cache　234 6.2.2　Shared Pool的设置说明　238 6.2.3　Oracle 9i子缓冲池的增强　243 6.2.4　Oracle 10g共享池管理的增强　245 6.2.5　了解X$KSMSP视图　247 6.2.6　Shared Pool的...

    Hibernate Reference Documentation3.1

    15.2. Narrowing the result set 15.3. Ordering the results 15.4. Associations 15.5. Dynamic association fetching 15.6. Example queries 15.7. Projections, aggregation and grouping 15.8. Detached queries...

    php.ini-development

    should be disabled, as enabling it may result in issues when generating XML ; documents, however this remains supported for backward compatibility reasons. ; Note that this directive does not control...

    DbfDotNet_version_1.0_Source

    When you want to manipulate or cache SQL data When you need to write highly procedural data manipulation routines When you want maximum speed Features Despite its small size DbfDotNet provides a ...

    前端-后端java的Util类的工具类

    │ │ │ frame-sourcefiles-org.apache.ibatis.executor.result.html │ │ │ frame-sourcefiles-org.apache.ibatis.executor.resultset.html │ │ │ frame-sourcefiles-org.apache.ibatis.executor.statement....

    ZendFramework中文文档

    4.3.3. Zend_Cache_Frontend_Function 4.3.3.1. Introduction 4.3.3.2. A可用的选项 4.3.3.3. 例子 4.3.4. Zend_Cache_Frontend_Class 4.3.4.1. Introduction 4.3.4.2. Available options 4.3.4.3. Examples ...

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