11g 新特性之--query result cache(3)
<P>SQL> show user<BR>USER is "ROGER"<BR>SQL> create table ht03 as select * from ht02 where rownum <10000;</P><P>Table created.</P>
<P>Elapsed: 00:00:03.51<BR>SQL> desc ht03<BR> Name Null? Type<BR> ----------------------------------------- -------- ----------------------------<BR> OWNER VARCHAR2(30)<BR> OBJECT_ID NUMBER<BR> OBJECT_NAME VARCHAR2(128)<BR> <BR>SQL> create table ht04(OWNER VARCHAR2(30),OBJECT_ID NUMBER,OBJECT_NAME VARCHAR2(128)) <BR> 2 RESULT_CACHE (MODE FORCE);</P>
<P>Table created.</P>
<P>Elapsed: 00:00:00.14<BR>SQL> insert into /*+append */ ht04 select * from ht03;</P>
<P>9999 rows created.</P>
<P>Elapsed: 00:00:00.32<BR>SQL> commit;</P>
<P>Commit complete.</P>
<P>Elapsed: 00:00:00.01<BR>SQL> <BR>SQL> create index ht03_idx on ht03(object_id);</P>
<P>Index created.</P>
<P>Elapsed: 00:00:00.32<BR>SQL> create index ht04_idx on ht04(object_id);</P>
<P>Index created.</P>
<P>Elapsed: 00:00:00.10<BR>SQL> analyze table ht03 compute statistics for table for all indexes for all indexed columns;</P>
<P>Table analyzed.</P>
<P>Elapsed: 00:00:00.73<BR>SQL> analyze table ht04 compute statistics for table for all indexes for all indexed columns;</P>
<P>Table analyzed.</P>
<P>Elapsed: 00:00:00.18<BR>SQL> <BR>SQL> set autot traceonly<BR>SQL> set lines 160<BR>SQL> select * from ht03 where object_id=999;</P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 1330547204</P>
<P>----------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>----------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |<BR>| 1 | TABLE ACCESS BY INDEX ROWID| HT03 | 1 | 36 | 2 (0)| 00:00:01 |<BR>|* 2 | INDEX RANGE SCAN | HT03_IDX | 1 | | 1 (0)| 00:00:01 |<BR>----------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 2 - access("OBJECT_ID"=999)</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 1 recursive calls<BR> 0 db block gets<BR> 4 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 570 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> select * from ht04 where object_id=999;</P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2782040647</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | 1wsv07hr29687c877123g0cumt | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT04 | 1 | 36 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | HT04_IDX | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=999)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT04); attributes=(ordered); name="select * from ht04 where object_id=999"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 1 recursive calls<BR> 0 db block gets<BR> 4 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 566 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> <BR>SQL> show parameter result</P>
<P>NAME TYPE VALUE<BR>------------------------------------ ----------- ------------------------------<BR>_client_result_cache_bypass boolean FALSE<BR>_result_cache_auto_execution_thresho integer 1<BR>ld<BR>_result_cache_auto_size_threshold integer 100<BR>_result_cache_auto_time_distance integer 300<BR>_result_cache_auto_time_threshold integer 1000<BR>_result_cache_block_size integer 1024<BR>_result_cache_global boolean TRUE<BR>_result_cache_timeout integer 10<BR>_xsolapi_sql_result_set_cache_size integer 32<BR>client_result_cache_lag big integer 3000<BR>client_result_cache_size big integer 0<BR>result_cache_max_result integer 5<BR>result_cache_max_size big integer 960K<BR>result_cache_mode string MANUAL<BR>result_cache_remote_expiration integer 0<BR>SQL> <BR>SQL> select /*+ RESULT_CACHE */ * from ht03 where object_id=999;</P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 1330547204</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | f42hd8bp1h26hbdqqs6bz47m3z | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT03 | 1 | 36 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | HT03_IDX | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=999)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT03); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht03 where object_id=999"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 1 recursive calls<BR> 0 db block gets<BR> 4 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 566 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed<BR> </P>
<P>从上面的实验,我们可以看出11gR2 比11gR1 多了其中一点就是这里,那就是在create table的时候,<BR>我们可以指定是否对该表启用query cache特性,create table的语法如下:<BR>CREATE|ALTER TABLE [<schema>.]<table> ... </P>
<P>当然,既然create table有的新的语法,那么必然同时也会增加alter table的语法了,请看测试。<BR>SQL> set autot off<BR>SQL> alter table ht03 RESULT_CACHE(mode force);</P>
<P>Table altered.</P>
<P>Elapsed: 00:00:00.33<BR>SQL> set autot traceonly<BR>SQL> select * from ht03 where object_id=999;</P>
<P>Elapsed: 00:00:00.05</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 1330547204</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | f42hd8bp1h26hbdqqs6bz47m3z | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT03 | 1 | 36 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | HT03_IDX | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=999)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT03); attributes=(ordered); name="select * from ht03 where object_id=999"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 178 recursive calls<BR> 0 db block gets<BR> 27 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 566 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 6 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> </P>
<P>同时在11gR2中,oracle在dba_tables和all_tables中增加一个字段,RESULT_CACHE。<BR>SQL> select owner,table_name,RESULT_CACHE from dba_tables where owner='ROGER';</P>
<P>OWNER TABLE_NAME RESULT_<BR>------------------------------ ------------------------------ -------<BR>ROGER HT04 FORCE<BR>ROGER HT03 FORCE<BR>ROGER HT02 DEFAULT<BR>ROGER HT01 DEFAULT</P>
<P>Elapsed: 00:00:02.75<BR>SQL> </P>
<P>关于字段RESULT_CACHE其中有3个属性,分别为DEFAULT,FORCE和MANUAL,大家可以参考11.2的官方文档。<BR>这里有点需要说明的是,必然当表结构或定义发变化了,那么query cache 缓存的信息都将被清除,如下例子。<BR>SQL> set autot traceonly<BR>SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;</P>
<P>Elapsed: 00:00:00.03</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | b500gqatyy0zq32az39dhnk3fb | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=100)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 44 recursive calls<BR> 0 db block gets<BR> 10 consistent gets<BR> 2 physical reads<BR> 0 redo size<BR> 560 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> desc ht01<BR> Name Null? Type<BR> ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------<BR> OWNER VARCHAR2(30)<BR> OBJECT_NAME VARCHAR2(128)<BR> OBJECT_ID NUMBER</P>
<P>SQL> alter table ht01 modify (owner VARCHAR2(40));</P>
<P>Table altered.</P>
<P>Elapsed: 00:00:00.22<BR>SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;</P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | b500gqatyy0zq32az39dhnk3fb | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=100)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 178 recursive calls<BR> 0 db block gets<BR> 27 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 560 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 6 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;</P>
<P>Elapsed: 00:00:00.02</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | b500gqatyy0zq32az39dhnk3fb | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=100)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 1 recursive calls<BR> 0 db block gets<BR> 0 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 560 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;</P>
<P>Elapsed: 00:00:00.01</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | b500gqatyy0zq32az39dhnk3fb | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=100)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 0 recursive calls<BR> 0 db block gets<BR> 0 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 560 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> </P>
<P>从上面的信息来看,我想已经完全可以说明问题了,如何有人说这还不能说明问题的话,那请看下面:<BR>SQL> conn /as sysdba<BR>Connected.<BR>SQL> alter session set events 'immediate trace name heapdump level 2';</P>
<P>Session altered.</P>
<P>Elapsed: 00:00:04.38<BR>SQL> @ gettrc.sql</P>
<P>TRACE_FILE_NAME<BR>------------------------------------------------------------------------------------<BR>/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_4106.trc</P>
<P>Elapsed: 00:00:00.82<BR>SQL> <BR>SQL> !<BR>$ grep -i Result /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_4106.trc<BR> Chunk 24bdecac sz= 32816 freeable "Result Cache " ds=0x272758b4<BR> Chunk 24be6cdc sz= 32816 freeable "Result Cache " ds=0x272758b4<BR> Chunk 24beed0c sz= 32816 recreate "Result Cache " latch=(nil)<BR>$ <BR>SQL> oradebug setmypid<BR>Statement processed.<BR>SQL> oradebug dump heapdump_addr 2 656890036;<BR>Statement processed.<BR>SQL> oradebug tracefile_name<BR>/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_6683.trc<BR>SQL> </P>
<P># grep -i ht01 /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_6683.trc<BR>24BE11A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR>24BE15A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR>24BE11A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR>24BE15A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR># </P>
<P>下面我们修改表ht01的表结构,然后再次dump 看看结果如何。<BR>SQL> set autot off<BR>SQL> alter table ht01 modify (owner VARCHAR2(50));</P>
<P>Table altered.</P>
<P>Elapsed: 00:00:00.06<BR>SQL> set autot traceonly<BR>SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=101;</P>
<P>Elapsed: 00:00:00.05</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | br1m2nyfp7v9c5drfp1gn5xp92 | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=101)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=101"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 178 recursive calls<BR> 0 db block gets<BR> 27 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 563 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 6 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> /</P>
<P>Elapsed: 00:00:00.01</P>
<P>Execution Plan<BR>----------------------------------------------------------<BR>Plan hash value: 2671155529</P>
<P>-----------------------------------------------------------------------------------------------------------<BR>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR>-----------------------------------------------------------------------------------------------------------<BR>| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |<BR>| 1 | RESULT CACHE | br1m2nyfp7v9c5drfp1gn5xp92 | | | | |<BR>| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |<BR>|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |<BR>-----------------------------------------------------------------------------------------------------------</P>
<P>Predicate Information (identified by operation id):<BR>---------------------------------------------------</P>
<P> 3 - access("OBJECT_ID"=101)</P>
<P>Result Cache Information (identified by operation id):<BR>------------------------------------------------------</P>
<P> 1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=101"</P>
<P><BR>Statistics<BR>----------------------------------------------------------<BR> 0 recursive calls<BR> 0 db block gets<BR> 0 consistent gets<BR> 0 physical reads<BR> 0 redo size<BR> 563 bytes sent via SQL*Net to client<BR> 415 bytes received via SQL*Net from client<BR> 2 SQL*Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</P>
<P>SQL> <BR>SQL> oradebug setmypid<BR>Statement processed.<BR>SQL> oradebug dump heapdump_addr 2 656890036;<BR>Statement processed.<BR>SQL> oradebug tracefile_name<BR>/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_28227.trc<BR>SQL> <BR># grep -i ht01 /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_28227.trc<BR>24BE11A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR>24BE15A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR>24BE19A0 6F726620 7468206D 77203130 65726568 [ from ht01 where] <BR>24BE11A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR>24BE15A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR>24BE19A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]<BR># </P>
<P>从上面可以看出多了2条信息,24BE19A0。 从上面的实验来看,我们可以推断出oracle这里应该是这样管理的,<BR>那就是即使表结构定义发生改变了,那么原来cache的信息仍然存在query cache中,当然,当cache不够用了,<BR>也是会被清除掉的,至于说oracle这里是如何去判断如何不去选择旧的cache信息,那么我就不得而知了。<BR>如果谁研究的更为透彻,记得告诉我,谢谢!</P>
<P>到最后,大家可能会想query cache的工作原理是什么?sql的结果集缓存超过多少或者说在使用了该特性<BR>的情况下,如何通过算法去检索client所需要的信息呢?这些目前还都是未知数。<BR></P>
页:
[1]