oracle cache table(轉)

zhouwf0726發表於2019-05-29
Oracle
The KEEP buffer pool retains the schema object’s data blocks in memory.
The RECYCLE buffer pool eliminates data blocks from memory as soon as they
are no longer needed.
The DEFAULT buffer pool contains data blocks from schema objects that are not
assigned to any buffer pool, as well as schema objects that are explicitly
assigned to the DEFAULT pool.
The initialization parameters that configure the KEEP and RECYCLE buffer pools are
DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE.

物件放入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 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表一共是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

下面繼續說明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的方式。而忽略物件本身的CACHE和NOCACHE選項

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

相關文章