- 浏览: 5153191 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
silence19841230:
先拿走看看
SpringBoot2.0开发WebSocket应用完整示例 -
wallimn:
masuweng 写道发下源码下载地址吧!三个相关文件打了个包 ...
SpringBoot2.0开发WebSocket应用完整示例 -
masuweng:
发下源码下载地址吧!
SpringBoot2.0开发WebSocket应用完整示例 -
masuweng:
SpringBoot2.0开发WebSocket应用完整示例 -
wallimn:
水淼火 写道你好,我使用以后,图标不显示,应该怎么引用呢,谢谢 ...
前端框架iviewui使用示例之菜单+多Tab页布局
今天看了yangtingkun很多文章,相当的不错。
下面摘录关于KEEP池的文章:
本人在ORACLE11GR2上,也测试了一遍。
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池要简单一些。
转自:http://space.itpub.net/8183550/viewspace-667046
下面摘录关于KEEP池的文章:
本人在ORACLE11GR2上,也测试了一遍。
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池要简单一些。
转自:http://space.itpub.net/8183550/viewspace-667046
发表评论
-
Oracle连接故障的排除
2024-09-09 22:33 315Oracle版本为11G,操作系统为Windows Ser ... -
Oracle数据库相关系统突然提示“SQLException:违反协议”
2024-02-19 15:50 991SQLException:违反协议这个异常可能由很多的 ... -
CentOS在Docker中安装Oracle
2024-02-06 12:13 11071.拉取Oracle镜像,并检 ... -
Windows Server安装oracle数据库一直停在82%
2023-02-04 12:01 556网上有个说法:服务器超过一定数量的CPU后,将不能正常安装 ... -
ORA-04030错误处理
2023-02-04 11:52 2581【错误描述】 错误信息如下: ORA-04030:在尝 ... -
ORA-04030错误处理
2023-02-04 11:45 403【错误描述】 错误信息如下: ORA-04030:在尝 ... -
Linux安装MySQL数据库
2019-06-10 22:27 17491.进入安装包所在目录,解压: tar zxvf mysql- ... -
确定MySQL在Linux系统中配置文件的位置
2019-04-14 19:30 27471.通过which mysql命令来查看mysql的安装位置。 ... -
mysql set names 命令和 mysql 字符编码问题
2019-04-12 00:34 1121转自:https://www.cnblogs.com/digd ... -
MYSQL中取当前周/月/季/年的第一天与最后一天
2018-11-17 23:16 2190转自:https://blog.csdn.net/ ... -
Oracle删除大量数据的实践
2016-11-07 18:03 5797一、引言 从来没有 ... -
Oracle 数据库简明教程 V0.1
2016-03-23 21:01 2029供初学者入门学习使用,以开发者常见、常用的知识为主,基本上 ... -
Oracle拆分字符串函数
2016-03-23 10:58 3333create or replace type string ... -
Oracle数据库远程连接无响应
2016-03-21 10:20 4280故障现象: 服务器本机使用sqlplus / as s ... -
Oracle PGA详解
2015-10-21 15:34 11431转自:http://yanguz123.iteye.com/b ... -
Oracle12C导入dmp数据
2015-10-08 23:43 20504Oracle12C,发生了较大的变化。以前熟悉的东西变得陌 ... -
SQLLDR数据导入小结
2015-07-25 22:06 75011.创建数据表 CREATE TABLE ... -
Window7安装Oracle10
2015-03-06 12:14 1587每次安装都要百度,转到自己的博客上,找起来方便,还能增加访 ... -
Oracle SQL Developer 连接 Mysql 数据库
2015-02-25 19:36 3622下载JDBC包,解压缩这里只要mysql-connector- ... -
Mysql数据备份与恢复
2015-02-25 19:15 1325备份/恢复策略 1. 要定期做 mysql备份,并考虑系统可以 ...
相关推荐
各省地方财政收入及物价指数(2009-2019)-最新出炉.zip
php资门房产公司网站的设计与实现(功能视频).mp4
基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计),该项目是个人毕设项目,答辩评审分达到98分,代码都经过调试测试,确保可以运行!欢迎下载使用,可用于小白学习、进阶。该资源主要针对计算机、通信、人工智能、自动化等相关专业的学生、老师或从业者下载使用,亦可作为期末课程设计、课程大作业、毕业设计等。项目整体具有较高的学习借鉴价值!基础能力强的可以在此基础上修改调整,以实现不同的功能。 基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计)基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计)基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计)基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计)基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计)基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计)基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计)基于STM32的智能家居系统设计源码+论文+答辩PPT(高分毕业设计)基于STM32的智能家居系统设计源
基于Python+django的智能人脸识别监控系统源码+文档说明(毕业设计)该项目是个人毕设项目,答辩评审分达到98分,代码都经过调试测试,确保可以运行!欢迎下载使用,可用于小白学习、进阶。该资源主要针对计算机、通信、人工智能、自动化等相关专业的学生、老师或从业者下载使用,亦可作为期末课程设计、课程大作业、毕业设计等。项目整体具有较高的学习借鉴价值!基础能力强的可以在此基础上修改调整,以实现不同的功能。 基于Python+django的智能人脸识别监控系统源码+文档说明(毕业设计)基于Python+django的智能人脸识别监控系统源码+文档说明(毕业设计)基于Python+django的智能人脸识别监控系统源码+文档说明(毕业设计)基于Python+django的智能人脸识别监控系统源码+文档说明(毕业设计)基于Python+django的智能人脸识别监控系统源码+文档说明(毕业设计)基于Python+django的智能人脸识别监控系统源码+文档说明(毕业设计)基于Python+django的智能人脸识别监控系统源码+文档说明(毕业设计)基于Python+django的智能人脸识
通过STM32C8T6单片机对DHT11温湿度数据进行采集,并且温湿度数据显示在OLED屏幕上,同时TTL语音播报温湿度
【项目简介】 可辅助在本地配置运行 本基于微信小程序的无中介租房系统是针对租房而开发,采用MYSQL数据库、微信开发者工具进行微信端开发,满足用户通过微信客户端进行租房的需求。本小程序是以微信为入口的,可以说是在自带接近10亿的流量人口。整个系统不仅操作简单、便捷,而且节约用户的时间及资源成本等,仅需通过一部手机和微信号即可满足用户们的需求。 论文首先阐述了基于微信小程序的无中介租房系统的开发背景,并对开发基于微信小程序的无中介租房系统所采用的相关技术进行了详细的介绍,然后对此应用软件进行了需求分析、设计,最后对系统进行测试、维护,保证系统的正常运行。 关键词:微信小程序 ;微信开发者工具;租房;MYSQL数据库
企业生命周期(基于现金流组合)数据和Stata代码-最新出炉.zip
遥感数据-最新出炉.zip
als;vnaa;sdlvjaoei
社科基金申报大礼包-最新出炉.zip
交易会安全保卫规定.doc
Practical_5.zip
行政执法证件登记申领表.doc
python
校内占用绿地、道路施工申请表.xls
分屏软件-Maxto-最新出炉.zip
人工神经网络算法,python
数据库课程设计
文化旅游发展计划.docx
毕业论文工具大全-最新出炉.zip