都是大字段clob设计不合理惹的祸

  今天开发组的同事找到我，说查一张小表4587条记录，18个字段需要12s，简直不敢相信。

SQL> select * from  cfms_questions;

已选择4587行。

已用时间:  00: 00: 11.52

执行计划

----------------------------------------------------------

Plan hash value:3912515109

------------------------------------------------------------------------------------

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                |  3804 | 1329K|    68   (0)| 00:00:01 |

|   1 | TABLE ACCESS FULL| CFMS_QUESTIONS | 3804 |  1329K|    68  (0)| 00:00:01 |

------------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

          1 recursive calls

          1 db block gets

       4637 consistent gets

          4 physical reads

          0 redo size

    4300934 bytes sent via SQL*Net to client

    2387052 bytes received via SQL*Net from client

      13798 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

       4587 rows processed

遇到这种问题，只有求助于神器10046。

SQL> alter session set events '10046 trace name context forever ,level 12' ;
会话已更改。
已用时间:  00: 00: 00.01
SQL> select * from cfms_questions;
已选择4587行。
已用时间:  00: 00: 12.27
执行计划
----------------------------------------------------------
Plan hash value: 3912515109
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |  3804 |  1329K|    68   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CFMS_QUESTIONS |  3804 |  1329K|    68   (0)| 00:00:01 |
------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       4637  consistent gets
          4  physical reads
          0  redo size
    4300934  bytes sent via SQL*Net to client
    2387052  bytes received via SQL*Net from client
      13798  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4587  rows processed
SQL> alter session set events '10046 trace name context off' ;

trace的文件经过tkprof格式化以后，发现没有问题啊，挺快的，没有发现问题：

select * 
from
 cfms_questions
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4588      0.28       0.37          0       4633          1        4587
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4590      0.28       0.38          0       4633          1        4587
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 96  
Rows     Row Source Operation
-------  ---------------------------------------------------
   4587  TABLE ACCESS FULL CFMS_QUESTIONS (cr=4633 pr=0 pw=0 time=111289 us cost=68 size=1361832 card=3804)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    4589        0.00          0.02
  SQL*Net message from client                  4589        0.00          3.18
  SQL*Net more data to client                    11        0.00          0.00
  SQL*Net more data from client                   8        0.00          0.00
********************************************************************************

看trace的原始文件,发现有很多的LOBREAD，这说明表里面有大字段啊，再来做一个实验：

PARSING IN CURSOR #707451568 len=28 dep=0 uid=96 oct=3 lid=96 tim=53142591824 hv=1200569272 ad='2a310f8d0' sqlid='6ncj3q53syfxs'
select * from cfms_questions
END OF STMT
PARSE #707451568:c=0,e=10817,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3912515109,tim=53142591819
EXEC #707451568:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3912515109,tim=53142592040
..............................................................

.............................................................
LOBREAD: c=0,e=49,p=0,cr=0,cu=0,tim=53142594276
WAIT #0: nam='SQL*Net message from client' ela= 656 driver id=1413697536 #bytes=1 p3=0 obj#=132667 tim=53142594989
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=132667 tim=53142595099
LOBREAD: c=0,e=68,p=0,cr=0,cu=0,tim=53142595131
.............................................................
LOBREAD: c=0,e=65,p=0,cr=0,cu=0,tim=53142596914

去掉大字段后查全表，非常快：

SQL> select ID,
  2         VERSION_ID,
  3         MODULE_ID,
  4         STATE,
  5         CATA,
  6         CREATOR_ID,
  7         DISCOVER_TIME,
  8         PS_ONLINE_VERSION,
  9         PS_SOLVE_TIME,
 10         CREATOR,
 11         RESULT,
 12         CONTACT_WAY,
 13         RESOLVE_TIME,
 14         DEPARTMENT_ID,
 15         SERVICE_ID,
 16         SYSTEM_ID
 17    from CFMS_QUESTIONS;
已选择4587行。
已用时间:  00: 00: 00.34
执行计划
----------------------------------------------------------
Plan hash value: 3912515109
-----------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |  3804 |   631K|    68   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CFMS_QUESTIONS |  3804 |   631K|    68   (0)| 00:00:01 |
------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        550  consistent gets
          0  physical reads
          0  redo size
     168413  bytes sent via SQL*Net to client
       3693  bytes received via SQL*Net from client
        307  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4587  rows processed

加上大字段后查全表，打回原形：
SQL> select ID,
  2         VERSION_ID,
  3         MODULE_ID,
  4         STATE,
  5         CATA,
  6         CREATOR_ID,
  7         DISCOVER_TIME,
  8         PS_ONLINE_VERSION,
  9         PS_SOLVE_TIME,
 10         CONTENT,
 11         CREATOR,
 12         RESULT,
 13         CONTACT_WAY,
 14         RESOLVE_TIME,
 15         DEPARTMENT_ID,
 16         SERVICE_ID,
 17         SYSTEM_ID
 18    from CFMS_QUESTIONS;
已选择4587行。
已用时间:  00: 00: 10.59
执行计划
----------------------------------------------------------
Plan hash value: 3912515109
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |  3804 |  1329K|    68   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CFMS_QUESTIONS |  3804 |  1329K|    68   (0)| 00:00:01 |
------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       4637  consistent gets
          4  physical reads
          0  redo size
    4300934  bytes sent via SQL*Net to client
    2387052  bytes received via SQL*Net from client
      13798  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4587  rows processed

  结论：本次的性能问题的原因是大字段读取引起，在设计表的时候，大字段要与主表分离。