[20151222]小表全表扫描为何如此慢.txt
--论坛上有人问的问题，小表全表扫描为何如此慢，200M的大小。链接如下。
http://www.itpub.net/thread-2049088-1-1.html
--我的猜测是可能含有lob字段。自己测试看看：
1.环境：
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> create table t (id number,text clob ) ;
Table created.
SCOTT@book> insert into t select rownum ,lpad('a',4000,'a') from dual connect by level <=2e5;
200000 rows created.
SCOTT@book> exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
2.检查对象：
SCOTT@book> select blocks from dba_tables where owner=user and table_name='T';
BLOCKS
----------
1504
1504*8192/1024/1024=11.75
--我插入的字符4000字节，大于clob所能存放的长度4000-36 ， 也就是blob保存在块外。
--注意因为字符集问题，实际上4000个字符，长度实际是8000个字符。
SCOTT@book> select segment_name from dba_lobs where owner=user and table_name='T';
SEGMENT_NAME
-------------------------
SYS_LOB0000088951C00002$$
SCOTT@book> select header_file,header_block,blocks,extents from dba_segments where segment_name='SYS_LOB0000088951C00002$$' and owner=user;
HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
----------- ------------ ---------- ----------
4 538 204800 208
--204800*8192/1024/1024=1600M，lob类型占用1.6G。
3.测试：
SCOTT@book> set autot traceonly;
SCOTT@book> set timing on
SCOTT@book> select * from t;
ERROR:
ORA-01013: user requested cancel of current operation
26811 rows selected.
Elapsed: 00:10:25.54
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
80436 consistent gets
53624 physical reads
0 redo size
236999434 bytes sent via SQL*Net to client
15148724 bytes received via SQL*Net from client
80435 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26811 rows processed
--时间太长了，等不来这么长时间。我仅仅取了26XXX花了10分钟。
SCOTT@book> select id from t;
200000 rows selected.
Elapsed: 00:00:00.59
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 976K| 410 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| T | 200K| 976K| 410 (1)| 00:00:05 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2437 consistent gets
21 physical reads
0 redo size
1375058 bytes sent via SQL*Net to client
11509 bytes received via SQL*Net from client
1001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200000 rows processed
--如果我仅仅查询id，可以发现不到1秒就完成了。当然对方没有恢复，总之取lob类型就会很慢，特别是在块外。
4.如果在块内呢？
SCOTT@book> set autot off
SCOTT@book> drop table t purge ;
Table dropped.
SCOTT@book> create table t (id number,text clob ) ;
Table created.
SCOTT@book> insert into t select rownum ,lpad('a',982,'a') from dual connect by level <=2e5;
200000 rows created.
SCOTT@book> commit ;
Commit complete.
--分析表。
SCOTT@book> select blocks from dba_tables where owner=user and table_name='T';
BLOCKS
----------
67217
--67217*8192/1024/1024=525M.
SCOTT@book> select segment_name from dba_lobs where owner=user and table_name='T';
SEGMENT_NAME
-------------------------
SYS_LOB0000088957C00002$$
SCOTT@book> select header_file,header_block,blocks,extents from dba_segments where segment_name='SYS_LOB0000088957C00002$$' and owner=user;
HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
----------- ------------ ---------- ----------
4 538 8 1
--lob类型空间占用很少。
SCOTT@book> set timing on
SCOTT@book> set autot traceonly
SCOTT@book> select * from t;
5769 rows selected.
Elapsed: 00:02:52.43
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5774 consistent gets
1987 physical reads
0 redo size
50021192 bytes sent via SQL*Net to client
25872237 bytes received via SQL*Net from client
17308 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5769 rows processed
--时间太长我中断了，依旧很慢。才取了5769条。
SCOTT@book> select id from t;
200000 rows selected.
Elapsed: 00:00:00.81
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 976K| 18220 (1)| 00:03:39 |
| 1 | TABLE ACCESS FULL| T | 200K| 976K| 18220 (1)| 00:03:39 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
67382 consistent gets
66706 physical reads
0 redo size
1375058 bytes sent via SQL*Net to client
11509 bytes received via SQL*Net from client
1001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200000 rows processed
--可以发现不涉及lob类型字段很快。
--这是我以前的测试，如果使用to_char函数在text字段。
SCOTT@book> select id,to_char(text) from t;
200000 rows selected.
Elapsed: 00:00:26.17
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 395M| 18220 (1)| 00:03:39 |
| 1 | TABLE ACCESS FULL| T | 200K| 395M| 18220 (1)| 00:03:39 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
67393 consistent gets
41793 physical reads
0 redo size
2173308 bytes sent via SQL*Net to client
11509 bytes received via SQL*Net from client
1001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200000 rows processed
--仅仅26秒。相对前面块了许多。
5.我转储了其中1块：
Block header dump: 0x0100020b
Object id on Block? Y
seg/obj: 0x15b7d csc: 0x00.1a3111 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000208 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.014.00000da9 0x00c008e9.037c.2d C--- 0 scn 0x0000.001a3110
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0100020b
data_block_dump,data header at 0x7fd2f6047a64
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x7fd2f6047a64
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x80d
avsp=0x7f5
tosp=0x7f5
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x17bf
0x14:pri[1] offs=0xfe6
0x16:pri[2] offs=0x80d
block_row_dump:
tab 0, row 0, @0x17bf
tl: 2009 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 0e
col 1: [2000]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 a5 b5 3a 07 bc 09 00 00
00 00 00 07 ac 00 00 00 00 00 01 00 61 00 61 00 61 00 61 00 61 00 61 00 61
...
61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
LOB
Locator:
Length: 84(2000)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.00.a5.b5.3a
Flags[ 0x02 0x0c 0x80 0x00 ]:
Type: CLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
Inode:
Size: 1980
Flag: 0x09 [ Valid DataInRow ]
Future: 0x00 (should be '0x00')
Blocks: 0
Bytes: 1964
Version: 00000.0000000001
Inline data[1964]
--Storage: BasicFile , 换成securefile看看。注:我写入字符982，实际占用1964. 总长2000.
--也就是在11GR2缺省建立lob类型是BasicFile,而不是securefile类型的。
SCOTT@book> show parameter db_secure
NAME TYPE VALUE
-------------- -------- -----------
db_securefile string PERMITTED
5.换成securefile在测试看看。
SCOTT@book> drop table t purge ;
Table dropped.
SCOTT@book> create table t (id number,text clob ) lob(text) store as securefile;
Table created.
SCOTT@book> insert into t select rownum ,lpad('a',982,'a') from dual connect by level <=1e5;
100000 rows created.
SCOTT@book> commit ;
Commit complete.
--分析表。
SCOTT@book> select blocks from dba_tables where owner=user and table_name='T';
BLOCKS
----------
33557
SCOTT@book> select * from t where rownum<=2000;
2000 rows selected.
Elapsed: 00:00:58.30
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 4037K| 183 (0)| 00:00:03 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 2000 | 4037K| 183 (0)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=2000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2004 consistent gets
80 physical reads
0 redo size
17312414 bytes sent via SQL*Net to client
8950520 bytes received via SQL*Net from client
6002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
--也是很慢。
SCOTT@book> select id,to_char(text) from t where rownum<=2000;
2000 rows selected.
Elapsed: 00:00:00.30
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 4037K| 183 (0)| 00:00:03 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 2000 | 4037K| 183 (0)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=2000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
678 consistent gets
0 physical reads
0 redo size
22137 bytes sent via SQL*Net to client
619 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
--搞不懂为什么这样会快。
