频繁insert一张表会产生大量的buffer busy waits吗？

不了峰

Oracle 10.2.0.1版本，查询v$session_wait 发现大量的buffer busy wait 与latch: cache buffers chains,
还有不了解的undo global data。

1. SQL> SELECT event,count(*) FROM v$session_wait WHERE wait_class<>'Idle' group by event order by 2 desc ;
   
2. 
   
3. EVENT                                                              COUNT(*)
   
4. ---------------------------------------------------------------- ----------
   
5. buffer busy waits                                                       235
   
6. latch: undo global data                                                  45
   
7. latch: cache buffers chains                                              41
   
8. latch: library cache                                                      5
   
9. db file sequential read                                                   4
   
10. enq: TX - row lock contention                                             2
    
11. latch: row cache objects                                                  1
    
12. 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 .
难道，是由于同时发生大量的物理读而引起的？
还是有其他的原因

1. SQL> select a.event,c.SQL_TEXT,count(*)
   
2.   2    from ( SELECT sid,event FROM v$session_wait WHERE wait_class<>'Idle' ) a
   
3.   3  ,v$session b,v$sql  c
   
4.   4  where a.sid=b.sid and b.SQL_ID=c.sql_id
   
5.   5  group by a.event,c.SQL_TEXT
   
6.   6  /
   
7. 
   
8. EVENT                             SQL_TEXT                                                                           COUNT(*)
   
9. --------------------------------- -------------------------------------------------------------------------------- ----------
   
10. buffer busy waits                 INSERT INTO VlanPort(DeviceID,VlanID,PortDescr,ChangeType)                              298
    
11. buffer busy waits                 INSERT INTO Vlan(DeviceID,VlanID,VlanName,VlanType,VlanStatus,ChangeType)                16
    
