为何有未提交的事务时，索引扫描逻辑读增加很多

repentance

/*构造测试数据*/
session1:
SQL> CREATE TABLE TEST AS SELECT * from dba_objects;
Table created.
SQL> INSERT INTO TEST SELECT * from dba_objects;
65988 rows created.
SQL> /
65988 rows created.
SQL> /
65988 rows created.
SQL> /
65988 rows created.
SQL> /
65988 rows created.
SQL> /
65988 rows created.
SQL> commit;
Commit complete.
SQL> create index TEST_CREATED on TEST (CREATED);
Index created.
--数据分布情况
SQL> SELECT * FROM (SELECT trunc(created) c_date,COUNT(1) from TEST  GROUP BY TRUNC(created) ORDER BY 2 DESC)
  2  WHERE ROWNUM<5;
C_DATE       COUNT(1)
---------- ----------
2008-10-31     192367
2008-05-06      69426
2009-09-11      62342
2009-05-31      38059
/*测试*/
1.索引范围扫描
SQL> SELECT COUNT(1) from TEST a WHERE a.created BETWEEN DATE'2009-05-31' AND DATE'2009-06-01';
  COUNT(1)
----------
     38066
Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     9 |   112   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE   |              |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_CREATED | 48563 |   426K|   112   (1)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        104  consistent gets
          0  physical reads
          0  redo size
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
2.有事务插入数据未提交或回滚
session2:
SQL> INSERT INTO TEST SELECT * FROM TEST a WHERE a.created BETWEEN DATE'2009-05-31' AND DATE'2009-06-01';
38066 rows inserted
SQL> INSERT INTO TEST SELECT * FROM TEST a WHERE a.created BETWEEN DATE'2009-05-31' AND DATE'2009-06-01';
76132 rows inserted
SQL> INSERT INTO TEST SELECT * FROM TEST a WHERE a.created BETWEEN DATE'2009-05-31' AND DATE'2009-06-01';
152264 rows inserted
session2不提交或回滚
session1:
SQL> SELECT COUNT(1) from TEST a WHERE a.created BETWEEN DATE'2009-05-31' AND DATE'2009-06-01';
  COUNT(1)
----------
     38066
Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     9 |   190   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE   |              |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_CREATED | 38548 |   338K|   190   (1)| 00:00:03 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      67277  consistent gets            ---逻辑读从104增长到67277
          0  physical reads
      67156  redo size
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
3.session2提交或回滚事务后session运行同样查询
session2:
SQL> commit;
Commit complete
session1:
SQL> SELECT COUNT(1) from TEST a WHERE a.created BETWEEN DATE'2009-05-31' AND DATE'2009-06-01';
  COUNT(1)
----------
    304528
Execution Plan
--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     9 |   485   (3)| 00:00:06 |
|   1 |  SORT AGGREGATE       |              |     1 |     9 |            |          |
|*  2 |   INDEX FAST FULL SCAN| TEST_CREATED |   263K|  2311K|   485   (3)| 00:00:06 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1891  consistent gets        --提交事务后逻辑读下降为正常值
          0  physical reads
      64080  redo size
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
         
/*问题*/
1.为何有事务未提交的时候，索引逻辑读变的那么高？

yanshoupeng

没有提交的时候，Oracle需要当前块及其UNDO块来构造一致性块，多读很多块