[20150209]为什么少1个逻辑读.txt

--昨天看vage的,提到1个奇怪的问题,作者也清楚为什么?我自己也困惑.
--把它记录下来.

1.建立测试环境:
SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t1 as select rownum id,'test' data from dual connect by levelcreate table t2 as select rownum id,'test' data from dual connect by levelexec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't2',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);

SCOTT@test> show array
arraysize 200

SCOTT@test> alter session set statistics_level=all;
Session altered.

SCOTT@test> select * from t1;
        ID DATA
---------- ----
         1 test

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1
Plan hash value: 3617692013
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     3 (100)|      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS FULL| T1   |      1 |      1 |     3   (0)|      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------
13 rows selected.

SCOTT@test> select * from t2;
        ID DATA
---------- ----
         1 test
         2 test

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  741xfy549bxbz, child number 0
-------------------------------------
select * from t2
Plan hash value: 1513984157
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     3 (100)|      2 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS FULL| T2   |      1 |      2 |     3   (0)|      2 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------
13 rows selected.

--注意看buffers的数量,一个是2,多了1条记录的情况下是3,很奇怪吧.而且在仅仅1条记录的情况下,仅仅读取段头1次.而不是作者讲的2
--次. 不知道是否这个版本特殊.还是oracle做了什么特殊优化.
--如果在10g,12c下测试(使用ctas建表),全表扫描T1逻辑读是3,全表扫描T2逻辑读是4. 不管怎样逻辑读是相差1.
--按照vage的书中介绍(P155),在整个全表扫描,整个表的第1行,会单独占用1个逻辑读.这样在2条记录的情况下存在2个逻辑读.
  加上段头1个逻辑读就3个.

--至于为什么,我也不清楚,作者也不清楚.不过可以做一个例子来验证这种情况.

SCOTT@test> create table t3 as select rownum id,'test' data from dual connect by levelTable created.

SCOTT@test> alter table t3 minimize records_per_block;
Table altered.

--这样就实现每块100条记录.

SCOTT@test> insert into t3  select 100+rownum id,'test' data from dual connect by level400 rows created.

SCOTT@test> commit ;
Commit complete.

--这样总共插入100条记录. 正好占满一个1个extents.(我使用assm,前面有L1,L2,以及段头),剩下仅仅5块.

SCOTT@test> select * from dba_extents where owner=user and segment_name='T3';
OWNER  SEGMENT_NAME  SEGMENT_TYPE  TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------------- ------------- ---------------- --------- ---------- ---------- ---------- ---------- ------------
SCOTT  T3            TABLE         USERS                    0          4        536      65536          8            4

--如果我设置array=100,可以想象逻辑读的情况,跨块的算2个逻辑读:
读1,99,1,99,1,99,1,99,1,99,这样在加上块头1个逻辑读(我使用11g的版本),应该是11个逻辑读.

SCOTT@test> @stats t3
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
--注意要分析表,避免动态取样.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8xs59s0kcpn57, child number 0
-------------------------------------
select * from t3
Plan hash value: 4161002650
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     4 (100)|    500 |00:00:00.01 |      11 |
|   1 |  TABLE ACCESS FULL| T3   |      1 |    500 |     4   (0)|    500 |00:00:00.01 |      11 |
-------------------------------------------------------------------------------------------------
13 rows selected.

--可以发现我的猜测一直.

--如果我设置array=98呢?
--读1,98,1 ,98,2, 96,4, 94,6, 92,8,这样在加上块头1个逻辑读(我使用11g的版本),应该是12个逻辑读.

SCOTT@test> set array 98

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8xs59s0kcpn57, child number 0
-------------------------------------
select * from t3
Plan hash value: 4161002650
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     4 (100)|    500 |00:00:00.01 |      12 |
|   1 |  TABLE ACCESS FULL| T3   |      1 |    500 |     4   (0)|    500 |00:00:00.01 |      12 |
-------------------------------------------------------------------------------------------------
13 rows selected.

--如果设置array=101呢?
--读1,99, 2,98, 3,97, 4,96, 5,96这样在加上块头1个逻辑读(我使用11g的版本),应该是11个逻辑读.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8xs59s0kcpn57, child number 0
-------------------------------------
select * from t3
Plan hash value: 4161002650
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     4 (100)|    500 |00:00:00.01 |      11 |
|   1 |  TABLE ACCESS FULL| T3   |      1 |    500 |     4   (0)|    500 |00:00:00.01 |      11 |
-------------------------------------------------------------------------------------------------
13 rows selected.

--如果设置array=200呢?
--读1,99, 100, 1,99 100, 1,99 这样在加上块头1个逻辑读(我使用11g的版本),应该是9个逻辑读.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8xs59s0kcpn57, child number 0
-------------------------------------
select * from t3
Plan hash value: 4161002650
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     4 (100)|    500 |00:00:00.01 |       9 |
|   1 |  TABLE ACCESS FULL| T3   |      1 |    500 |     4   (0)|    500 |00:00:00.01 |       9 |
-------------------------------------------------------------------------------------------------
13 rows selected.

--如果设置array=498呢?
--读1,99, 100, 100 100, 99,1 这样在加上块头1个逻辑读(我使用11g的版本),应该是8个逻辑读.
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8xs59s0kcpn57, child number 0
-------------------------------------
select * from t3
Plan hash value: 4161002650
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     4 (100)|    500 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS FULL| T3   |      1 |    500 |     4   (0)|    500 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------------------
13 rows selected.

--如果设置array=499呢?
--读1,99, 100, 100 100, 100 这样在加上块头1个逻辑读(我使用11g的版本),应该是7个逻辑读.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8xs59s0kcpn57, child number 0
-------------------------------------
select * from t3
Plan hash value: 4161002650
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     4 (100)|    500 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS FULL| T3   |      1 |    500 |     4   (0)|    500 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------------------
13 rows selected.

--再设置大一些,最少也需要7个逻辑读.

总结:
1.在全表扫描是表的第一行自成1批,算1个逻辑读.
2.不知道11g做了什么处理,仅仅读1次段头.
3.理论讲设置大一些array参数,可以减少逻辑读.