Arraysize 對consistent get的影響

[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>

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.

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

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

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