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