12. latch: shared pool                UPDATE ALARMSUMMARY SET LASTOCCURTIME = :B6 , TIMES = TIMES + :B4 - NVL (:B5 , 0          1
    
13. latch: library cache              update nco_status set summary=:p1,tally=tally+1,lastoccurrence=to_date(:p2,'yyyy          1
    
14. latch: library cache               select a.event,c.SQL_TEXT,count(*)   from ( SELECT sid,event FROM v$session_wai          1
    
15. db file scattered read            delete from ResMoniHourlyInfo R WHERE R.HourID='2012041913'                               1
    
16. db file scattered read            select f.circuitid, f.inavgvec, f.outavgvec   from fluxh f  where f.fluxtime = t          2
    
17. db file scattered read            insert into ERRCIRCUIT(CIRCUITID,CIRCUITNAME,DEVICENAME,AINTDESCR,CONFSTATUS,IFO          1
    
18. db file sequential read           INSERT INTO Flux(CIRCUITID,FLUXTIME,INTERVAL,INAVGVEC,OUTAVGVEC) VALUES(:p1,:p2,          1
    
19. db file sequential read           select count(*) from (select distinct circuitid from flux where fluxtime > to_ch          1
    
20. db file sequential read           SELECT sumalarmid,alarmtypeid,resid,respara,resentityclassid,                             1
    
21. latch: undo global data           INSERT INTO VlanPort(DeviceID,VlanID,PortDescr,ChangeType)                              134
    
22. latch: undo global data           INSERT INTO Vlan(DeviceID,VlanID,VlanName,VlanType,VlanStatus,ChangeType)                54
    
23. latch: cache buffers chains       INSERT INTO VlanPort(DeviceID,VlanID,PortDescr,ChangeType)                               14
    
24. latch: cache buffers chains       select f.circuitid, f.inavgvec, f.outavgvec   from fluxh f  where f.fluxtime = t          1
    
25. latch: cache buffers chains       INSERT INTO Vlan(DeviceID,VlanID,VlanName,VlanType,VlanStatus,ChangeType)                 8
    
26. 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, 下载次数: 718)

2012-4-19 14:00 上传

点击文件名下载附件

[ 本帖最后由 不了峰 于 2012-4-19 14:02 编辑 ]

不了峰

VLAN  表有6个字段，有一个主键 (DEVICEID, VLANID, CHANGETYPE) 表大小100MB，索引大小85MB

VlanPort 表有四个字段，有一个主键 ( DEVICEID, VLANID, CHANGETYPE, PORTDESCR )  
并且有一个外键 关联 vlan的主键    表大小 240MB,索引大小450MB

外键没有建索引，，难道是这个问题，，但是都是insert 操作，应该不是没有建索引的原因。

这个系统的sga_target=0 ..db_cache_size=2G,shared_pool_size=2G

[ 本帖最后由 不了峰 于 2012-4-19 14:36 编辑 ]

cnaning

想分析awr报告，不想下载，嗨

cnaning

哈哈，可以下载了，我觉得是大量并发插入，导致索引分裂造成的，另外你有delete操作，造成undo latch也是必然的

不了峰

"大量并发插入，导致索引分裂造成的"

这个能具体讲讲吗？

Maclean Liu(刘相兵

Event        Waits        Time(s)        Avg Wait(ms)        % Total Call Time        Wait Class
buffer busy waits        6,793,343        607,524        89        52.2        Concurrency
latch: undo global data        4,104,131        253,198        62        21.8        Other
latch: cache buffers chains        1,334,748        89,938        67        7.7        Concurrency
CPU time                 49,567                 4.3         
latch free        480,262        26,992        56        2.3        Other

buffer busy waits 是主要性能瓶颈 平均 89ms


Redo size:        527,935.49        12,142.70
Logical reads:        1,409,326.02        32,414.98
Block changes:        3,458.25        79.54


每秒逻辑读 达到 1,409,326.02 = 11010 MB   从这一点来看 并发逻辑访问存在瓶颈很正常



Segments by Buffer Busy Waits

    % of Capture shows % of Buffer Busy Waits for each top segment compared
    with total Buffer Busy Waits for all segments captured by the Snapshot

Owner        Tablespace Name        Object Name        Subobject Name        Obj. Type        Buffer Busy Waits        % of Capture
FJSLVIEW         DATACFG         VLANPORT                   TABLE         4,091,048         61.87
FJSLVIEW         DATACFG         VLAN                   TABLE         2,507,333         37.92
FJSLVIEW         DATACFG         PPINFO                   TABLE         4,164         0.06
FJSLVIEW         DATACFG         PPEXTINFO                   TABLE         3,536         0.05
FJSLVIEW         DATACFG         PORTINFO                   TABLE         2,670         0.04


主要引发  Buffer Busy  的 segment 是  VLANPORT          和  VLAN         这2张表



SQL ordered by Gets

    Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    Total Buffer Gets: 5,097,482,882
    Captured SQL account for 96.6% of Total

Buffer Gets         Executions         Gets per Exec         %Total        CPU Time (s)        Elapsed Time (s)        SQL Id        SQL Module        SQL Text
3,150,055,622         40,174         78,410.31         61.80         25290.91         650605.58         6cuc59szy3tp8         perl@nmscol1.fz.fjtelecom (TNS V1-V3)         INSERT INTO VlanPort(DeviceID,...
1,601,715,201         34,726         46,124.38         31.42         13408.75         412363.78         bmbmrmyj9mvg6         perl@nmscol1.fz.fjtelecom (TNS V1-V3)         INSERT INTO Vlan(DeviceID, Vla...



对应的语句 是逻辑读最高的 2条：

        INSERT INTO VlanPort(DeviceID, VlanID, PortDescr, ChangeType) VALUES(:p1, :p2, :p3, :p4)
       INSERT INTO Vlan(DeviceID, VlanID, VlanName, VlanType, VlanStatus, ChangeType) VALUES(:p1, :p2, :p3, :p4, :p5, :p6)


当多个进程并发地 向同一个块 INSERT插入数据时 ， 可能引发 buffer busy waits 和 latch cbc 等待事件。

且这里可以观察到 引发 buffer busy wait的并不是 INDEX 对象。

建议考虑 采用 RANGE-HASH 或 HASH 分区的方式 打散数据的分布 来避免 由于频繁的 DML造成的 buffer busy waits

不了峰

感谢
有点不能理解  
“每秒逻辑读 达到 1,409,326.02 = 11010 MB   从这一点来看 并发逻辑访问存在瓶颈很正常 ”
并从下面 SQL ordered by Gets 的top2来看，

为什么 insert 会产生这么大的逻辑读？
是由于并发的原因？

Maclean Liu(刘相兵

为什么 insert 会产生这么大的逻辑读？


这需要进一步诊断

单次  Gets per Exec   达到 78,410.31 确实有些惊人


首先确认该 SQL  6cuc59szy3tp8 的执行计划， VlanPort 表的相关索引的状态


进一步诊断 我们需要知道这些逻辑读的 来源 ，可以尝试用 event 10200 事件

不了峰

该sql的执行计划应该被刷出共享池，看不到执行计划了。

索引的状态 是valid