- 浏览: 50852 次
- 性别:
- 来自: 北京
最新评论
Oracle Result Cache
- 博客分类:
- Oracle
result cache
1.结果集缓存,oracle 11g新功能,可以在服务器以及客户端缓存结果集,
服务器端可以通过参数
sql query result cache:存储sql查询结果集
PL/SQL Function Result Cache:用于存储PL/SQL函数结果集
2.result cache 相关参数设置
如果result_cache_mode 设置为MANUAL 时,需要通过手工指定来实现,hints 是result_cache
注意:参数设置为auto时,只有在结果集已经存在的情况下才会直接查询结果集,
如果结果集不存在,则不会自动创建
相关参数设置
SQL> select a.VALUE,a.NAME from v$parameter a where a.NAME like '%result%';
cache 的对象
SQL> select type,name from v$result_cache_objects;
TYPE NAME
---------- ------------------------------------------------------------------------------------------------------------
--------------
Dependency SCOTT.DEPT
Dependency SCOTT.EMP
Result select /*+ result_cache */ empno,ename,dname from scott.emp e,scott.dept d where d.deptno = e.deptno
Result select /*+ result_cache */ empno,ename from scott.emp
Result select /*+ result_cache */ * from scott.emp
Result select /*+ result_cache */ count(1) from scott.emp
3.测试结果集
SQL> select /*+ result_cache */ empno,ename from scott.emp;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | dhf4gu6spq6tjbu3f8pu4y3652 | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(SCOTT.EMP); name="select /*+ result_cache */ empno,ename from scott.emp"
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
再次查询
SQL> select /*+ result_cache */ empno,ename from scott.emp;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | dhf4gu6spq6tjbu3f8pu4y3652 | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(SCOTT.EMP); name="select /*+ result_cache */ empno,ename from scott.emp"
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
其逻辑读降为0,即直接从缓冲结果集获取数据
发表评论
-
awr
2012-01-17 16:02 1175Oracle AWR 手动配置 1.查看当前的AWR保存策略s ... -
Oracle 登录认证相关
2011-11-30 14:11 13041.主要几个配置文件1.sql ... -
Oracle 数据库搬迁测试
2011-11-24 09:36 1100Oracle 数据库搬迁测试 测试目的:将物理文件从E盘目录移 ... -
Oracle logMiner
2011-11-21 20:37 944-- oracle 10g 10.2.0.1.0 ... -
Oracle 表访问方式
2011-11-17 12:12 1005SQL> set autotrace traceonly ... -
listener.ora、sqlnet.ora、tnsnames.ora
2011-11-10 11:20 874三个配置文件 listener.ora、sqlnet.ora、 ... -
Oracle cursor
2011-11-01 11:45 2490一 概念 游标是 ... -
Oracle 认证
2011-10-17 14:23 131.主要几个配置文件1.sql ... -
Oracle 命令大全
2011-09-29 09:33 840System Operateion Sql ... -
ORACLE数据库优化设计方案
2011-09-26 14:22 657对于ORACLE数据库的数据 ... -
oracle 分区管理
2011-09-19 10:47 1078oracle分区表管理2009-08-30 12:29作用:将 ... -
Oracle over
2011-09-01 10:14 858Oracle over函数 ... -
Oracle 号段
2011-09-01 09:42 8611.用lead 和lag 可以获取相邻号段的值 SELECT ... -
oracle job
2011-08-08 20:21 895定义oracle job DECLARE V_J ... -
oracle 日期大全
2011-07-05 16:05 47751:取得当前日期是本月的第几周 SQL> select ... -
EXPIMP详解
2011-06-30 11:22 670exp将数据库内的各对象以二进制方式下载成dmp文件, ... -
oracle 手动建库
2011-06-23 14:42 885自己手动创建的一个过程,贴出来以备查看 1.建立ora ... -
ORACLE 中更改客户端日期显示方式
2011-06-23 14:27 882如,按 yyyy-mm-dd 显示如下 ALTER SESS ... -
Oracle 数据类型及存储方式
2011-04-12 15:36 905Oracle 数据类型及存储方式 概述 通过实例,全面而深入的 ... -
ORA-01092 数据库打不开的问题原因(数据库升级不成功的问题)
2011-04-11 16:13 737Oracle10.2.0.1到10.2.0.4升级,升级完成后 ...
相关推荐
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 ...
Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, ...
Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, ...
Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, ...
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的...
上海 Oracle 用户组 -- SHOUG -- ShangHai Oracle Users Group http://www.shoug.info/11g
The PL/SQL features for 11g Release 1 (11.1) are: ■ Enhancements to Regular Expression Built-In SQL Functions ■ SIMPLE_INTEGER, SIMPLE...■ Result Caches in Oracle RAC Environment Are No Longer Private
#hibernate.connection.driver_class oracle.jdbc.driver.OracleDriver #hibernate.connection.username ora #hibernate.connection.password ora #hibernate.connection.url jdbc:oracle:thin:@localhost:1521:orcl...
- 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 ...
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...