数据库负载不高但是相应很慢
环境：OS: RHEL 5.8 X86
DB：11.2.0.3.7
盘阵:IBM DS5020 做的raid10
单实例ASM
数据库负载不高，相应非常慢，应用做的都是日常操作，vmstat数据如下：
# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
2 6 0 1372088 265408 17038184 0 0 209 39 1 5 4 0 95 1 0
3 10 0 1376832 265408 17038184 0 0 21632 220 1711 8843 7 0 83 9 0
1 11 0 1409376 265408 17038184 0 0 8 0 1302 8509 5 0 81 13 0
2 7 0 1375152 265408 17038184 0 0 22728 626 3521 10425 6 0 82 11 0
1 8 0 1409872 265408 17038184 0 0 0 48 1186 8733 6 0 84 10 0
3 6 0 1380104 265408 17038184 0 0 82432 814 1966 9670 8 0 82 10 0
3 7 0 1408008 265408 17038184 0 0 19456 128 1742 9157 11 0 80 9 0
1 9 0 1407496 265408 17038184 0 0 16 0 1354 9204 5 0 83 11 0
1 10 0 1405624 265408 17038188 0 0 5136 12 1469 9028 3 0 84 12 0
1 10 0 1405368 265408 17038188 0 0 0 0 1328 8571 4 0 83 12 0
2 10 0 1367072 265408 17038188 0 0 39176 970 1776 8868 6 0 80 14 0
1 12 0 1403604 265408 17038188 0 0 24 240 1271 8286 5 0 76 18 0
3 7 0 1366160 265408 17038188 0 0 41408 1472 1859 8557 7 0 79 14 0
2 10 0 1403856 265408 17038188 0 0 8 52 1334 8147 9 0 75 16 0
6 5 0 1404084 265408 17038188 0 0 3096 88 1437 8386 6 0 75 19 0
3 10 0 1367812 265408 17038188 0 0 13312 90 1328 8428 6 0 82 12 0
1 11 0 1404640 265408 17038188 0 0 0 48 1527 9072 5 0 79 16 0
7 8 0 1372660 265408 17038188 0 0 60712 886 1667 9275 10 0 82 9 0
1 10 0 1407588 265408 17038188 0 0 24 0 3612 10854 7 0 78 15 0
5 5 0 1384648 265408 17038188 0 0 56240 2230 1866 9406 7 0 80 13 0
2 7 0 1303800 265412 17038184 0 0 13496 274 1455 11400 8 0 85 6 0
2 7 0 1411056 265412 17038184 0 0 0 0 1248 9855 5 0 88 6 0
1 7 0 1418008 265412 17038184 0 0 11272 472 1588 9888 3 0 89 8 0
3 8 0 1417264 265412 17038764 0 0 0 48 1648 9851 5 0 85 9 0
3 6 0 1377480 265412 17038764 0 0 23736 291 1825 9848 5 0 87 7 0
1 9 0 1418128 265412 17038764 0 0 32 0 1314 8524 6 0 84 10 0
AWR报告见附件，是这周一（今天）和上周一的相同时间段AWR报告 看log file sync时间很高但是commit和日志切换次数来看都很正常啊，我怀疑是物理IO设备这块有问题 怀疑是sql语句 UPDATE PURCHASE_RETURNBILLDETAIL T
SET (T.CHECK_QUANTITY, T.CHECK_PRICEAMOUNT) =
(SELECT SUM(S.SORTING_QTY), SUM(S.SORTING_PRICEAMOUNT)
FROM SORTING_BILLDETAIL S
WHERE S.LOGISTICSTYPEID = '进退单'
AND S.LOGISTICSBILLDETAILID = T.PURCHASERETURNBILLDETAILID)
WHERE T.PURCHASERETURNBILLID = :B2
AND T.SYSTEMID = :B1
AND EXISTS
(SELECT 1
FROM SORTING_BILLDETAIL Y
WHERE Y.LOGISTICSTYPEID = '进退单'
AND Y.LOGISTICSBILLDETAILID = T.PURCHASERETURNBILLDETAILID) 执行一次，提交一次，估计外边有循环，查看是否有循环，提交的时候一次性提交。 iostat -x 3
iostat -d 3
看下磁盘负载 DECLARE
JOB BINARY_INTEGER := :JOB;
NEXT_DATE DATE := :MYDATE;
BROKEN BOOLEAN := FALSE;
BEGIN
DECLARE
DESCERR VARCHAR2(500);
BEGIN
JOB_IF_GET_SALESORDER30(AS_SYSTEMID => '2',
AS_USERID => 'admin',
DESCERR => DESCERR);
IF DESCERR IS NOT NULL THEN
ROLLBACK;
SYS_SYSJOB_LOGCREATE('2',
1864,
'存储过程Job_If_Get_Salesorder30执行失败' || DESCERR,
'false',
DESCERR);
END IF;
COMMIT;
END;
:MYDATE := NEXT_DATE;
IF BROKEN THEN
:B := 1;
ELSE
:B := 0;
END IF;
END;
看看提交的是否太频繁了。 545459983 发表于 2013-12-16 18:31 static/image/common/back.gif
怀疑是sql语句 UPDATE PURCHASE_RETURNBILLDETAIL T
SET (T.CHECK_QUANTITY, T.CHECK_PRICEAMOUNT) =
...
因为业务需要，此语句无法一次提交 疾风狼 发表于 2013-12-16 19:32 static/image/common/back.gif
DECLARE
JOB BINARY_INTEGER := :JOB;
NEXT_DATE DATE := :MYDATE;
这个语句commit属于正常，与数据库正常情况下commit一致无异常
页:
[1]