今天开发组的同事找到我，说查一张小表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'
sel