arraysize定义了一次返回到客户端的行数,当扫描了arraysize 行后,停止扫描,返回数据,然后继续扫描。
这个过程就是统计信息中的SQL*Net roundtrips to/from client。因为arraysize 默认是15行,那么就有一个问题,因为我们一个block 中的记录数一般都会超过15行,所以如果按照15行扫描一次,那么每次扫描要多扫描一个数据块,一个数据块也可能就会重复扫描多次。
重复的扫描会增加consistent gets 和 physical reads。 增加physical reads,这个很好理解,扫描的越多,物理的可能性就越大。
consistent gets,这个是从undo里读的数量,Oracle 为了保证数据的一致性,当一个查询很长,在查询之后,数据块被修改,还未提交,再次查询时候,Oracle根据Undo 来构建CR块,这个CR块,可以理解成数据块在之前某个时间的状态。 这样通过查询出来的数据就是一致的。
那么如果重复扫描的块越多,需要构建的CR块就会越多,这样读Undo 的机会就会越多,consistent gets 就会越多。
测试:
nat@ORCL-10.1.16.14>create table t as select * from dba_tables;
Table created.
Elapsed: 00:00:00.65
nat@ORCL-10.1.16.14>analyze table t compute statistics;
Table analyzed.
Elapsed: 00:00:00.31
nat@ORCL-10.1.16.14>set autot trace
nat@ORCL-10.1.16.14>select * from t;
2773 rows selected.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2773 | 584K| 30 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 2773 | 584K| 30 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
284 consistent gets
0 physical reads
0 redo size
715274 bytes sent via SQL*Net to client
2443 bytes received via SQL*Net from client
186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2773 rows processed
nat@ORCL-10.1.16.14>select * from t order by 1;
2773 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2773 | 584K| | 164 (1)| 00:00:02 |
| 1 | SORT ORDER BY | | 2773 | 584K| 936K| 164 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T | 2773 | 584K| | 30 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
101 consistent gets
0 physical reads
0 redo size
149423 bytes sent via SQL*Net to client
2443 bytes received via SQL*Net from client
186 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2773 rows processed
nat@ORCL-10.1.16.14>show array
arraysize 15
nat@ORCL-10.1.16.14>set arraysize 5000
nat@ORCL-10.1.16.14>select * from t
2 ;
2773 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2773 | 584K| 30 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 2773 | 584K| 30 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
102 consistent gets
0 physical reads
0 redo size
691354 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2773 rows processed
nat@ORCL-10.1.16.14>select * from t order by 1;
2773 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2773 | 584K| | 164 (1)| 00:00:02 |
| 1 | SORT ORDER BY | | 2773 | 584K| 936K| 164 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T | 2773 | 584K| | 30 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
101 consistent gets
0 physical reads
0 redo size
126055 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2773 rows processed
在select * from t order by 1;时,Oracle也把arraysize临时设为t表的行数,它把所有数据先全部取出来放到sort区做排序,而在sort区的读取就不算在 consistent gets里了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24383181/viewspace-717737/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24383181/viewspace-717737/