RECYCLE池的CACHE特点（一）-技术开发专区

RECYCLE池的CACHE特点（一）

作者：yangtingkun 编辑：孙敏杰 2006-05-27 00:00

【IT168 技术文档】

RECYCLE池的CACHE算法与DEFAULT池和KEEP池都不相同。

RECYCLE池的设置也很简单，设置DB_RECYCLE_CACHE_SIZE的值大于0，再将对象的BUFFER_POOL参数设置为RECYCLE就可以了。

SQL> SHOW PARAMETER DB_%E%_CACHE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
db_keep_cache_size big integer 134217728
db_recycle_cache_size big integer 0
SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 0;
System altered.
SQL> ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE = 134217728;
System altered.

创建测试用表：

SQL> CREATE TABLE T STORAGE(BUFFER_POOL RECYCLE) AS
2 SELECT * FROM DBA_SOURCE
3 UNION ALL
4 SELECT * FROM DBA_SOURCE;
Table created.
SQL> CREATE TABLE T2 STORAGE(BUFFER_POOL RECYCLE) AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> CREATE TABLE T3 STORAGE(BUFFER_POOL RECYCLE) AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> COL SEGMENT_NAME FORMAT A30
SQL> SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 M
2 FROM USER_SEGMENTS
3 WHERE SEGMENT_NAME IN ('T', 'T2', 'T3')
4 GROUP BY SEGMENT_NAME;
SEGMENT_NAME M
------------------------------ ----------
T 152
T2 80
T3 80

T表的大小超过RECYCLE池的大小，而T2和T3的大小都小于RECYCLE的大小。

下面看看RECYCLE池的CACHE特点：

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 1
T2 xcur 1
T3 xcur 1
SQL> SET AUTOT ON STAT
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 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 1
T2 xcur 4829
T3 xcur 1
SQL> SELECT COUNT(*) FROM T3;
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 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 1
T2 xcur 4829
T3 xcur 3266

第1页：之一第2页：之二第3页：之三

关注我们