ORACLE程式佔用CPU情況分析（轉載）

dragon路發表於2011-07-22
Oracle

Oracle資料庫經常會遇到CPU利用率很高的情況，這種時候大都是資料庫中存在著嚴重效能低下的SQL語句，這種SQL語句大大的消耗了CPU資源，導致整個系統效能低下。當然，引起嚴重效能低下的SQL語句的原因是多方面的，具體的原因要具體的來分析，下面通過一個實際的案例來說明如何來診斷和解決CPU利用率高的這類問題。


8EUg)Z:I25736250

作業系統：solairs8

資料庫：Oracle9.2.0.4

問題描述：現場工程師彙報資料庫非常慢，幾乎所有應用操作均無法正常進行。

 

首先登陸主機，執行top發現CPU資源幾乎消耗殆盡，存在很多佔用CPU很高的程式，而記憶體和I/O都不高，具體如下：

last pid: 26136;  load averages:  8.89,  8.91,  8.12                                                                      

216 processes: 204 sleeping, 8 running, 4 on cpu

CPU states:  0.6% idle, 97.3% user,  1.8% kernel,  0.2% iowait,  0.0% swap

Memory: 8192M real, 1166M free, 14M swap in use, 8179M swap free

PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND

25725 oracle     1  50    0 4550M 4508M cpu2   12:23 11.23% oracle

25774 oracle     1  41    0 4550M 4508M run    14:25 10.66% oracle

26016 oracle     1  31    0 4550M 4508M run     5:41 10.37% oracle

26010 oracle     1  41    0 4550M 4508M run     4:40  9.81% oracle

26014 oracle     1  51    0 4550M 4506M cpu6    4:19  9.76% oracle

25873 oracle     1  41    0 4550M 4508M run    12:10  9.45% oracle

25723 oracle     1  50    0 4550M 4508M run    15:09  9.40% oracle

26121 oracle     1  41    0 4550M 4506M cpu0    1:13  9.28% oracle

25745 oracle     1  41    0 4551M 4512M run     9:33  9.28% oracle

26136 oracle     1  41    0 4550M 4506M run     0:06  5.61% oracle

  409 root      15  59    0 7168K 7008K sleep 173.1H  0.52% picld

25653 oracle     1  59    0 4550M 4508M sleep   1:01  0.46% oracle

25565 oracle     1  59    0 4550M 4508M sleep   0:07  0.24% oracle

25703 oracle     1  59    0 4550M 4506M sleep   0:08  0.13% oracle

25701 oracle     1  59    0 4550M 4509M sleep   0:23  0.10% oracle

於是先檢視資料庫的告警日誌ALERT檔案，並沒有發現有什麼錯誤存在，日誌顯示資料庫執行正常，排除資料庫本身存在問題。

然後檢視這些佔用CPU資源很高的Oracle程式究竟是在做什麼操作，使用如下SQL語句：

select sql_text,spid,v$session.program,process  from

v$sqlarea,v$session,v$process

where v$sqlarea.address=v$session.sql_address

and v$sqlarea.hash_value=v$session.sql_hash_value

and v$session.paddr=v$process.addr

and v$process.spid in (PID);

用top中佔用CPU很高的程式的PID替換指令碼中的PID,得到相應的Oracle程式所執行的SQL語句，發現佔用CPU資源很高的程式都是執行同一個SQL語句：

SELECT d.domainname,d.mswitchdomainid, a.SERVICEID,a.SERVICECODE,a.USERTYPE,a.STATUS,a.NOTIFYSTATUS,to_char(a.DATECREATED,'yyyy-mm-dd hh24:mi:ss') DATECREATED,VIPFLAG,STATUS2,CUSTOMERTYPE,CUSTOMERID  FROM service a, gatewayloc b, subbureaunumber c, mswitchdomain d   WHERE b.mswitchdomainid = d.mswitchdomainid and b.gatewaysn = c.gatewaysn  AND a.ServiceCode like c.code||'%' and a.serviceSpecID=1 and a.status!='4' and a.status!='10'  and a.servicecode like '010987654321%' and SubsidiaryID=999999999

基本上可以肯定是這個SQL引起了系統CPU資源大量被佔用，那究竟是什麼原因造成這個SQL這麼大量佔用CPU資源呢，我們先來看看資料庫的程式等待事件都有些什麼：

SQL> select sid,event,p1,p1text from v$session_wait;

       SID EVENT       P1 P1TEXT

---------- ----------------------------------------------------------------

        12 latch free  4.3982E+12 address

        36 latch free  4.3982E+12 address

        37 latch free  4.3982E+12 address

        84 latch free  4.3982E+12 address

       102 latch free  4.3982E+12 address

       101 latch free  4.3982E+12 address

        85 latch free  4.3982E+12 address

        41 latch free  4.3982E+12 address

       106 latch free  4.3982E+12 address

       155 latch free  4.3982E+12 address

       151 latch free  4.3982E+12 address

       149 latch free  4.3982E+12 address

       147 latch free  4.3982E+12 address

         1 pmon timer  300 duration

