oracle9i出现buffer busy waits!

上一篇 / 下一篇  2008-06-25 08:57:59 / 个人分类：常见错误解决

查看( 23 ) / 评论( 51 )
早上上班时系统出现缓慢现象。
做了个statspack。并查看了当时v$session_wait中有5-6个的buffer busy waits 和db file sequential read等待事件。
个人没有调优的经历。看了半天，没什么头绪。希望各位高手提供点思路、方法。谢谢！
statspack如附件：

wip_stats_6.21.txt
(2008-06-23 16:14:00, Size: 102 KB, Downloads: 24)


论坛模式 推荐 收藏 分享给好友 推荐到圈子 管理

TAG:

suniori发布于2008-06-23 16:16:13
索引合理吗？BUFF CACHE 是不是小了，LOG 是不是小了？
haorman zuohao_lu 发布于2008-06-23 16:23:32

QUOTE:

原帖由 suniori 于 2008-6-23 16:16 发表
索引合理吗？BUFF CACHE 是不是小了，LOG 是不是小了？
应该是free buffer等待事件时才考虑是不是BUFF CACHE 是不是小了吧？
我理解错了？
howard_zhang的个人空间 howard_zhang 发布于2008-06-23 16:24:50
Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     20,858,775        1,675       12,453.0   28.4    66.73     65.80 3124638581
Module: LCMMES.EXE
select * from (select null table_catalog, ac.owner table_schema,
ac.table_name table_name, acc.column_name column_name, null col
umn_guid, null column_propid, acc.position ordinal, ac.constrain
t_name pk_name from all_constraints ac, all_cons_columns acc whe
re ac.owner=acc.owner and ac.constraint_type='P' and ac.constrai

处理一下这个SQL
haorman zuohao_lu 发布于2008-06-23 16:25:27
附件不太方便。report又多。
我选取一点有用的贴上来吧。
棉花糖ONE发布于2008-06-23 16:26:19
把top sql和parse处理下
haorman zuohao_lu 发布于2008-06-23 16:27:34

QUOTE:

原帖由 howard_zhang 于 2008-6-23 16:24 发表
Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     20,858,775        1,675       12,453.0   28.4    66.73     65.80 3124638581
Module: LCMMES.EXE
select * from (select null table_catalog, ac.owner table_schema,
ac.table_name table_name, acc.column_name column_name, null col
umn_guid, null column_propid, acc.position ordinal, ac.constrain
t_name pk_name from all_constraints ac, all_cons_columns acc whe
re ac.owner=acc.owner and ac.constraint_type='P' and ac.constrai

处理一下这个SQL
我问过开发的。他们都不知道这个SQL是做什么的。
对比一下正常时的statspack。正常时也有这条SQL了。
haorman zuohao_lu 发布于2008-06-23 16:29:50
另外我发现top查看时。并没有单个进程占用太大cpu资源的情况。
只是%wa这项对比正常状态时多出了20%,达到了30%几。
haorman zuohao_lu 发布于2008-06-23 16:33:34
[php]

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:        44 21-Jun-08 07:53:08      211      77.1
  End Snap:        45 21-Jun-08 08:24:15      201      82.4
   Elapsed:               31.12 (mins)


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
buffer busy waits                               1,565,144       7,586    39.41
db file sequential read                         1,526,183       6,601    34.29
CPU time                                                        3,095    16.08
enqueue                                               484         690     3.59
latch free                                        286,722         652     3.39
          -------------------------------------------------------------


[/php]
haorman zuohao_lu 发布于2008-06-23 16:34:44
[php]

SQL ordered by Gets for DB: Q1WIP01  Instance: q1wip01  Snaps: 44 -45
-> 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

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     20,858,775        1,675       12,453.0   28.4    66.73     65.80 3124638581
Module: LCMMES.EXE
select * from (select null table_catalog, ac.owner table_schema,
ac.table_name table_name, acc.column_name column_name, null col
umn_guid, null column_propid, acc.position ordinal, ac.constrain
t_name pk_name from all_constraints ac, all_cons_columns acc whe
re ac.owner=acc.owner and ac.constraint_type='P' and ac.constrai

        981,915        1,632          601.7    1.3    44.61    461.46 2629342598
Module: LCMMES.EXE
begin OPR_MT2(7,:V0002,:V0003,700,:V0005,:V0006,800,:V0008,:V000
9,:V0010,:V0011,7,8,:V0014,0,:V0016,:V0017,:V0018,:V0019,:V0020,
:V0021,:V0022,:V0023,:V0024,:V0025,:V0026,:V0027,:V0028,:V0029,:
V0030,:V0031,:V0032,:V0033,:V0034,:V0035,:V0036,:V0037,:V0038,:V
0039,:V0040,:V0041,:V0042,:V0043,:V0044,:V0045,:V0046,:V0047,:V0

        952,395           26       36,630.6    1.3    10.69     10.44  665011216
Module: LCMMES.EXE
SELECT ERR_GRP,ERR_SUBGRP,TO_TYPE,TO_CHAR(TRANS_DATE,'yyyy/mm/dd
hh24:mi:ss') FROM SLTS WHERE FAC_ID=:B4 AND WORK_CTR=:B3 AND SU
BSTR(PROD_NBR,1,11)=SUBSTR(:B2 ,1,11) AND TRANS_DATE>TO_DATE(:B1
,'yyyy/mm/dd hh24:mi:ss') AND TO_TYPE IN ('1','2') ORDER BY TRA
NS_DATE DESC

        795,405        5,118          155.4    1.1    27.79     42.40  771114783
Module: LCMMES.EXE
SELECT PR_UNIT FROM BRM_BOM WHERE CUST_NBR=:B3 AND PROD_NBR=:B2
AND PART_NBR=:B1 AND INEFFECTIVE_DATE>SYSDATE

        658,227           66        9,973.1    0.9     3.12      3.06 1918082559
Module: LCMMES.EXE
select nvl(KC_DESC_4,' ') AS KC_DESC,nvl(SUM(KC_NUM_4),0) AS KC_
NUM from ctoc.re_mtrl   where mach_id='10'  and work_ctr=7000
and FAC_ID=7 and RE_CHECK='N' GROUP BY KC_DESC_4

        604,247      279,678            2.2    0.8     8.67      7.55  884378831
Module: LCMMES.EXE
SELECT EDITFLAG FROM CL99 WHERE FAC_ID=:B2 AND CHECKNO = :B1

        433,904           67        6,476.2    0.6    17.40     17.10 2255905933
Module: led.exe
begin KANBAN; end;

        390,645          933          418.7    0.5    15.10     21.36 3698426615
Module: LCMMES.EXE
begin OPR_MTRL(7,:V00002,:V00003,:V00004,:V00005,200,:V00007,:V0
0008,:V00009,:V00010,:V00011,:V00012,:V00013,:V00014,:V00015,:V0
0016,:V00017); end;

        384,707          415          927.0    0.5    28.98     28.44 4158415665
[/php]
haorman zuohao_lu 发布于2008-06-23 16:36:42
[php]

SQL ordered by Reads for DB: Q1WIP01  Instance: q1wip01  Snaps: 44 -45
-> End Disk Reads Threshold:      1000

                                                     CPU      Elapsd
Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
         88,257            2       44,128.5    4.0    29.72     87.03 4130081486
Module: PL/SQL Developer
Select 'main_release' as type,substr(ENTITY_NO,6,1),  to_char(tr
unc(TRANS_DATE-(7.5/24)),'YYYY-MM-DD HH24:MI:SS') as ReportDate,
substr(PROD_NBR,5,3) as PSIZE,  substr(PROD_NBR,1,11) as ModelNo
,substr(PROD_NBR,17,1) as RANK,  TO_TYPE,WORK_CTR,MACH_ID,count(
*) as CNT  FROM  SLTS  where TRANS_DATE between TRUNC(SYSDATE-1)

         12,511       11,483            1.1    0.6    11.59    137.28 3786399628
Module: LCMMES.EXE
INSERT INTO WIP_LIST (FAC_ID,PNL_ID,WORK_CTR,PROD_NBR,CUST_NBR,T
YPE,TRANS_DATE,SEC_WORK,SEC_PROD_NBR, MACH_ID,SHIFT_ID,NEXT_WORK
CTR,OPERATOR) VALUES (:B12 ,:B11 ,:B10 ,:B9 ,:B8 ,'OK_OUT',TO_DA
TE(:B7 ,'MM/DD/YYYY HH24:MI:SS'),:B6 ,:B5 , :B4 ,:B3 ,:B2 ,:B1 )


         11,012            2        5,506.0    0.5     1.82     64.37 1057933429
Module: LCMMES.EXE
select * from ctoc.re_mtrl where fac_id = 7 and work_ctr = 7000
and mach_id='04'  and re_check='N'

[/php]
haorman zuohao_lu 发布于2008-06-23 16:39:46
[php]
Buffer wait Statistics for DB: Q1WIP01  Instance: q1wip01  Snaps: 44 -45
-> ordered by wait time desc, waits desc

                                 Tot Wait    Avg
Class                    Waits   Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block           1,558,334      7,720         5
undo block               4,594         43         9
undo header                  8          0         0
          -------------------------------------------------------------
[/php]
haorman zuohao_lu 发布于2008-06-23 16:40:56
为方便各位查看。我选取了report中相关的部分贴上来。
多多指点……
rollingpig的个人空间 rollingpig 发布于2008-06-23 17:06:21
最大的问题没做绑定变量！！！！
造成的后果有二
1是耗了额外的20%的CPU
2是使Top SQL 无法反映真正的情况。很难一下定为到真正有问题的SQL.

buffer busy waits 和 Latch 中的大量cache buffers chains 基本可以认为是某个table的index不合适。但是具体是哪个，由于没做绑定变量，很难从statspack中看出来。
当然，勉为其难的话，看Top SQL中elapsed time 明显大于CPU Time的SQL, 看看执行计划，是否的确用了不合适的index。

或者只好从v$session_wait入手：
select sql_text from v$session_wait w, v$session s, v$sql sql
where s.sid = w.sid
and sql.hash_value = nvl(s.sql_hash_value,s.prev_hash_value)
and w.event ='buffer busy waits'
再看看抓出来的SQL.
haorman zuohao_lu 发布于2008-06-23 17:20:54
当时查看v$session_wait发现有5-6个的 buffer busy wait和5-6个db file sequential read.
我抓取了相关的语句。
造成buffer busy wait 语句及其执行计划如下：
[php]

select substr(a.prod_nbr, 1, 13) as modelno,
                substr(a.prod_nbr, 18, 20) as suffix,
                a.custmr_c as cust_nbr,
                b.cust_name,
                decode(splendor, 0, 0, 1) as qcokng,
                'O' as flag1,
                count(distinct a.pnl_id) as ngcnt,
                to_char(trunc((sysdate-1)+ (7.5 / 24)), 'YYYY-MM-DD') AS CDATE,
                'C' as flag2,
                d.duty_reason as falg3
           from wvls a, ocms b, rpch d
          where a.cart_id2 in
                (select distinct nt_boxid
                   FROM ctoc.as400int
                  where nt_flagy = 'X'
                    and nt_dch1 = 'B'
                    and nt_ftime between trunc(sysdate - 1) + (7.5 / 24) and
                    trunc(sysdate) + (7.5 / 24))
            and a.fac_id = 7
            and a.work_num_status = '2'
            and a.custmr_c = b.cust_nbr
            and a.fac_id = d.fac_id
            and a.pnl_id = d.pnl_id
            and d.duty_reason = '1'
          group by decode(splendor, 0, 0, 1),
                   substr(a.prod_nbr, 1, 13),
                   substr(a.prod_nbr, 18, 20),
                   a.custmr_c,
                   b.cust_name,
                   d.duty_reason


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                        |  Name         | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |     1 |   122 |    73
|   1 |  SORT GROUP BY                   |               |     1 |   122 |    73
|   2 |   FILTER                         |               |       |       |
|   3 |    NESTED LOOPS                  |               |     1 |   122 |    68
|   4 |     NESTED LOOPS                 |               |     1 |   110 |    67
|   5 |      NESTED LOOPS                |               |    72 |  6408 |    24
|   6 |       TABLE ACCESS BY INDEX ROWID| RPCH          |    72 |  1584 |     2
|   7 |        INDEX RANGE SCAN          | RPCH_PK       |  2882 |       |     2
|   8 |       TABLE ACCESS BY INDEX ROWID| WVLS          |     1 |    67 |     1
|   9 |        INDEX UNIQUE SCAN         | WVLS_PK       |     1 |       |
|  10 |      TABLE ACCESS BY INDEX ROWID | AS400INT      |     1 |    21 |     1
|  11 |       INDEX RANGE SCAN           | AS400INT_PK1  |     4 |       |     1
|  12 |     INDEX RANGE SCAN             | OCMS1         |     1 |    12 |     1
--------------------------------------------------------------------------------

[/php]
haorman zuohao_lu 发布于2008-06-23 17:25:11
造成db file sequential read的SQL及执行计划如下：
[php]
Select 'main_release' as type,
       substr(ENTITY_NO, 6, 1),
       to_char(trunc(TRANS_DATE - (7.5 / 24)), 'YYYY-MM-DD HH24:MI:SS') as ReportDate,
       substr(PROD_NBR, 5, 3) as PSIZE,
       substr(PROD_NBR, 1, 11) as ModelNo,
       substr(PROD_NBR, 17, 1) as RANK,
       TO_TYPE,
       WORK_CTR,
       MACH_ID,
       count(*) as CNT
  FROM SLTS
where TRANS_DATE between TRUNC(SYSDATE - 1) + (7.5 / 24) and
       TRUNC(SYSDATE) + (7.5 / 24)
   and WORK_CTR not in (100, 300, 500, 700, 6000, 7000, 0)
   and STIMES = '1'
   and FG_TYPE in ('1', '4')
group by substr(ENTITY_NO, 6, 1),
          to_char(trunc(TRANS_DATE - (7.5 / 24)), 'YYYY-MM-DD HH24:MI:SS'),
          substr(PROD_NBR, 5, 3),
          substr(PROD_NBR, 1, 11),
          substr(PROD_NBR, 17, 1),
          TO_TYPE,
          WORK_CTR,
          MACH_ID

--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |  1567 | 67381 |   201K|
|   1 |  SORT GROUP BY                |             |  1567 | 67381 |   201K|
|   2 |   FILTER                      |             |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| SLTS        |  1567 | 67381 |   201K|
|   4 |     INDEX RANGE SCAN          | SLTS8       |   156K|       |   579K|
-----------------------------------------------------------------------------

[/php]
haorman zuohao_lu 发布于2008-06-23 17:29:43
上面的两个语句都只是在每天早上运行一次。并且只有一个用户运行它们。
我不明白的是为什么我看到v$session 或者v$session_wait里有5-6个的session在执行这两条语句呢？
haorman zuohao_lu 发布于2008-06-23 17:33:48
应该问题就出在引起db file sequential read的SQL上，这样说对吗？
棉花糖ONE发布于2008-06-23 17:42:14
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |  1567 | 67381 |   201K|
|   1 |  SORT GROUP BY                |             |  1567 | 67381 |   201K|
|   2 |   FILTER                      |             |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| SLTS        |  1567 | 67381 |   201K|
|   4 |     INDEX RANGE SCAN          | SLTS8       |   156K|       |   579K|

索引的选择性应该不是最好的，这个语句应该有选择性能强的条件，rows从156k到1567，说明了非索引列的选择度到1/100

[ 本帖最后由 棉花糖ONE 于 2008-6-23 17:43 编辑 ]
haorman zuohao_lu 发布于2008-06-23 17:54:37
多谢棉花糖
这个表很大。本来就有近10个索引。我不想为了他再建索引。
我试了下让它走其它相关的索引。如下：
[php]
Select /*+index (slts slts4)*/'main_release' as type,
       substr(ENTITY_NO, 6, 1),
       to_char(trunc(TRANS_DATE - (7.5 / 24)), 'YYYY-MM-DD HH24:MI:SS') as ReportDate,
       substr(PROD_NBR, 5, 3) as PSIZE,
       substr(PROD_NBR, 1, 11) as ModelNo,
       substr(PROD_NBR, 17, 1) as RANK,
       TO_TYPE,
       WORK_CTR,
       MACH_ID,
       count(*) as CNT
  FROM SLTS
where TRANS_DATE between TRUNC(SYSDATE - 1) + (7.5 / 24) and
       TRUNC(SYSDATE) + (7.5 / 24)
   and WORK_CTR not in (100, 300, 500, 700, 6000, 7000, 0)
   and STIMES = '1'
   and FG_TYPE in ('1', '4')
group by substr(ENTITY_NO, 6, 1),
          to_char(trunc(TRANS_DATE - (7.5 / 24)), 'YYYY-MM-DD HH24:MI:SS'),
          substr(PROD_NBR, 5, 3),
          substr(PROD_NBR, 1, 11),
          substr(PROD_NBR, 17, 1),
          TO_TYPE,
          WORK_CTR,
          MACH_ID

--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |  1567 | 67381 |   151K|
|   1 |  SORT GROUP BY                |             |  1567 | 67381 |   151K|
|   2 |   FILTER                      |             |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| SLTS        |  1567 | 67381 |   150K|
|   4 |     INDEX FULL SCAN           | SLTS4       |   194K|       |   431K|
-----------------------------------------------------------------------------

[/php]
这样好象cost小了不少。虽然还是很大。
haorman zuohao_lu 发布于2008-06-23 17:57:38
请教棉花糖ONE
如果不建索引，这条语句有无优化的可能？
棉花糖ONE发布于2008-06-23 17:58:58
select count(*) from SLTS where TRANS_DATE between TRUNC(SYSDATE - 1) + (7.5 / 24) and
       TRUNC(SYSDATE) + (7.5 / 24) ;
select count(*),count(distinct stimes) from SLTS where STIMES = '1';
select count(*),count(distinct fg_type) from SLTS where FG_TYPE in ('1', '4');

看看分别返回多少
棉花糖ONE发布于2008-06-23 17:59:53
10个索引，我晕啊，有时候单列索引的选择性不强，就应该建组合索引
haorman zuohao_lu 发布于2008-06-23 18:01:32

QUOTE:

10个索引，我晕啊，有时候单列索引的选择性不强，就应该建组合索引
都是组合索引。呵呵……

[ 本帖最后由 zuohao_lu 于 2008-6-23 18:17 编辑 ]
wenhuiqiao发布于2008-06-23 18:09:39
发一下这个sql 的查询计划？
select kc_desc_1,kc_lot_1,kc_desc_2,kc_lot_2,kc_desc_3,kc_lot_3,
kc_desc_4,kc_lot_4,kc_desc_5,kc_lot_5 from wmus where fac_id=7 a
nd mach_id='03'  and trans_nbr=(select max(trans_nbr) from wmus
where fac_id=7 and mach_id='03' )
发一下这个sql 的查询计划？
haorman zuohao_lu 发布于2008-06-23 18:16:12

QUOTE:

原帖由 wenhuiqiao 于 2008-6-23 18:09 发表
select kc_desc_1,kc_lot_1,kc_desc_2,kc_lot_2,kc_desc_3,kc_lot_3,
kc_desc_4,kc_lot_4,kc_desc_5,kc_lot_5 from wmus where fac_id=7 a
nd mach_id='03'  and trans_nbr=(select max(trans_nbr) from wmus
where fac_id=7 and mach_id='03' )
发一下这个sql 的查询计划？
为什么是要看这条语句。从哪看出来它有问题？？
haorman zuohao_lu 发布于2008-06-23 18:35:42

QUOTE:

原帖由 棉花糖ONE 于 2008-6-23 17:58 发表
select count(*) from SLTS where TRANS_DATE between TRUNC(SYSDATE - 1) + (7.5 / 24) and
       TRUNC(SYSDATE) + (7.5 / 24) ;
select count(*),count(distinct stimes) from SLTS where STIMES = '1';
select count(*),count(distinct fg_type) from SLTS where FG_TYPE in ('1', '4');

看看分别返回多少
1、count(*)
    643082
2、count(*),   count(distinct stimes)
    97088893               1
3、count(*)    count(distinct fg_type)
   120457087              2
wenhuiqiao发布于2008-06-23 18:38:13
回复 #27 zuohao_lu 的帖子
第二部分的 SQL ordered by Gets for DB中有很多类似的语句，负载应该比较大
棉花糖ONE发布于2008-06-23 18:43:42
select count(*) from SLTS where TRANS_DATE between TRUNC(SYSDATE - 1) + (7.5 / 24) and
       TRUNC(SYSDATE) + (7.5 / 24)  and STIMES = '1';
      and  FG_TYPE in ('1', '4');
看看这语句返回多少
haorman zuohao_lu 发布于2008-06-23 19:23:49

QUOTE:

原帖由 wenhuiqiao 于 2008-6-23 18:38 发表
第二部分的 SQL ordered by Gets for DB中有很多类似的语句，负载应该比较大
但是那些单次执行的buffer gets 都很少啊，只是执行次数多了而已吧？
应该不是问题所在。
haorman zuohao_lu 发布于2008-06-23 19:38:55

QUOTE:

原帖由 棉花糖ONE 于 2008-6-23 18:43 发表
select count(*) from SLTS where TRANS_DATE between TRUNC(SYSDATE - 1) + (7.5 / 24) and
       TRUNC(SYSDATE) + (7.5 / 24)  and STIMES = '1';
      and  FG_TYPE in ('1', '4');
看看这语句返回多少
count(*)=536834
我来说两句

(可选)

日历

« 2008-09-08  
日 一 二 三 四 五 六
 123456
78910111213
14151617181920
21222324252627
282930    

数据统计

  • 访问量: 1833
  • 日志数: 43
  • 建立时间: 2008-01-19
  • 更新时间: 2008-09-04

RSS订阅

Open Toolbar