Chinaunix首页 | 论坛 | 博客

Roger's Oracle bloglovewifelovelif.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

huanhuanlove

  • 博客访问： 82228
  • 博文数量： 12
  • 博客积分： 265
  • 博客等级： 二等列兵
  • 技术积分： 165
  • 用 户 组： 普通用户
  • 注册时间： 2010-03-02 01:30
个人简介

Roger,6年oracle dba经验，专注于Oracle在Linux/Unix下的管理,诊断,调优以及高可用,擅长oracle数据备份恢复,诊断,熟悉Rac/dataguard/goldengate等,提供Oracle技术支持及咨询服务！ 个人技术站点：http://www.killdb.com

文章分类

全部博文（12）

文章存档

2011年（12）

我的朋友
最近访客
推荐博文
相关博文
11g 新特性之--query result cache(3)

分类： Oracle

2011-08-21 18:10:13

SQL> show user
USER is "ROGER"
SQL> create table ht03 as select * from ht02 where rownum <10000;

Table created.

Elapsed: 00:00:03.51
SQL> desc ht03
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)
 
SQL> create table ht04(OWNER VARCHAR2(30),OBJECT_ID NUMBER,OBJECT_NAME VARCHAR2(128))
  2  RESULT_CACHE (MODE FORCE);

Table created.

Elapsed: 00:00:00.14
SQL> insert into /*+append */ ht04  select * from ht03;

9999 rows created.

Elapsed: 00:00:00.32
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> create index ht03_idx on ht03(object_id);

Index created.

Elapsed: 00:00:00.32
SQL>  create index ht04_idx on ht04(object_id);

Index created.

Elapsed: 00:00:00.10
SQL> analyze table ht03 compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Elapsed: 00:00:00.73
SQL> analyze table ht04 compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Elapsed: 00:00:00.18
SQL>
SQL> set autot traceonly
SQL> set lines 160
SQL> select * from ht03 where object_id=999;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1330547204

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HT03     |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | HT03_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=999)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        570  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 * from ht04 where object_id=999;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2782040647

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | 1wsv07hr29687c877123g0cumt |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT04                       |     1 |    36 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | HT04_IDX                   |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=999)

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

   1 - column-count=3; dependencies=(ROGER.HT04); attributes=(ordered); name="select * from ht04 where object_id=999"


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        566  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>
SQL> show parameter result

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_client_result_cache_bypass          boolean     FALSE
_result_cache_auto_execution_thresho integer     1
ld
_result_cache_auto_size_threshold    integer     100
_result_cache_auto_time_distance     integer     300
_result_cache_auto_time_threshold    integer     1000
_result_cache_block_size             integer     1024
_result_cache_global                 boolean     TRUE
_result_cache_timeout                integer     10
_xsolapi_sql_result_set_cache_size   integer     32
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 960K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
SQL>
SQL> select /*+ RESULT_CACHE */ * from ht03 where object_id=999;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1330547204

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | f42hd8bp1h26hbdqqs6bz47m3z |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT03                       |     1 |    36 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | HT03_IDX                   |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=999)

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

   1 - column-count=3; dependencies=(ROGER.HT03); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht03 where object_id=999"


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        566  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
         

从上面的实验，我们可以看出11gR2 比11gR1 多了其中一点就是这里，那就是在create table的时候，
我们可以指定是否对该表启用query cache特性，create table的语法如下：
CREATE|ALTER TABLE [.]

... [RESULT_CACHE (MODE {FORCE|DEFAULT})]

当然，既然create table有的新的语法，那么必然同时也会增加alter table的语法了，请看测试。
SQL> set autot off
SQL> alter table ht03 RESULT_CACHE(mode force);

Table altered.

Elapsed: 00:00:00.33
SQL> set autot traceonly
SQL> select * from ht03 where object_id=999;

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1330547204

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | f42hd8bp1h26hbdqqs6bz47m3z |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT03                       |     1 |    36 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | HT03_IDX                   |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=999)

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

   1 - column-count=3; dependencies=(ROGER.HT03); attributes=(ordered); name="select * from ht03 where object_id=999"


Statistics
----------------------------------------------------------
        178  recursive calls
          0  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        566  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

同时在11gR2中，oracle在dba_tables和all_tables中增加一个字段，RESULT_CACHE。
SQL> select owner,table_name,RESULT_CACHE from dba_tables where owner='ROGER';

OWNER                          TABLE_NAME                     RESULT_
------------------------------ ------------------------------ -------
ROGER                          HT04                           FORCE
ROGER                          HT03                           FORCE
ROGER                          HT02                           DEFAULT
ROGER                          HT01                           DEFAULT