從上面的查詢我們可以看出，大都是latch free的等待事件，然後接著查一下這些latch的等待都是什麼程式產生的：

SQL> select spid from v$process where addr in

(select paddr from v$session where sid in(84,102,101,106,155,151));

SPID

------------

25774

26010

25873

25725

26014

26016

由此看出latch free這個等待事件導致了上面的那個SQL語句都在等待，佔用了大量的CPU資源。我們來看看究竟主要是那種型別的latch的等待，根據下面的SQL語句:

SQL> SELECT latch#, name, gets, misses, sleeps

     FROM v$latch

     WHERE sleeps>0

     ORDER BY sleeps;

LATCH#  NAME                          GETS     MISSES      SLEEPS  

---------- ----------------------------------------------------------------

    15   messages                       96876       20          1    

   159   library cache pin allocation   407322      43          1    

   132   dml lock allocation            194533      213         2    

     4   session allocation             304897      48          3    

   115   redo allocation                238031      286         4    

    17   enqueue hash chains            277510      85          5    

     7   session idle bit               2727264     314         16   

   158   library cache pin              3881788     5586        58   

   156   shared pool                    2771629     6184        662  

   157   library cache                  5637573     25246       801  

    98   cache buffers chains           1722750424  758400      109837

由上面的查詢可以看出最主要的latch等待是cache buffers chains，這個latch的等待表明資料庫存在單獨的BLOCK的競爭這些latch，我們來看這個latch存在的子latch及其對應的型別：

SQL> SELECT addr, latch#, gets, misses, sleeps

     FROM v$latch_children 

     WHERE sleeps>0         

     and latch# = 98  

     ORDER BY sleeps desc;

ADDR                 LATCH#       GETS     MISSES     SLEEPS

---------------- ---------- ---------- ---------- ----------

000004000A3DFD10         98   10840661      82891        389

000004000A698C70         98     159510          2        244

0000040009B21738         98  104269771      34926        209

0000040009B227A8         98  107604659      35697        185

000004000A3E0D70         98    5447601      18922        156

000004000A6C2BD0         98     853375          7        134

0000040009B24888         98   85538409      25752        106

000004000A36B250         98    1083351        199         96

000004000A79EC70         98     257970         64         35

000004000A356AD0         98    1184810        160         34

……………

接著我們來檢視sleep較多的子latch對應都有哪些物件：

SQL> select distinct a.owner,a.segment_name,a.segment_type from

     dba_extents a,

(select dbarfil,dbablk

from x$bh

where hladdr in

     (select addr

     from (select addr

     from v$latch_children

     order by sleeps desc)

     where rownum < 5)) b

where a.RELATIVE_FNO = b.dbarfil

and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;

OWNER                    SEGMENT_NAME                    SEGMENT_TYPE

---------------------------------------------------------------------------

TEST                    I_SERVICE_SERVICESPECID              INDEX

TEST                    I_SERVICE_SUBSIDIARYID               INDEX

TEST                    SERVICE                              TABLE

TEST                    MSWITCHDOMAIN                        TABLE

TEST                    I_SERVICE_SC_S                       INDEX

TEST                    PK_MSWITCHDOMAIN                     INDEX

TEST                    GATEWAYLOC                           TABLE

…………………

我們看到在開始的那個SQL語句中的幾個物件都有包括在內，於是來看看開始的那個SQL的執行計劃：

SQL> set autotrace trace explain

SQL>SELECT d.domainname,d.mswitchdomainid, a.SERVICEID,a.SERVICECODE,a.USERTYPE,a.STATUS,a.NOTIFYSTATUS,to_char(a.DATECREATED,'yyyy-mm-dd hh24:mi:ss') DATECREATED,VIPFLAG,STATUS2,CUSTOMERTYPE,CUSTOMERID  FROM service a, gatewayloc b, subbureaunumber c, mswitchdomain d   WHERE b.mswitchdomainid = d.mswitchdomainid and b.gatewaysn = c.gatewaysn  AND a.ServiceCode like c.code||'%' and a.serviceSpecID=1 and a.status!='4' and a.status!='10'  and a.servicecode like '010987654321%' and SubsidiaryID=999999999;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE

   1    0   NESTED LOOPS

   2    1     NESTED LOOPS

   3    2       NESTED LOOPS

   4    3         TABLE ACCESS (FULL) OF 'SUBBUREAUNUMBER'

   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'GATEWAYLOC'

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25736250/viewspace-702824/，如需轉載，請註明出處，否則將追究法律責任。

相關文章