[20150309]sqlplus set array最小2.txt

--上午做测试发现1个问题，设置array=1是无效的，在sqlplus下set array最小是2.自己做一个人测试：

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

SCOTT@test> CREATE  TABLE tx AS SELECT ROWNUM c2 FROM DUAL CONNECT BY LEVEL Table created.

SCOTT@test> show array
arraysize 200

$ cat ax.sql
set term off
select * from t2;
set term on

--这样避免输出到屏幕。

2.开始测试：
SCOTT@test> show array
arraysize 200

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

SCOTT@test> @ax
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 |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     2 (100)|          |    100 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS FULL| T2   |      1 |    100 |   300 |     2   (0)| 00:00:01 |    100 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------
--array=200,逻辑读是3，块头读1次，第1条记录1次，剩下99条1次，共3个逻辑读。

3.array=2:

SCOTT@test> set array 2
SCOTT@test> @ax
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 |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     2 (100)|          |    100 |00:00:00.01 |      52 |
|   1 |  TABLE ACCESS FULL| T2   |      1 |    100 |   300 |     2   (0)| 00:00:01 |    100 |00:00:00.01 |      52 |
--------------------------------------------------------------------------------------------------------------------
--array=2 ,逻辑读是52，块头读1次，第1条记录1次，剩下99条共50次，共52个逻辑读。

4.如果我设置array=1.
SCOTT@test> set array 1
SCOTT@test> @ax
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 |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     2 (100)|          |    100 |00:00:00.01 |      52 |
|   1 |  TABLE ACCESS FULL| T2   |      1 |    100 |   300 |     2   (0)| 00:00:01 |    100 |00:00:00.01 |      52 |
--------------------------------------------------------------------------------------------------------------------

--可以发现array=1与array=2的逻辑读是一样的，也就是将设置这个参数array最小是2.