Solved: Statspack report | Experts Exchange

ASKER

Also, what the OS_CPU_WAIT_TIME is showing? The # that I have for that is pretty high. I read it is supposed to be 1/100 of a second, but when calculating that it's showing a very long time. This is the value - 54081042509800

Thanks!

joebednarz

If I remember correctly, CPU statistics can be interesting to interpret. Yes, the value you got back for OS_CPU_WAIT_TIME is very high! I'm not 100% sure, but I think CPU statistics are based upon all CPU's aggregated... so, if it says the CPU was idle for four minutes, on a four cpu system... real-time translates to only one minute idle.

Also, all statistics need a reference point... how long did you wait for each statspack.snap?

If you could post the header of the report, plus a section from the CPU usage, maybe it would be easier to troubleshoot...

cdion

ASKER

The report went for about 36 minutes. The value is actually different in the report, although still very high. That one was pulled from v$osstat. Here is the top & cpu portion of the statspack report, let me know if any other sections might help. Thanks.

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 25-Oct-07 10:16:41 17 3.8
End Snap: 2 25-Oct-07 10:53:09 18 5.0
Elapsed: 36.47 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 2,512M Std Block Size: 8K
Shared Pool Size: 768M Log Buffer: 14,158K

Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 433,081.01 2,972.22
Logical reads: 1,697.12 11.65
Block changes: 1,480.41 10.16
Physical reads: 3.42 0.02
Physical writes: 41.41 0.28
User calls: 292.57 2.01
Parses: 0.70 0.00
Hard parses: 0.05 0.00
Sorts: 0.39 0.00
Logons: 0.15 0.00
Executes: 147.09 1.01
Transactions: 145.71

% Blocks changed per Read: 87.23 Recursive Call %: 34.59
Rollback per transaction %: 0.00 Rows per Sort: 66.21

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 28.57
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.86 Soft Parse %: 92.24
Execute to Parse %: 99.52 Latch Hit %: 99.86
Parse CPU to Parse Elapsd %: 111.54 % Non-Parse CPU: 99.86

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 91.34 91.15
% SQL with executions>1: 68.13 68.82
% Memory for SQL w/exec>1: 69.06 69.32

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
log file sync 318,787 1,484 5 41.0
log file parallel write 319,005 1,473 5 40.7
log file switch (checkpoint incomplete) 1,179,043 235 0 6.5
CPU time 209 5.8
control file sequential read 7,061,432 98 0 2.7
-------------------------------------------------------------
Host CPU (CPUs: 4)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.14 0.44 19.73 6.70 73.57 29.91 #######

Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 16.86
% of busy CPU for Instance: 63.77
%DB time waiting for CPU - Resource Mgr:

Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 32,738.6 32,738.6
SGA use (MB): 3,424.0 3,424.0
PGA use (MB): 113.3 118.7
% Host Mem used for SGA+PGA: 10.8 10.8
-------------------------------------------------------------

Time Model System Stats DB/Inst: KVSIEBD/KVSIEBD Snaps: 1-2
-> Ordered by % of DB time desc, Statistic name

OS Statistics DB/Inst: KVSIEBD/KVSIEBD Snaps: 1-2
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name

Statistic Total
------------------------- ----------------------
BUSY_TIME 231,333
IDLE_TIME 643,819
IOWAIT_TIME 261,767
SYS_TIME 58,648
USER_TIME 172,685
LOAD 0
OS_CPU_WAIT_TIME 163,518,094,100
VM_IN_BYTES 1,925,120
PHYSICAL_MEMORY_BYTES 34,328,862,720
NUM_CPUS 4
NUM_CPU_SOCKETS 4
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: KVSIEBD/KVSIEBD Snaps: 1-2
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SIEBEL_RBS
24,281 11 0.2 1.0 4,172,788 1,907 158 364.6
SIEBEL_IDX1
251,340 115 14.1 1.9 1,458,800 667 1 10.0
SIEBEL_DATA1
344,954 158 2.4 5.3 954,414 436 565 69.6
SYND_DATA1
97,048 44 0.0 1.0 97,000 44 0 0.0
SYND_IDX1
60,655 28 0.1 1.0 60,625 28 0 0.0
SYSTEM
64,204 29 1.6 1.2 49,944 23 6 6.7
SYSAUX
22,156 10 6.0 1.5 55,926 26 0 0.0
SIEBEL_USR
12,743 6 1.0 1.1 43,629 20 0 0.0
TOOLS
12,130 6 0.1 1.0 14,055 6 0 0.0
INDX
12,127 6 0.1 1.0 12,125 6 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: KVSIEBD/KVSIEBD Snaps: 1-2
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File

Computer101

Forced accept.

Computer101
EE Admin