[20151228]小表全表扫描为何如此慢2.txt
--论坛上有人问的问题，小表全表扫描为何如此慢，200M的大小。链接如下。
http://www.itpub.net/thread-2049088-1-1.html
--我的猜测是可能含有lob字段，不过对方的恢复没有lob字段。仔细检查发现array使用缺省值，zergduan，bfc99观察都比我细致。
--拿例子sh.sales测试看看。
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show array
arraysize 15
SCOTT@book> set autot traceonly
SCOTT@book> set timing on
SCOTT@book> select * from sh.sales;
918843 rows selected.
Elapsed: 00:00:19.60
Execution Plan
----------------------------------------------------------
Plan hash value: 1550251865
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| 525 (2)| 00:00:07 | | |
| 1 | PARTITION RANGE ALL| | 918K| 25M| 525 (2)| 00:00:07 | 1 | 28 |
| 2 | TABLE ACCESS FULL | SALES | 918K| 25M| 525 (2)| 00:00:07 | 1 | 28 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
62795 consistent gets
1625 physical reads
0 redo size
36919530 bytes sent via SQL*Net to client
674335 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
918843 rows processed
--表大小25M，需要19秒。
SCOTT@book> set array 200
SCOTT@book> select * from sh.sales;
918843 rows selected.
Elapsed: 00:00:12.59
Execution Plan
----------------------------------------------------------
Plan hash value: 1550251865
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 25M| 525 (2)| 00:00:07 | | |
| 1 | PARTITION RANGE ALL| | 918K| 25M| 525 (2)| 00:00:07 | 1 | 28 |
| 2 | TABLE ACCESS FULL | SALES | 918K| 25M| 525 (2)| 00:00:07 | 1 | 28 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6218 consistent gets
1619 physical reads
0 redo size
26550384 bytes sent via SQL*Net to client
51053 bytes received via SQL*Net from client
4596 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
918843 rows processed
--设置array=200, 需要13秒，快了50%。行记录918843/网络往返4596=199.9223237597911227154,基本吻合。
--再来看看包含lob字段的情况：
SCOTT@book> create table t (id number,text clob ) ;
Table created.
SCOTT@book> insert into t select rownum ,lpad('a',40,'a') from dual connect by level <=1e4;
10000 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> set array 2
SCOTT@book> select id from t ;
SCOTT@book> show array
arraysize 200
SCOTT@book> set autot traceonly
SCOTT@book> set timing on
SCOTT@book> select * from t;
10000 rows selected.
Elapsed: 00:04:51.68
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1826K| 68 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 10000 | 1826K| 68 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
10031 consistent gets
0 physical reads
0 redo size
11160334 bytes sent via SQL*Net to client
7170519 bytes received via SQL*Net from client
30002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
--改用to_char函数。
SCOTT@book> select id,to_char(text) from t ;
10000 rows selected.
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1826K| 68 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 10000 | 1826K| 68 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
239 consistent gets
0 physical reads
0 redo size
99483 bytes sent via SQL*Net to client
1058 bytes received via SQL*Net from client
51 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
--很明显第2次快许多。仔细检查网络往返就很容易明白问题所在。
--第2次51次很好解读，10000条记录，array=200, 相等于50次就可以完成，51次基本复合测试结果。换1句话讲，当使用to_char函数时
--oracle是先"打包好"数据在上传的。
--而当存在lob字段时，出现30002次往返，我估计oracle是1条记录1条记录的上传，每条记录3个往返，导致整个效率低下。
--测试取1条与2条记录就可以看出问题：
SCOTT@book> select * from t where rownum<=1;
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 187 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 1 | 187 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1648 bytes sent via SQL*Net to client
1236 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SCOTT@book> select * from t where rownum<=2;
Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 374 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 2 | 374 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
2764 bytes sent via SQL*Net to client
1953 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
-- SQL*Net roundtrips to/from client =5,8 也说明取1条记录需要3次网络往返，逻辑读仅仅增加1次。
-- 另外oracle传输数据实际是压缩的。
-- 如果使用to_char函数
--99483/50/200=9.9483，平均每条记录仅仅10个字节，比实际小许多，也说明数据是压缩传送的。
-- 如果不使用to_char函数
--11160334/30002=371.9863342443837775281
--真不知道平均每次为什么这么多。估计考虑网络协议的开销，真正的数据并没有占多少。