oracle cache table

作者：你好赵伟 | 2024-06-19 01:19:58

踩

oracle cache table

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.[@more@] 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/34329/viewspace-916663/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/34329/viewspace-916663/

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/734899