CPU performance questions

[Rick Garland]

Hi all:

Got some Oracle contractors working on some of our systems. 1 contractor is pointing out some performance issues. He has provided some very limited stats but I am hoping someone can provide some insight.

I am posting his message below.

I have been gathering data on the server since 11am and I see indicators of CPU bottleneck however I could be wrong. Kindly help diagnose the issues from the OS end and let me know the results.


Regards,

Suresh





From the Statistics in Oracle, we see that for every CPU second spent parsing SQL statements, we spend 2.4 seconds of Wall clock time.



One of the top 5 waits in oracle is CPU Time



########################################################33

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~ % Total

Event Waits Time (s) Ela Time

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

db file sequential read 3,739,860 12,021 38.75

db file scattered read 5,200,891 9,922 31.98

CPU time 5,266 16.97

latch free 95,498 983 3.17

buffer busy waits 325,978 972 3.13

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







###################################################################

The sar output below indicates significant I/O wait.



park:orap1:OADBP1 > sar -u 5 5



HP-UX park B.11.23 U ia64 05/09/06



13:56:56 %usr %sys %wio %idle

13:57:01 34 9 53 4

13:57:06 32 8 54 7

13:57:11 38 11 46 5

13:57:16 39 10 47 4

13:57:21 35 8 52 5



Average 35 9 50 5



The stats below show processes waiting to run [r] or that are blocked for resources[b].



vmstat -S park:orap1:OADBP1 > vmstat -S 5 5

procs memory page faults cpu

r b w avm free si so pi po fr de sr in sy cs us sy id

4 9 0 1284613 40862 95 95 5 5 0 0 109 9111 47293 2644 25 7 67

4 9 0 1284613 43787 0 0 5 0 0 0 0 8004 30004 3133 35 5 59

4 6 0 1274352 43904 0 0 1 0 0 0 0 8119 23981 3599 30 5 65

4 6 0 1274352 43182 0 0 1 0 0 0 0 11529 32012 6985 28 8 64

6 5 0 1150416 43955 0 0 0 0 0 0 0 9299 37859 4619 40 7 53

park:orap1:OADBP1 > vmstat -S 5 5

procs memory page faults cpu

r b w avm free si so pi po fr de sr in sy cs us sy id

1 5 0 1284925 53607 95 95 5 5 0 0 109 9112 47283 2645 25 7 67

1 5 0 1284925 54607 0 0 3 0 0 0 0 7841 25737 2007 8 5 88

3 5 0 1214446 54552 0 0 1 0 0 0 0 9030 37783 2997 13 10 77

3 5 0 1214446 54390 0 0 1 0 0 0 0 12769 58996 5935 23 13 63

1 5 0 1124451 56638 0 0 2 0 0 0 0 12278 41867 4805 12 10 78




Many thanks

[Ninad_1 · Accepted solution]

Seems that there is a high IO wait time - meaning CPU is waiting idle for data to be received from disks.
Please do check the sar -d output for high avserv, avwait, avqueue and %busy - to understand if there is unequal distribution of data across disks and which disks are having high avserv times causing high IO wait.
The High %wio is causing very less %idle and hence if systme requires CPU to process - its not available.

Regards,
Ninad

[Jakes Louw]

Try to get some of the more obscure IOSTAT output as well (I think these also work on HP-UX):

iostat -xDd
iostat -nxC

[Chan 007]

Rick,

Seeing your input, I guess that it is IO bottleneck, Check the RAID or SAN performance.

also found this link which gives some thought for db_cache_size

http://www.oracle.com/technology/oramag/oracle/03-jan/o13expert.html

Chan

[Dave Wherry]

There is certainly not enough information here to make a diagnosis and, I'll say I'm not sure I'm reading it correctly.
One of the first things though, if

"db file sequential read 3,739,860 12,021 38.75

db file scattered read 5,200,891 9,922 31.98"

are the total I/O's during this job, nearly 42% of the I/O's are sequential.

Reads kill. If these are sequential scans it can certainly slow things down drastically.

A few years ago we had an SAP job that ran for 17 hours. With the proper use of indexes the job came down to 6 minutes.

[Rick Garland]

Many thanks all