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