Enqueue activity for DB: DB Instance: aaa Snaps: 2845 -2848
-> ordered by waits desc, gets desc
Enqueue Gets Waits
---------- ------------ ----------
ST 1,554 1,554
-------------------------------------------------------------
看到主要队列等待在等待ST锁定，对于DMT，我们说这个等待和FET$、UET$的争用紧密相关。再回过头来研究捕获SQL语句：
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
4,800,073 10,268 467.5 51.0 2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
803,187 10,223 78.6 8.5 528349613
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a
nd ext#=:4
454,444 10,300 44.1 4.8 1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4
23,110 10,230 2.3 0.2 3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
21,201 347 61.1 0.2 1705880752
select file# from file$ where ts#=:1
….
9,505 12 792.1 0.1 1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
6,426 235 27.3 0.1 1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
可以看到数据库频繁操作UET$、FET$系统表已经成为了系统的主要瓶颈。
至此，已经可以准确地为该系统定位问题，相应的解决方案也很容易确定，在Oracle 8.1.7中使用LMT代替DMT，这是解决问题的根本办法，当然实施起来还要进行综合考虑，实际情况还要复杂得多。
- The End -