KEEP池的CACHE特點(轉)

zhouwf0726發表於2019-07-10

http://yangtingkun.itpub.net/post/468/86429

物件放入KEEP池後,資料的CACHE特性和DEFAULT池是有一些區別的。

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池演算法的差異之處。

文章介紹了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表一共是9728BLOCK,而KEEP池中僅僅快取了8097個。

SQL> SHOW PARAMETER DB_BLOCK_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
db_block_size integer 16384

KEEP池的大小是128M,除以資料塊的大小16KKEEP池中可以存放的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

下面繼續說明KEEP池和DEFAULT的一些不同之處。

首先,建立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的方式。而忽略物件本身的CACHENOCACHE選項


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/756652/viewspace-242252/,如需轉載,請註明出處,否則將追究法律責任。

相關文章