記一次Oracle Sql最佳化經曆--消耗過多CPU

同事打電話跟我說，資料庫CPU過高、swap交換頻繁，要我馬上看看，這裡記錄下整個過程以供大家參考，也讓大家提點意見

$topas

Topas Monitor for host:    fjlt_wb_db01         EVENTS/QUEUES    FILE/TTY

Mon Feb 13 10:10:09 2012   Interval:  2         Cswitch   13932  Readch  2718.1K

                                                Syscall  344.8K  Writech  162.7K

CPU  User%  Kern%  Wait%  Idle%                  Reads      1231  Rawin         0

ALL   89.1   10.9    0.0    0.0                  Writes      861  Ttyout      725

                                                 Forks         6  Igets         0

Network  KBPS   I-Pack  O-Pack   KB-In  KB-Out  Execs         6  Namei       414

Total    97.6K    24.9K   51.5K   30.8K   66.9K Runqueue   43.0  Dirblk        0

                                                 Waitqueue   0.0

Disk    Busy%     KBPS     TPS KB-Read KB-Writ                   MEMORY

Total    13.0     22.6K 2715.0    21.7K  932.5  PAGING           Real,MB   31744

                                                Faults     4337  % Comp     85

FileSystem        KBPS     TPS KB-Read KB-Writ  Steals        0  % Noncomp   4

Total            638.7   398.5  637.4    1.3    PgspIn        0  % Client    4

                                                PgspOut       0

Name            PID   CPU%  PgSp Owner           PageIn         0   PAGING SPACE

Oracle     66847032  17.3  10.4 oracle           PageOut       1   Size,MB   32768

oracle     33751546   4.3  10.7 oracle            Sios          1   % Used     39

oracle     37093606   2.6  10.6 oracle                              % Free     61

oracle     51577090   2.4  10.4 oracle          NFS (calls/sec)

oracle     60752382   2.4  10.2 oracle          SerV2         0  WPAR Activ    0

oracle      2425184   2.3  10.5 oracle          CliV2         0  WPAR Total    0

oracle     38535516   2.1  10.6 oracle          SerV3         0  Press: "h"-help

oracle     65404954   2.0  10.5 oracle          CliV3         0         "q"-quit

oracle     40239486   2.0  10.3 oracle

oracle     65208590   2.0  11.6 oracle

oracle     60555628   1.9  10.5 oracle

oracle     23658656   1.9  10.3 oracle

oracle     47841658   1.9  10.5 oracle

oracle     52363552   1.8  10.3 oracle

oracle      2359684   1.8  10.5 oracle

oracle     31916154   1.7  10.6 oracle

oracle     46530580   1.7  10.9 oracle

oracle       721382   1.6  11.7 oracle

oracle      2884060   1.6  10.5 oracle

oracle      5636414   1.6  10.5 oracle

  發現 CPU 使用率為 100% ， swap 分區交換頻繁，其中進程號為 66847032 的 oracle 使用者進程的 CPU 使用率為 17.3% 。

kthr    memory              page              faults        cpu  
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
 2  0 7506815 658832   0   0   0   0    0   0 6913 154614 20053 31 12 58  0
 5  0 7506576 659071   0   0   0   0    0   0 5763 193844 14268 53 11 36  0
 5  0 7506767 658880   0   0   0   0    0   0 8699 157452 18035 43 11 45  0
 2  0 7506557 659089   0   0   0   0    0   0 6392 135539 20697 27  9 64  0
 2  0 7506286 659359   0   0   0   0    0   0 6518 126193 19194 21 10 69  0

pi 、 po 均為 0 ，說明 swap 分區並未頻繁交換。問題定位為 CPU 使用率過高
根據目前時間做 ash 報告。發現 CPU 的等待事件佔了啟用時間的 67.62%

另外看到 sqlid 為 dczhdxppd0fmm 的 sql 佔用了 41.4 的啟用時間

等了半天沒有反應，估計是 sga 過大，導致查詢較慢，先放著讓他跑跑。

使用 PL\SQL 執行如下sql，大致看下目前系統中的等待事件

select * from v$session_wait where wait_class#<>6;

 

1 2 3 下一頁