[20151222]小表全表扫描为何如此慢.txt

  1. 云栖社区>
  2. 博客>
  3. 正文

[20151222]小表全表扫描为何如此慢.txt

lfreeali 2015-12-22 09:44:13 浏览693
展开阅读全文

[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

--搞不懂为什么这样会快。

网友评论

登录后评论
0/500
评论
lfreeali
+ 关注