ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DDL,DML操作对结果缓存的影响

DDL,DML操作对结果缓存的影响

原创 Linux操作系统 作者:杨奇龙 时间:2011-07-13 21:40:16 0 删除 编辑

一 DDL 语句对结果缓存的影响。
清理实验环境,使用hint构造结果缓存,cache_id 为93qg9pxgyrhd35bxgp9ay1mvqw
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.00
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
已用时间:  00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
   name="select /*+ result_cache */ object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        753  consistent gets
          0  physical reads
          0  redo size
        496  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
再次查询时,使用到上次缓存的结果集。
yang@rac1>select object_type,count(*) from yangobj group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; type=AUTO; dependencies=(YANG.YANGOBJ); parameters=(nls);
   name="select object_type,count(*) from yangobj group by object_type
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        496  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

已用时间:  00: 00: 00.06
对表yangobj进行DDL操作,通过视图v$result_cache_objects查看结果缓存的status。
yang@rac1>ALTER TABLE yangobj MODIFY OBJECT_ID NOT NULL;
表已更改。
已用时间:  00: 00: 03.03
yang@rac1>set autotrace off
yang@rac1>select status,cache_id from v$result_cache_objects;
STATUS    CACHE_ID
--------- ---------------------------------------------------
Published YANG.YANGOBJ
Invalid   93qg9pxgyrhd35bxgp9ay1mvqw   --不可用了,
已用时间:  00: 00: 00.02
yang@rac1>set autotrace on
yang@rac1>select object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
已用时间:  00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
   name="select /*+ result_cache */ object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
        192  recursive calls
          0  db block gets
        776  consistent gets
          0  physical reads
          0  redo size
        496  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed
yang@rac1>set autotrace off
yang@rac1>select status,cache_id from v$result_cache_objects;
STATUS    CACHE_ID
--------- ---------------------------------------------------------------------------------------------
Published YANG.YANGOBJ
Invalid   93qg9pxgyrhd35bxgp9ay1mvqw  --老的结果缓存
已用时间:  00: 00: 00.01
yang@rac1>
二 DML 操作对结果缓存的影响。
清理缓存缓存。
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
yang@rac1>alter system set result_cache_mode=manual;
系统已更改。
yang@rac1>exec 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  = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 9460 bytes [0.004% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL 过程已成功完成。
yang@rac1>alter system flush shared_pool;
系统已更改。
构造缓存
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
   name="select /*+ result_cache */ object_type,count(*) from yangobj group by object_type"
统计信息
----------------------------------------------------------
        246  recursive calls
          0  db block gets
        781  consistent gets
          0  physical reads
          0  redo size
        496  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
   name="select /*+ result_cache */object_type,count(*) from yangobj group by obje
ct_type"
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        496  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
yang@rac1>set autotrace off
yang@rac1>select name,cache_id,cache_key from v$result_cache_objects;
NAME                                CACHE_ID
----------------------------------- -----------------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
YANG.YANGOBJ                        YANG.YANGOBJ
YANG.YANGOBJ
select /*+ result_cache */ object_t 93qg9pxgyrhd35bxgp9ay1mvqw
ype,count(*) from yangobj group by
object_type
fpn1dsgmvbq9cbhu4vs188mqr3
yang@rac1>select name,status,cache_id,cache_key from v$result_cache_objects;

NAME                                STATUS    CACHE_ID
----------------------------------- --------- -------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
YANG.YANGOBJ                        Published YANG.YANGOBJ
YANG.YANGOBJ
select /*+ result_cache */ object_t Published 93qg9pxgyrhd35bxgp9ay1mvqw
ype,count(*) from yangobj group by
object_type
fpn1dsgmvbq9cbhu4vs188mqr3
对表yangobj进行dml操作并不提交,
yang@rac1>delete from yangobj  where rownum<11;

已删除10行。
yang@rac1>select name,status,cache_id,cache_key from v$result_cache_objects;

NAME                                STATUS    CACHE_ID
----------------------------------- --------- -------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
YANG.YANGOBJ                        Published YANG.YANGOBJ
YANG.YANGOBJ
select /*+ result_cache */ object_t Published 93qg9pxgyrhd35bxgp9ay1mvqw
ype,count(*) from yangobj group by
object_type
fpn1dsgmvbq9cbhu4vs188mqr3
yang@rac1>set autotrace off
yang@rac1>set autotrace on
再次查询时候,结果缓存不可用了。
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65517
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select /*+ result_cache */object_type,count(*) from yangobj group by obje
ct_type"
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        753  consistent gets  --产生了一致性读。
          0  physical reads
          0  redo size
        496  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
回滚之后,结果缓存可用。
yang@rac1>rollback;
回退已完成。
yang@rac1>set autotrace off
yang@rac1>select name,status,cache_id,cache_key from v$result_cache_objects;
NAME                                STATUS    CACHE_ID
----------------------------------- --------- -------------------------------------------------------------
CACHE_KEY
---------------------------------------------------------------------------------------------
YANG.YANGOBJ                        Published YANG.YANGOBJ
YANG.YANGOBJ

select /*+ result_cache */ object_t Published 93qg9pxgyrhd35bxgp9ay1mvqw
ype,count(*) from yangobj group by
object_type                        
fpn1dsgmvbq9cbhu4vs188mqr3
yang@rac1>set autotrace on
yang@rac1>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                    65527
执行计划
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   1 |  RESULT CACHE       | 93qg9pxgyrhd35bxgp9ay1mvqw |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |     1 |     6 |   209   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| YANGOBJ                    | 65536 |   384K|   206   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select /*+ result_cache */object_type,count(*) from yangobj group by obje
ct_type"
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        496  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
对表的DML操作如果提交,是会造成结果缓存的失效的,如果回滚,结果缓存依然可用。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-702130/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7697979