Re: Poor Oracle performance with load CPU load

On Aug 2, 10:59 am, "jshen...._at_gmail.com" <jshen...._at_gmail.com> wrote:
> hi,
>
> we run oracle8.1.7 on HP-UX 11i.
>
> The DB performance becomes very poor that a lot data is lost for
> application processing queue overflow.
>
> Application ---- > DB
> ^
> |
> data queue here
>
> I noticed that CPU load of db server is not high, but the top5
> process change from DB main process 'oraclecodb' to
> ora_lgwr_codb, ora_dbw1_codb, ora_dbw2_codb, ora_dbw3_codb ,
> ora_dbw0_codb .
>
> with sar -d 5 1000, it shows some disk is very busy
>
> 23:56:55 c0t6d0 3.61 0.50 6 35 0.00 11.36
> c4t6d0 3.61 0.50 5 33 0.00 10.57
> c11t12d1 38.68 1.33 96 776 4.47 11.48
> c11t12d7 32.87 1.11 90 728 3.13 10.23
> c11t13d5 32.87 2.04 104 842 7.59 10.18
> c11t14d3 37.68 1.99 108 922 7.30 10.83
> c11t15d1 34.07 1.84 96 781 7.22 10.89
> c11t15d7 37.27 1.63 101 830 5.48 10.78
> c12t0d5 31.66 1.42 97 774 4.98 10.37
> c12t1d3 29.66 1.59 99 806 5.68 10.16
> c9t2d1 35.07 1.19 93 745 4.22 11.78
> c9t2d6 38.88 1.88 101 837 7.30 11.82
> c9t2d7 35.27 1.40 100 798 5.45 12.05
> c9t3d5 40.68 1.50 104 837 5.85 11.90
> c9t4d3 40.68 1.78 103 826 7.26 13.19
> c9t5d1 36.07 1.27 96 765 4.57 11.56
> c9t5d7 34.47 1.02 97 774 3.00 11.49
> c9t6d5 37.27 1.21 96 766 4.46 11.80
> c11t12d2 14.23 0.50 24 192 0.00 9.06
> c11t13d0 18.64 0.63 30 239 1.14 14.30
> c11t13d6 19.64 0.50 29 231 0.00 12.33
> c11t14d4 17.84 0.50 27 218 0.07 11.78
> c11t15d2 18.24 0.50 28 221 0.00 10.66
> c12t0d0 16.43 0.50 27 216 0.15 12.48
> c12t0d6 18.24 0.50 29 232 0.00 11.44
> c12t1d4 15.03 0.50 28 223 0.00 10.08
> c9t2d2 18.64 0.52 29 236 0.33 13.11
> c9t3d0 20.04 0.56 32 258 0.55 13.47
> c9t3d4 16.43 0.50 26 212 0.00 12.69
> c9t3d6 21.24 0.50 35 277 0.02 14.18
> c9t4d4 20.84 0.76 36 289 1.78 14.72
> c9t5d2 23.05 1.06 46 372 3.62 17.83
> c9t6d0 23.65 0.74 40 322 2.08 17.16
> c9t6d6 19.04 0.50 28 223 0.01 15.05
> c19t0d4 58.72 9.55 200 1597 31.99 14.60
> c19t0d5 51.30 8.87 174 1390 30.64 15.28
> c19t0d6 56.91 7.09 180 1441 24.49 14.90
> c19t0d7 55.71 7.12 179 1430 24.78 15.41
> c19t1d0 54.91 7.96 175 1396 28.06 15.43
> c19t1d1 55.91 8.70 188 1504 30.61 15.12
> c19t1d2 55.31 8.07 179 1435 27.21 15.25
> c19t1d3 54.91 8.53 186 1485 28.86 14.98
> c20t1d4 55.31 9.05 182 1454 30.60 15.06
> c20t1d5 56.91 12.45 186 1486 41.73 15.51
> c20t1d6 55.51 10.49 183 1461 34.74 14.73
> c20t1d7 54.71 8.07 173 1387 27.26 15.59
> c20t2d0 56.71 6.91 177 1414 23.97 15.36
> c20t2d1 56.31 6.54 174 1395 20.73 14.67
> c20t2d2 57.11 9.53 186 1491 31.22 15.03
> c20t2d3 54.71 10.66 186 1491 35.68 15.22
> c18t2d1 35.07 2.26 120 957 3.73 7.30
> c18t2d2 32.67 1.88 120 964 2.89 7.12
> c18t2d3 32.46 1.92 117 939 2.98 6.97
> c18t2d4 35.07 1.64 118 946 2.47 7.57
> c21t2d5 36.27 2.25 130 1042 4.62 8.14
> c21t2d6 37.27 2.50 126 1012 5.23 8.82
> c21t2d7 35.87 2.27 119 956 4.56 8.42
> c21t3d0 35.07 1.78 109 872 3.64 8.76
> c21t3d1 39.08 1.53 126 1012 2.88 8.16
> c21t3d2 18.04 0.50 184 4186 0.00 1.00
> c18t0d7 14.63 15.77 140 1121 22.46 6.88
> c21t1d0 15.43 13.28 103 832 26.19 10.19
>
> what are those ora_lgwr_codb, ora_dbw1_codb, ora_dbw2_codb,
> ora_dbw3_codb , ora_dbw0_codb process for ? where is the possible
> bottleneck and how to improve the situation ?
>
> Any word will be high appreciated.
>
> Joe

The ora_lgwr_codb process is your log writer, which writes to your redo logs. You do NOT want to lose that process. The other *dbw* processes are database writers; you apparently have 4 of those running, so that shouldn't be causing you much grief unless you're CPU bound.

I'd be running Statspack snapshots and generating reports to see what may be causing this slowdown; it may not be the database. Until you have these reports generated any suggestion would be guesswork.

David Fitzjarrell Received on Thu Aug 02 2007 - 15:06:23 CDT