说明：老白将整理一些以前的案例，在子衿技术论坛和大家分享。这些案例可能有些已经是几年前，甚至十年前的案例。老白将会对这些案例进行重新的梳理，有些案例当年的处理可能不是最佳的，老白也会做相关的点评。
SQL> set autotrace traceonly
SQL> set timi on
select * from crm.prod_2_pp where rownum<20;
SQL>
19 rows selected.Elapsed: 00:00:10.86
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE(Cost=14106 Card=19 Bytes=
1140)1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL)OF 'PROD_2_PP' (Cost=14106 Card=1494
0367 Bytes=896422020)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
114669 consistent gets
24192 physical reads
0 redo size
2186 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed
ALTER SESSION SETEVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12';
EXEC #1:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1594195875947
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 7088 p1=5 p2=2166946 p3=1
WAIT #1: nam='db file scattered read' ela= 6050 p1=8 p2=2127534 p3=2
WAIT #1: nam='db file sequential read' ela= 7956 p1=9 p2=2184752 p3=1
WAIT #1: nam='db file sequential read' ela= 4019 p1=9 p2=2184754 p3=1
WAIT #1: nam='db file sequential read' ela= 6380 p1=10 p2=2171339 p3=1
WAIT #1: nam='db file sequential read' ela= 10536 p1=11 p2=2204928 p3=1
WAIT #1: nam='db file sequential read' ela= 4729 p1=12 p2=2177987 p3=1
WAIT #1: nam='db file scattered read' ela= 5464 p1=12 p2=2177997 p3=2
WAIT #1: nam='db file scattered read' ela= 3297 p1=12 p2=2178000 p3=2
WAIT #1: nam='db file sequential read' ela= 4436 p1=12 p2=2178003 p3=1
WAIT #1: nam='db file sequential read' ela= 8271 p1=12 p2=2178273 p3=1
WAIT #1: nam='db file scattered read' ela= 3510 p1=12 p2=2178275 p3=3
WAIT #1: nam='db file sequential read' ela= 7320 p1=12 p2=2178520 p3=1
WAIT #1: nam='db file sequential read' ela= 3582 p1=12 p2=2178523 p3=1
WAIT #1: nam='db file sequential read' ela= 7716 p1=13 p2=2195396 p3=1
WAIT #1: nam='db file sequential read' ela= 5913 p1=13 p2=2195708 p3=1
WAIT #1: nam='db file sequential read' ela= 11884 p1=13 p2=2195786 p3=1
WAIT #1: nam='db file sequential read' ela= 15995 p1=13 p2=2195788 p3=1
WAIT #1: nam='db file sequential read' ela= 2489 p1=13 p2=2196301 p3=1
WAIT #1: nam='db file sequential read' ela= 6880 p1=14 p2=2194911 p3=1
WAIT #1: nam='db file sequential read' ela= 19473 p1=14 p2=2194914 p3=1
WAIT #1: nam='db file sequential read' ela= 6681 p1=14 p2=2195024 p3=1
WAIT #1: nam='db file sequential read' ela= 5100 p1=14 p2=2195044 p3=1
WAIT #1: nam='db file sequential read' ela= 6560 p1=14 p2=2195091 p3=1
WAIT #1: nam='db file scattered read' ela= 8772 p1=14 p2=2195259 p3=2
WAIT #1: nam='db file sequential read' ela= 7552 p1=14 p2=2195418 p3=1
WAIT #1: nam='db file sequential read' ela= 4918 p1=14 p2=2195424 p3=1
WAIT #1: nam='db file sequential read' ela= 3781 p1=14 p2=2195427 p3=1
WAIT #1: nam='db file scattered read' ela= 10503 p1=14 p2=2195492 p3=4
WAIT #1: nam='db file scattered read' ela= 3875 p1=14 p2=2195497 p3=5
WAIT #1: nam='db file scattered read' ela= 5524 p1=14 p2=2195539 p3=2
WAIT #1: nam='db file scattered read' ela= 1467 p1=15 p2=2204992 p3=6
WAIT #1: nam='db file sequential read' ela= 5435 p1=15 p2=2205167 p3=1
WAIT #1: nam='db file scattered read' ela= 884 p1=15 p2=2205180 p3=3
WAIT #1: nam='db file scattered read' ela= 903 p1=15 p2=2205184 p3=2
WAIT #1: nam='db file sequential read' ela= 242 p1=15 p2=2205276 p3=1
WAIT #1: nam='db file sequential read' ela= 5802 p1=15 p2=2205294 p3=1
alter system dump datafile 15 blockmin 2205428 block max 2205432;
ntab=1
nrow=111
frre=0
fsbo=0xf0
fseo=0x44e
avsp=0x1ea8
tosp=0x1ea8
0xe:pti[0] nrow=111 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=2
0x16:pri[2] sfll=3
0x18:pri[3] sfll=4
0x1a:pri[4] sfll=5
0x1c:pri[5] sfll=6
0x1e:pri[6] sfll=7
0x20:pri[7] sfll=8
0x22:pri[8] sfll=9
0x24:pri[9] sfll=10
0x26:pri[10] sfll=11
0x28:pri[11] sfll=12
0x2a:pri[12] sfll=13
0x2c:pri[13] sfll=14
0x2e:pri[14] sfll=15
0x30:pri[15] sfll=16
0x32:pri[16] sfll=17
0x34:pri[17] sfll=18
0x36:pri[18] sfll=19
0x38:pri[19] sfll=20
0x3a:pri[20] sfll=21
0x3c:pri[21] sfll=22
0x3e:pri[22] sfll=23
0x40:pri[23] sfll=24
0x42:pri[24] sfll=25
0x44:pri[25] sfll=26
0x46:pri[26] sfll=27
0x48:pri[27] sfll=28
0x4a:pri[28] sfll=29
0x4c:pri[29] sfll=30
0x4e:pri[30] sfll=31
0x50:pri[31] sfll=32
0x52:pri[32] sfll=33
0x56:pri[34] sfll=35
0x58:pri[35] sfll=36
0x5a:pri[36] sfll=37
0x5c:pri[37] sfll=38
0x5e:pri[38] sfll=39
0x60:pri[39] sfll=40
0x62:pri[40] sfll=41
0x64:pri[41] sfll=42
0x66:pri[42] sfll=43
0x68:pri[43] sfll=44
0x6a:pri[44] sfll=45
0x6c:pri[45] sfll=46
0x6e:pri[46] sfll=47
0x70:pri[47] sfll=48
0x72:pri[48] sfll=49
0x74:pri[49] sfll=50
0x76:pri[50] sfll=51
0x78:pri[51] sfll=52
0x7a:pri[52] sfll=53
0x7c:pri[53] sfll=54
0x7e:pri[54] sfll=55
0x80:pri[55] sfll=56
0x82:pri[56] sfll=57
0x84:pri[57] sfll=58
0x86:pri[58] sfll=59
0x88:pri[59] sfll=60
0x8a:pri[60] sfll=61
0x8c:pri[61] sfll=62
0x8e:pri[62] sfll=63
0x90:pri[63] sfll=64
0x92:pri[64] sfll=65
0x94:pri[65] sfll=66
0x96:pri[66] sfll=67
0x98:pri[67] sfll=68
0x9a:pri[68] sfll=69
0x9c:pri[69] sfll=70
0x9e:pri[70] sfll=71
0xa0:pri[71] sfll=72
0xa2:pri[72] sfll=73
0xa4:pri[73] sfll=74
0xa6:pri[74] sfll=75
0xa8:pri[75] sfll=76
0xaa:pri[76] sfll=77
0xac:pri[77] sfll=78
0xae:pri[78] sfll=79
0xe:pti[0] nrow=111 ，说明这个数据块中有111条记录，但是这些行都是空行，至此这个问题已经十分明确了。
案例点评：这类的问题，用户觉得慢，首先要确认是否真的慢，通过AUTOTRACE是比较好的做法。通过buffer get执行指标可以进行初步判断这条SQL有没有问题。同样10046 trace里的等待事件也有助于我们定位类似问题。当然如果我们认为表的前部有空块，最直接的验证方式就是找几个块DUMP出来看看。
