[20160401]AWR DB CPU 100%.txt
--这是前几天在ITPUB上的讨论,链接如下:
http://www.itpub.net/thread-2055614-1-1.html
--开始看这个awr报表感觉很奇怪,摘录其中内容:
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 425.5K 100.1
log file sync 89,085 577.4 6 .1 Commit
buffer busy waits 1,698 58.3 34 .0 Concurrency
log file switch (checkpoint incomplete) 101 36 356 .0 Configuration
db file sequential read 9,220 30.8 3 .0 User I/O
-- DB CPU 的消耗是100%.机器配置很高.
Host CPU
CPUs Cores Sockets Load Average Begin Load Average End %User %System %WIO %Idle
192 96 8 0.63 549.78 2.2 33.3 0.0 64.6
Memory Statistics
Begin End
Host Mem (MB): 516,591.3 516,591.3
SGA use (MB): 122,880.0 122,880.0
PGA use (MB): 447.9 702.8
% Host Mem used for SGA+PGA: 23.87 23.92
--在sql语句部分:
SQL ordered by Elapsed Time
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
2,351.92 3,552 0.66 0.55 97.98 0.00 69mubhmyqzgvt SELECT COUNT(EAJ.AHDM) from EA...
...
600.45 10,402,884 0.00 0.14 96.87 0.00 6v8v6s3j1hyd1 select add_months(:1, 1* 12) f...
--而看SQL ordered by Gets部分:
Buffer Gets Executions Gets per Exec %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
3,562,250 3,552 1,002.89 0.67 2,351.92 98 0 69mubhmyqzgvt SELECT COUNT(EAJ.AHDM) from EA...
--sql_id = 69mubhmyqzgvt.
SELECT COUNT(EAJ.AHDM)
FROM EAJ
WHERE EAJ.FYDM = '511028'
AND AJZT >= '300'
AND AJZT < '800'
AND dateadd('year', 1, tdh.convertDate('datetime', LARQ)) < getdate();
--一开始感觉很奇怪,平均才100X个读,而平均时间是0.66秒.也就是读内存8M要0.66秒,这个也太慢了.开始真的没有注意到里面的函数.
--最后才发现这个里面的函数不是oracle自带的,而是开发自己写的,可以大概猜测出getdate对应就是sysdate.
--很明显这个项目是从ms 或者sybase(最后lz讲是sybase移植过来的),真的很无语,什么这样做移植...
--正是这些消耗导致系统的CPU特别忙.
--最佳的建议就是改写这部分代码.看到这样的项目真的很无语....
--我看过一些系统在移植一些定义,估计也是抄别人的代码:
/* Formatted on 2016/04/01 09:04 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION dateadd (p_component VARCHAR2, p_number NUMBER, p_date DATE)
RETURN DATE
IS
/******************************************************************************/
/* 该函数为日期计算函数主要是计算〕 */
/* 从当前日期开始经过多少日、季、月、年等后的日期。 */
/* 入参说明：p_Component 时间元件，如年月日季度等等 */
/* p_Number 加数, 注意：应该为整数（可正可负） */
/* p_Date 基准时间 */
/* 注意：其他日期元件，如世纪等等，暂时未考虑 */
/******************************************************************************/
v_component VARCHAR2 (10);
v_middlenumber NUMBER;
v_returnvalue_str VARCHAR2 (20); --字符串日期格式
v_returnvalue DATE; --返回日期
BEGIN
v_component := UPPER (LTRIM (RTRIM (p_component)));
IF v_component IN ('Y', 'YY', 'YEAR', 'YYYY')
THEN --年情况
v_returnvalue := ADD_MONTHS (p_date, p_number * 12);
ELSIF v_component IN ('M', 'MM', 'MONTH', 'MON')
THEN --月情况
v_returnvalue := ADD_MONTHS (p_date, p_number);
ELSIF v_component IN ('D', 'DD', 'DAY')
THEN --日情况
v_returnvalue := p_date + p_number;
ELSIF v_component IN ('H', 'HH', 'HOUR')
THEN --时情况
v_returnvalue := p_date + p_number / 24;
ELSIF v_component IN ('MI', 'MINUTE')
THEN --分情况
v_returnvalue := p_date + p_number / 1440;
ELSIF v_component IN ('S', 'SS', 'SECOND')
THEN --秒情况
v_returnvalue := p_date + p_number / 86400;
ELSIF v_component IN ('Q', 'QQ', 'QUARTER')
THEN --季度情况
v_returnvalue := p_date + p_number * 3;
ELSIF v_component IN ('W', 'WW', 'WK', 'WEEK')
THEN --周情况
v_returnvalue := p_date + p_number * 7;
ELSE
v_returnvalue := TO_DATE ('1-1-1', 'yyyy-mm-dd');
END IF;
RETURN v_returnvalue;
EXCEPTION
WHEN OTHERS
THEN
RETURN TO_DATE ('1-1-1', 'yyyy-mm-dd'); --例外处理
END;
/
--这个明显基本的如何写sql都没有学好.
--估计他们还不是这样写的,看看sql_id=6v8v6s3j1hyd1,select add_months(:1, 1* 12) f的执行次数 10,402,884就知道.
-- 10402884/117.16/60=1479.8 (117.16是awr的检测时长),每秒执行1480次.
--总的执行次数2,202.2次,这个就占了1480/2202=.67.
