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