- 最后登录
- 2022-10-11
- 在线时间
- 137 小时
- 威望
- 163
- 金钱
- 1477
- 注册时间
- 2012-1-10
- 阅读权限
- 50
- 帖子
- 217
- 精华
- 1
- 积分
- 163
- UID
- 158
|
1#
发表于 2012-4-19 14:00:46
|
查看: 7969 |
回复: 8
Oracle 10.2.0.1版本,查询v$session_wait 发现大量的buffer busy wait 与latch: cache buffers chains,
还有不了解的undo global data。- SQL> SELECT event,count(*) FROM v$session_wait WHERE wait_class<>'Idle' group by event order by 2 desc ;
-
- EVENT COUNT(*)
- ---------------------------------------------------------------- ----------
- buffer busy waits 235
- latch: undo global data 45
- latch: cache buffers chains 41
- latch: library cache 5
- db file sequential read 4
- enq: TX - row lock contention 2
- latch: row cache objects 1
- SQL*Net message to client 1
复制代码
通过跟踪发现,个人觉得就是应该这两条语句
INSERT INTO Vlan(DeviceID,VlanID,VlanName,VlanType,VlanStatus,ChangeType) VALUES(:p1,:p2,:p3,:p4,:p5,:p6);
INSERT INTO VlanPort(DeviceID,VlanID,PortDescr,ChangeType) VALUES(:p1,:p2,:p3,:p4);
产生了大量上述等待.
那么想问问为什么 就是这么简单,但每小时有4万次的insert 会造成这么严重的buffer busy wait .
难道,是由于同时发生大量的物理读而引起的?
还是有其他的原因
- SQL> select a.event,c.SQL_TEXT,count(*)
- 2 from ( SELECT sid,event FROM v$session_wait WHERE wait_class<>'Idle' ) a
- 3 ,v$session b,v$sql c
- 4 where a.sid=b.sid and b.SQL_ID=c.sql_id
- 5 group by a.event,c.SQL_TEXT
- 6 /
-
- EVENT SQL_TEXT COUNT(*)
- --------------------------------- -------------------------------------------------------------------------------- ----------
- buffer busy waits INSERT INTO VlanPort(DeviceID,VlanID,PortDescr,ChangeType) 298
- buffer busy waits INSERT INTO Vlan(DeviceID,VlanID,VlanName,VlanType,VlanStatus,ChangeType) 16
- latch: shared pool UPDATE ALARMSUMMARY SET LASTOCCURTIME = :B6 , TIMES = TIMES + :B4 - NVL (:B5 , 0 1
- latch: library cache update nco_status set summary=:p1,tally=tally+1,lastoccurrence=to_date(:p2,'yyyy 1
- latch: library cache select a.event,c.SQL_TEXT,count(*) from ( SELECT sid,event FROM v$session_wai 1
- db file scattered read delete from ResMoniHourlyInfo R WHERE R.HourID='2012041913' 1
- db file scattered read select f.circuitid, f.inavgvec, f.outavgvec from fluxh f where f.fluxtime = t 2
- db file scattered read insert into ERRCIRCUIT(CIRCUITID,CIRCUITNAME,DEVICENAME,AINTDESCR,CONFSTATUS,IFO 1
- db file sequential read INSERT INTO Flux(CIRCUITID,FLUXTIME,INTERVAL,INAVGVEC,OUTAVGVEC) VALUES(:p1,:p2, 1
- db file sequential read select count(*) from (select distinct circuitid from flux where fluxtime > to_ch 1
- db file sequential read SELECT sumalarmid,alarmtypeid,resid,respara,resentityclassid, 1
- latch: undo global data INSERT INTO VlanPort(DeviceID,VlanID,PortDescr,ChangeType) 134
- latch: undo global data INSERT INTO Vlan(DeviceID,VlanID,VlanName,VlanType,VlanStatus,ChangeType) 54
- latch: cache buffers chains INSERT INTO VlanPort(DeviceID,VlanID,PortDescr,ChangeType) 14
- latch: cache buffers chains select f.circuitid, f.inavgvec, f.outavgvec from fluxh f where f.fluxtime = t 1
- latch: cache buffers chains INSERT INTO Vlan(DeviceID,VlanID,VlanName,VlanType,VlanStatus,ChangeType) 8
- enq: TX - row lock contention INSERT INTO ALARMSUMMARY (SUMALARMID, ALARMTYPEID, RESID, RESPARA, STARTTIME, LA 1
-
复制代码 并上传一份awr报告,极高的db_time ,操作系统负载及高 solaris 16core 他的负载会到如下
load averages: 63.3, 62.3, 62.0;
843 processes: 756 sleeping, 69 running, 1 zombie, 17 on cpu
awrrpt_1_42807_42808.html
(347.32 KB, 下载次数: 760)
[ 本帖最后由 不了峰 于 2012-4-19 14:02 编辑 ] |
|