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

RECYCLE池的CACHE特点（三）

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

SQL> SELECT /*+ INDEX(T) */ COUNT(TEXT) FROM T;
COUNT(TEXT)
-----------
333442

Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
13897 consistent gets
9744 physical reads
0 redo size
384 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7557
IND_T_NAME xcur 539
SQL> SELECT /*+ INDEX(T) */ COUNT(TEXT) FROM T;
COUNT(TEXT)
-----------
333442

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13896 consistent gets
9794 physical reads
0 redo size
384 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7869
IND_T_NAME xcur 227
SQL> SELECT /*+ INDEX(T) */ COUNT(TEXT) FROM T;
COUNT(TEXT)
-----------
333442

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13896 consistent gets
10106 physical reads
0 redo size
384 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7661
IND_T_NAME xcur 435

索引扫描产生的CACHE也会被放入到缓冲区，而且似乎对于索引扫描产生的CACHE的算法和全表扫描不大相同。通常观察物理读和缓冲区内的块数可以发现，每次的值都在变化。

SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
334022

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9667 consistent gets
2013 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
9667 consistent gets
2012 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', 'IND_T_NAME')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 7661
IND_T_NAME xcur 435

和全表扫描相比，索引扫描产生的CACHE被缓冲的优先级更高。

第1页：一第2页：二

关注我们