[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.