刚刚执行个SQL想看看统计情况,发现竟然物理读和一致读都是0,请高手帮忙解释下,谢谢
SQL> SELECT COUNT(1) FROM t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19564 (3)| 00:03:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 18M| 19564 (3)| 00:03:55 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
214 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(1) FROM t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19564 (3)| 00:03:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 18M| 19564 (3)| 00:03:55 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
214 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
解决方案 »
- 在控制台运行sqlplus后输入system后回车再输入密码 怎么不接受了
- oracle通过透明网关访问sql2005数据库查询没有结果
- 批量更新数据库
- pl/sql DEV中的一个问题
- 大批量写入数据性能比较批量
- procedure里面 commit时候的问题
- 数据库突然全部断开,提示ORA-01041: 内部错误,hostdef 扩展名不存在
- 为什么安装了Oracle之后我得Enterprise Manager Console不能运行呢?
- pro*c 动态sql方法三语法问题一
- 如何在oracle中使用begin end来运行select
- 求sql存储过程转oracle的写法
- ora-04091 表发生了变化,触发器函数不能读它 -求助
在select count(*)之前应该执行过select * from t之类的语句,导致表的数据块已经在SGA中了,这样Oracle就不需要物理读了。
这个T表记录有一千多万,就算记录在SGA中,consistent gets也应该有值呀。。
物理读为0应该就是数据已经在SGA中了,对oracle的统计数字还是比较有信心的,因此不怀疑统计的错误。
SQL> create table a as select object_id from dba_objects where object_id=2;Table created.SQL> select * from a; OBJECT_ID
----------
2首先创建一个小表,可以一次将全部数据读入SGA(这个测试库是我自己创建的,我一个人操作,排除其他人对数据的影响) set autotrace trace exp stat;
SQL> select * from a;
Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| A | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由于第一段的select语句,已将a表数据读入buffer cache ,所以这里显示的是 3 consistent gets,并不是你解释那样,只有读回滚段的数据才是consistent gets,而且也没人操作表aSQL> select * from a;
Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| A | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
接下来不管执行几次select * from a,都是显示 3 consistent gets。所以我认为出现一致读与物理读都为0,并不是楼上解释的那样,还请各位高手帮忙解释下