ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 2012-03-05 ArraySize

2012-03-05 ArraySize

原创 Linux操作系统 作者:demonat 时间:2012-03-05 15:44:21 0 删除 编辑

       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 就会越多。


引用:http://blog.csdn.net/tianlesoftware/article/details/6579913

测试:
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 2012-03-07 递归wih
请登录后发表评论 登录
全部评论

注册时间:2011-04-15

  • 博文量
    46
  • 访问量
    92231