||
步2:在另一个表以索引方式扫描若干行: SQL> select /*+index(qsmed.zjj1) */ * from qsmed.zjj1 where id>=1750 and id<=3500; 已选择1751行。 执行计划 ---------------------------------------------------------- Plan hash value: 2538640105 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1772 | 164K| 31 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| ZJJ1 | 1772 | 164K| 31 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | ZJJ1_ID | 1772 | | 6 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1750 AND "ID"<=3500) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 263 consistent gets 25 physical reads 0 redo size 165015 bytes sent via SQL*Net to client 1661 bytes received via SQL*Net from client 118 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1751 rows processed 这有25个物理读。为了为这25个物理读寻找可用块,服务器进程将从LRU的末端,寻找可用块,将ZJJ1相关的块的信息覆盖这些可用块。并将它们从LRU的冷端末尾,移到冷端头。 显示12号文件7号块,不会有任何变化: SQL> select lru_flag,tch from x$bh where dbablk=12 and dbarfil=7; LRU_FLAG TCH ---------- ---------- 0 10 ZJJ1的25个物理读,目前还没有对12号文件7号块的状态有任何的影响,只不过,12号文件7号块被挤了向冷端末尾: 上图中ZJJ_1只占了4个格,这是不精确的,ZJJ_1和其索引应该占至少25个块才对,因为物理读有25个块。 |
步4:再加大扫描的行数: SQL> select /*+index(qsmed.zjj1) */ * from qsmed.zjj1 where id<=17500; 已选择17500行。 执行计划 ---------------------------------------------------------- Plan hash value: 2538640105 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 17683 | 1640K| 287 (1)| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| ZJJ1 | 17683 | 1640K| 287 (1)| 00:00:02 | |* 2 | INDEX RANGE SCAN | ZJJ1_ID | 17683 | | 42 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"<=17500) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 2589 consistent gets 116 physical reads 0 redo size 1794670 bytes sent via SQL*Net to client 13211 bytes received via SQL*Net from client 1168 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 17500 rows processed 这一次12号文件7号块的位置和状态发生了变化, SQL> select lru_flag,tch from x$bh where dbablk=12 and dbarfil=7; LRU_FLAG TCH ---------- ---------- 8 0 LRU_FLAG的值变为了8,TCH列被清零。这说明12号文件7号块被移到了热端。原来方格10所代表的块不再属于热端,已被挤到了冷端。 |