2台机器一样,9.2.0.4
pga自动管理,大小都为200m.
这么说,所有的库包过机器一样,参数也一样。
但是同一个查询速度相差6秒,通过10046发现差距在sort阶段, 于是通过10032跟踪sort的信息。
排序慢的:
sort_area_size
458752 -----------------只有4K
sort_area_retained_size 458752
sort_multiblock_read_count7
max intermediate merge width2
---- Sort Statistics ------------------------------
Initial runs
847
Intermediate runs
843
Number of merges
844
Input records
544189
Output records
268870
Disk blocks 1st pass
4404
Total disk blocks used
6144
Total number of comparisons performed 7921081
Comparisons performed by in-memory sort 4388433
Comparisons performed during merge3532648
Temp segments allocated
1
Extents allocated
48
Total number of exceptions fired
49563
Fired during input
6146
Fired during merge/output
43417
Initial memory discarded for exceptions 80
---- Run Directory Statistics ----
Run directory block reads (buffer cache)2703
Block pins (for run directory)
2
Block repins (for run directory)
2701
排序快的:
sort_area_size
2031616 ----------------2M
sort_area_retained_size 2031616
sort_multiblock_read_count31
max intermediate merge width2
---- Sort Statistics ------------------------------
Initial runs
3
Number of merges
1
Input records
544189
Output records
126456
Disk blocks 1st pass
1046
Total disk blocks used
1048
Total number of comparisons performed 8099224
Comparisons performed by in-memory sort 8089786
Comparisons performed during merge9438
Temp segments allocated
1
Extents allocated
9
Total number of exceptions fired
66039
Fired during input
65965
Fired during merge/output
74
Initial memory discarded for exceptions 149992
---- Run Directory Statistics ----
Run directory block reads (buffer cache)4
Block pins (for run directory)
1
Block repins (for run directory)
3