oracle KEEP池的CACHE特點
KEEP池的使用十分簡單，設定DB_KEEP_CACHE_SIZE的值大於0，就可以將其他物件的BUFFER_POOL引數設定為KEEP了。
SQL> SHOW PARAMETER DB%CACHE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 956301312
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> SELECT 128*1024*1024 FROM DUAL;
128*1024*1024
-------------
134217728
SQL> SELECT 956301312-134217728 FROM DUAL;
956301312-134217728
-------------------
822083584
SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 822083584;
System altered.
SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 134217728;
System altered.
建立測試用表：
SQL> CREATE TABLE T AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> SELECT SUM(BYTES)/1024/1024 M FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'T';
M
----------
80
SQL> ALTER TABLE T STORAGE (BUFFER_POOL KEEP);
Table altered.
SQL> SET AUTOT ON STAT
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
107 recursive calls
0 db block gets
4849 consistent gets
4828 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
現在看到，KEEP池已經生效，T的資料已經快取在KEEP池中。
下面再建立一個同樣大小的表：
SQL> CREATE TABLE T2 STORAGE (BUFFER_POOL KEEP) AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4828 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 COUNT(*) FROM T2;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
兩張表的大小合在一起已經超過了KEEP池的大小。下面看看現在記憶體中快取的情況：
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3268
T2 xcur 4829
T2中的資料已經全部放入KEEP池，而部分T中的資料被替換出去。
下面用執行對T的查詢，嘗試快取T的資料：
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4828 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
這時可以觀察到一個有趣的現象，對T執行掃描的時候，幾乎全部都是物理讀，而我們剛剛看到內容中仍然有3000多個資料塊在快取中。
這就是KEEP池與DEFAULT池演算法的差異之處。
首先，將T表的容量擴大1倍，這樣，即使是單獨查詢T表，所有的資料也無法全部放入KEEP池。
SQL> INSERT INTO T SELECT * FROM T;
167011 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
4828 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8097
SQL> SELECT SUM(BLOCKS) FROM USER_EXTENTS WHERE SEGMENT_NAME = 'T';
SUM(BLOCKS)
-----------
9728
可以看到T表一共是9728個BLOCK，而KEEP池中僅僅快取了8097個。
SQL> SHOW PARAMETER DB_BLOCK_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
db_block_size integer 16384
KEEP池的大小是128M，除以資料塊的大小16K，KEEP池中可以存放的BLOCK上限就是8K個。現在KEEP池已經裝滿，但是仍然有部分資料被替換到KEEP池外。
下面說明KEEP池快取的特點，先看一下查詢的結果：
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3268
T2 xcur 4829
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9655 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
對於第一個查詢全部物理讀比較好理解，這是由於當時KEEP池中的空間被T表佔滿了，隱藏這時候對T2的查詢需要從物理磁碟讀取。執行完這個查詢，可以發現，T2表全部放入快取中，T表的資料被替換出一部分，還有3000多個BLOCK儲存在KEEP池中。但是對T的查詢確全部由物理讀組成，而KEEP池中的快取沒有起作用。
對於普通的DEFAULT池，Oracle使用的是最近最少使用演算法，在記憶體中有一個類似連結串列的結構。當DB_CACHE填滿後，Oracle會從將這個連結串列的最少使用端交換出去，用來存放新的資料。而且會根據新的資料的性質，選擇把新的資料放到最多使用端還是最少使用端。
如果DB_CACHE滿後，執行的是索引掃描，則Oracle認為需要快取這些資料，因此會清空最少使用端的空間，存放索引掃描的快取資料。如果是大表的全表掃描，則Oracle認為這些資料是很少需要被訪問的，因此清空最少使用端的空間放入表掃描的快取資料後，仍然放回到最少使用端。
而KEEP池沒有采用這種演算法，KEEP池其實是一塊可用記憶體採用類似迴圈的演算法進行訪問。如果KEEP池裡面還有剩餘空間，則新的資料會首先使用剩餘的空間，如果KEEP池已經儲存滿了，Oracle會從頭開始重用KEEP池。
這就是對T表的查詢導致了全部的物理讀的原因。由於T2表將T表中最初部分資料替換出KEEP，導致了查詢T表的時候，開頭部分的資料無法找到，產生了物理讀後在KEEP池中替換了T表中間部分的資料，同樣的道理，讀取到T表中部的時候，又把T表末尾的資料替換出去了。因此，執行完查詢發現，對T表查詢全部都是物理讀，KEEP池緩衝中的內容沒有起作用。
而且，由於T表的大小超過了KEEP池的大小，因此T表末尾部分的資料又會將開頭部分的資料替換出去，因此，再次對T表查詢仍然全部都是物理讀。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9655 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9655 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
只有當對T表的掃描的塊小於KEEP池的大小時，才能保證快取可以被利用。
SQL> SELECT COUNT(*) FROM T WHERE ROWNUM < 100000;
COUNT(*)
----------
99999
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3696 consistent gets
3695 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 COUNT(*) FROM T WHERE ROWNUM < 100000;
COUNT(*)
----------
99999
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3696 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
首先，建立T表的一個索引：
SQL> CREATE INDEX IND_T_NAME ON T (NAME) STORAGE (BUFFER_POOL KEEP);
Index created.
SQL> ALTER TABLE T MODIFY NAME NOT NULL;
Table altered.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
120 recursive calls
0 db block gets
9682 consistent gets
9655 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
下面看KEEP池和DEFAULT池快取的另一個區別：
SQL> CREATE INDEX IND_T_NAME ON T (NAME) STORAGE (BUFFER_POOL KEEP);
Index created.
SQL> ALTER TABLE T MODIFY NAME NOT NULL;
Table altered.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
120 recursive calls
0 db block gets
9682 consistent gets
9655 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8096
SQL> SELECT /*+ INDEX (T) */ COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
538 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7558
IND_T_NAME xcur 538
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9656 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8096
通過測試可以看到，在KEEP池中，索引掃描引起的快取不在擁有高於全表掃描快取的優先順序。根據上一篇文章中描述的KEEP池的快取使用特點也可以推出這個結果。由於KEEP池不存在LRU連結串列，因此對索引掃描和全表掃描產生的快取採用一視同仁的態度。
第二個特點，CACHE儲存引數無效：
SQL> CREATE TABLE T3 STORAGE(BUFFER_POOL KEEP) AS SELECT * FROM T2;
Table created.
SQL> ALTER TABLE T2 CACHE;
Table altered.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8096
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3267
T2 xcur 4829
SQL> SELECT COUNT(*) FROM T3;
COUNT(*)
----------
167011
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T2 xcur 3267
T3 xcur 4829
SQL> SELECT COUNT(*) FROM T WHERE ROWNUM < 100000;
COUNT(*)
----------
99999
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 3696
T3 xcur 4400
通過測試可以發現，CACHE選項沒有起作用，其實這也不難理解，既然放到單獨的KEEP池中，那麼必然打算將這個物件快取，因此Oracle對所有KEEP池中的物件採用了預設CACHE的方式。而忽略物件本身的CACHE和NOCACHE選項。
KEEP池雖然有很多特點與DEFAULT池有較大的區別，但是有一點卻是二者是相似的：最近最常用的BLOCK最晚被替換出記憶體。
雖然KEEP池沒有采用LRU連結串列的方式，但是Oracle仍然考慮了LRU的演算法，KEEP池的快取中，仍然是越常用的BLOCK保留時間越長。看下面這個例子：
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9655 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8096
首先通過全表掃描T表將KEEP池“清空”。
SQL> SELECT /*+ INDEX(T) */ COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
538 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7558
IND_T_NAME xcur 538
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 2729
T2 xcur 4829
IND_T_NAME xcur 538
先對T表的索引IND_T_NAME的掃描，後對T2表進行掃描，將這兩部分資料裝載到KEEP池中。
SQL> SELECT COUNT(*) FROM T WHERE ROWNUM < 200000;
COUNT(*)
----------
199999
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6543 consistent gets
6544 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 6544
T2 xcur 1552
通過限制一定數量的T的掃描，發現Oracle首先將IND_T_NAME的快取替換出去，然後才是T2的快取。這個符合KEEP池的先進先出的特點。這裡不用T的全掃描是為了避免將T2和IND_T_NAME全部替換出去，導致無法觀察到替換的先後順序。
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9666 consistent gets
9656 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 8096
下面再次利用T的全掃描“清空”KEEP池。
SQL> SELECT /*+ INDEX(T) */ COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
538 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7558
IND_T_NAME xcur 538
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4829 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 /*+ INDEX(T) */ COUNT(*) FROM T;
COUNT(*)
----------
334022
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
538 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 COUNT(*) FROM T WHERE ROWNUM < 200000;
COUNT(*)
----------
199999
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6543 consistent gets
6544 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 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 OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 6544
T2 xcur 1014
IND_T_NAME xcur 538
仍然採用先索引IND_T_NAME掃描後T2掃描的順序，不同的是，在將T2裝載入KEEP池之後，又對索引IND_T_NAME進行了一次掃描。
這次執行T的掃描可以發現，首先被替換出去的是T2的快取。這說明了KEEP池同樣也考慮了LRU演算法。不過這種演算法相對於DEFAULT池要簡單一些。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-667046/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Buffer Cache（緩衝區快取）篇：keep pool（保留池）快取
- KEEP POOL和CACHE屬性的區別
- Oracle keep的用法Oracle
- mysql和Oracle的特點，優缺點MySqlOracle
- oracle dataguard的兩個新特點。Oracle
- 測試將表快取到keep池快取
- 【NUMBER】Oracle的NUMBER資料型別特點Oracle資料型別
- 關於Oracle中ASH功能的特別說明，oracle10個/11g的新特點Oracle
- Oracle 的 KEEP pool, RECYCLE pool和DEFAULT poolOracle
- C++的特點C++
- 繼承的特點繼承
- Ubuntu的特點(轉)Ubuntu
- 關於Oracle中DB time等待事件的特別說明，oracle10個/11g的新特點Oracle事件
- Oracle11g DataGuard 新特點小結Oracle
- MyBatis的優缺點以及特點MyBatis
- 11.2使用KEEP池導致ENQ: KO - Fast Object Checkpoint等待ENQASTObject
- Service Worker cache 相比 HTTP cache 的一些優點HTTP
- Oracle中分組查詢時keep的使用[轉][Oracle
- HTTP協議的特點HTTP協議
- XFlow軟體的特點
- 大資料的特點大資料
- 科技翻譯的特點
- 化工行業的特點行業
- Python語言的特點有哪些?九大特點介紹！Python
- create table中的buffer pool 設定 KEEP 及CACHE 引數的聯絡及區別
- Oracle Exadata 技術架構和主要特點Oracle架構
- 深入理解shared pool共享池之library cache的library cache lock系列四
- 深入理解shared pool共享池之library cache的library cache pin系列三
- oracle分析函式,keep and over解說Oracle函式
- oracle cache table(轉)Oracle
- Oracle Library cacheOracle
- Oracle Buffer Cache原理Oracle
- Oracle Query Result CacheOracle
- Oracle database buffer cacheOracleDatabase
- oracle cache快取Oracle快取
- oracle cache table(1)Oracle
- oracle cache table(3)Oracle
- oracle cache table(2)Oracle