Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

62

积分

0

好友

8

主题
1#
发表于 2012-5-22 10:12:52 | 查看: 4323| 回复: 1
/*构造测试数据*/
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.为何有事务未提交的时候,索引逻辑读变的那么高?
2#
发表于 2012-5-22 14:45:47
没有提交的时候,Oracle需要当前块及其UNDO块来构造一致性块,多读很多块

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-25 13:55 , Processed in 0.046375 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569