Arraysize 对consistent get的影响

 logical read  或 consistent get 是指读取block 的块*次，对block每次访问被计数一次

创建测试表,生成统计信息

[oracle@oracle10g ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 3 02:53:38 2014

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> conn test/test

Connected.

SQL> create table t

  2  nologging

  3  as

  4  select *

  5  from all_objects;

 

Table created.

 

SQL> create index t_ind on t(owner,object_type,object_name) nologging;

 

Index created.

 

SQL> begin

  2      dbms_stats.gather_table_stats(

  3      ownname =>test,

  4      tabname =>'T',

  5      estimate_percent=>dbms_stats.auto_sample_size,

  6      cascade=>true);

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

SQL> select count(*) from t; 

 

  COUNT(*)

----------

     70611

 

SQL>

 

使用show_space存储过程查询block情况

SQL> set serveroutput on

SQL> exec show_space('T_IND','TEST','INDEX');

Total Blocks............................512

Total Bytes.............................4194304

Unused Blocks...........................1

Unused Bytes............................8192

Last Used Ext FileId....................4

Last Used Ext BlockId...................119433

Last Used Block.........................127

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............1

25% -- 50% free space bytes.............8192

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........0

75% -- 100% free space bytes............0

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................494

Total bytes.............................4046848

 

PL/SQL procedure successfully completed.

 

每个block 大概70611/512=138行记录

 

 

 

 arraysize to 15

SQL> set autotrace traceonly explain statistics;

SQL> show arraysize;

arraysize 15

 

SQL> select owner,object_type,object_name from t order by owner,object_type,object_name;

 

70611 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1106009184

 

--------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       | 70611 |  2758K|   497   (1)| 00:00:06 |

|   1 |  INDEX FULL SCAN | T_IND | 70611 |  2758K|   497   (1)| 00:00:06 |

--------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       5167  consistent gets

          0  physical reads

          0  redo size

    2666019  bytes sent via SQL*Net to client

      52196  bytes received via SQL*Net from client

       4709  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      70611  rows processed

Arraysize 设置为2  consistent gets为35548 

SQL> set arraysize 2;

SQL> select owner,object_type,object_name from t order by owner,object_type,object_name;

 

70611 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1106009184

 

--------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       | 70611 |  2758K|   497   (1)| 00:00:06 |

|   1 |  INDEX FULL SCAN | T_IND | 70611 |  2758K|   497   (1)| 00:00:06 |

--------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      35548  consistent gets

          0  physical reads

          0  redo size

    6551934  bytes sent via SQL*Net to client

     388774  bytes received via SQL*Net from client

      35307  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      70611  rows processed

Arraysize 设置为2  consistent gets  509 

SQL> set arraysize 5000;

SQL> select owner,object_type,object_name from t order by owner,object_type,object_name;

 

70611 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1106009184

 

--------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       | 70611 |  2758K|   497   (1)| 00:00:06 |

|   1 |  INDEX FULL SCAN | T_IND | 70611 |  2758K|   497   (1)| 00:00:06 |

--------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        509  consistent gets

          0  physical reads

          0  redo size

    2070008  bytes sent via SQL*Net to client

        573  bytes received via SQL*Net from client

         16  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      70611  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15747463/viewspace-1159245/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15747463/viewspace-1159245/