Elapsed: 00:00:02.75
SQL>

关于字段RESULT_CACHE其中有3个属性，分别为DEFAULT，FORCE和MANUAL，大家可以参考11.2的官方文档。
这里有点需要说明的是，必然当表结构或定义发变化了，那么query cache 缓存的信息都将被清除，如下例子。
SQL> set autot traceonly
SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | b500gqatyy0zq32az39dhnk3fb |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT01                       |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_HT01_ID                |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=100)

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

   1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"


Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
         10  consistent gets
          2  physical reads
          0  redo size
        560  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> desc ht01
 Name                                                                                      Null?    Type
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 OWNER                                                                                              VARCHAR2(30)
 OBJECT_NAME                                                                                        VARCHAR2(128)
 OBJECT_ID                                                                                          NUMBER

SQL> alter table ht01 modify (owner VARCHAR2(40));

Table altered.

Elapsed: 00:00:00.22
SQL>  select /*+ RESULT_CACHE */ * from ht01 where object_id=100;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | b500gqatyy0zq32az39dhnk3fb |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT01                       |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_HT01_ID                |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=100)

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

   1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"


Statistics
----------------------------------------------------------
        178  recursive calls
          0  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        560  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | b500gqatyy0zq32az39dhnk3fb |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT01                       |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_HT01_ID                |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=100)

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

   1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        560  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 */ * from ht01 where object_id=100;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | b500gqatyy0zq32az39dhnk3fb |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT01                       |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_HT01_ID                |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=100)

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

   1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        560  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>

从上面的信息来看，我想已经完全可以说明问题了，如何有人说这还不能说明问题的话，那请看下面：
SQL> conn /as sysdba
Connected.
SQL> alter session set events 'immediate trace name heapdump level 2';

Session altered.

Elapsed: 00:00:04.38
SQL> @ gettrc.sql

TRACE_FILE_NAME
------------------------------------------------------------------------------------
/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_4106.trc

Elapsed: 00:00:00.82
SQL>
SQL> !
[oracle@roger ~]$ grep -i Result  /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_4106.trc
  Chunk 24bdecac sz=    32816    freeable  "Result Cache   "  ds=0x272758b4
  Chunk 24be6cdc sz=    32816    freeable  "Result Cache   "  ds=0x272758b4
  Chunk 24beed0c sz=    32816    recreate  "Result Cache   "  latch=(nil)
[oracle@roger ~]$
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump_addr 2 656890036;
Statement processed.
SQL> oradebug tracefile_name
/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_6683.trc
SQL>

[root@roger ~]# grep -i ht01 /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_6683.trc
24BE11A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
24BE15A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
24BE11A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
24BE15A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
[root@roger ~]#

下面我们修改表ht01的表结构，然后再次dump 看看结果如何。
SQL> set autot off
SQL> alter table ht01 modify (owner VARCHAR2(50));

Table altered.

Elapsed: 00:00:00.06
SQL> set autot traceonly
SQL>  select /*+ RESULT_CACHE */ * from ht01 where object_id=101;

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | br1m2nyfp7v9c5drfp1gn5xp92 |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT01                       |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_HT01_ID                |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=101)

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

   1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=101"


Statistics
----------------------------------------------------------
        178  recursive calls
          0  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        563  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                | br1m2nyfp7v9c5drfp1gn5xp92 |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT01                       |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_HT01_ID                |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=101)

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

   1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=101"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        563  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>
SQL>  oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump_addr 2 656890036;
Statement processed.
SQL> oradebug tracefile_name
/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_28227.trc
SQL>
[root@roger ~]# grep -i ht01 /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_28227.trc
24BE11A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
24BE15A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
24BE19A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]   
24BE11A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
24BE15A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
24BE19A0 6F726620 7468206D 77203130 65726568  [ from ht01 where]
[root@roger ~]#

从上面可以看出多了2条信息，24BE19A0。 从上面的实验来看，我们可以推断出oracle这里应该是这样管理的，
那就是即使表结构定义发生改变了，那么原来cache的信息仍然存在query cache中，当然，当cache不够用了，
也是会被清除掉的，至于说oracle这里是如何去判断如何不去选择旧的cache信息，那么我就不得而知了。
如果谁研究的更为透彻，记得告诉我，谢谢！

到最后，大家可能会想query cache的工作原理是什么？sql的结果集缓存超过多少或者说在使用了该特性
的情况下，如何通过算法去检索client所需要的信息呢？这些目前还都是未知数。

阅读(1556) | 评论(0) | 转发(0) |
0

上一篇：11g 新特性之–query result cache(2)

下一篇：ora-08102 核心bootstrap对象不一致

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6