粗略看了一下
RBS NoSegment SizeAvg ActiveOptimal SizeMaximum Size
------ --------------- --------------- --------------- ---------------
0 401,408 8,19220,971,520 401,408
1221,086,20821,787,01120,971,520 143,433,728
1321,086,208 7,000,46820,971,520 143,433,728
1421,086,20839,166,63720,971,520 147,652,608
1521,086,208 4,222,57920,971,520 130,777,088
1621,086,20831,696,34220,971,520 143,433,728
1721,086,208 4,346,45220,971,520 151,871,488
回滚段 Optimal Size 参数的值应该大于Avg Active
BufferHit %: 71.59太低,要加大data buffer最好达到90%以上
share pool 居然只有30m, 奇怪的是命中率还挺高,是不是平常的操作很单一啊,不管如何也该设为150+
log_buffer
32768改为256k
job_queue_processes 6 改为 4
enqueue avg wait 279 证明表建的不合理,常见是外键未加索引,或者extent设置太小
sort_area_size629145600 夸张了,基本就不用tmp表空间,占用太多物理内存
根据你的应用是在nt下,我想数据量应该不会很大,但是I/O确这么严重,而且主要查询,但是indx和data读取比例却不到1%,请检查你的索引是否建的好,如果索引建得好data buffer也不用设置太高
DATA_L01 Reads 6,077,074
INDX_L01 Reads43,800
|