Oracle Database Discussions

Hi,

I am using oracle 9.2.0.8 RAC on SUN solaris platform.I am trying to understand my DB statistics using the below statspack report.Can you please coment on the below report

My quetions/thoughts are:

1) CPU time is in the top timed events,Is that eman some need to do with CPU increase.Was CPU bottleneck?

2) Parse CPU to Parse Elapsd %: 80.28 .Is this means I am hard parsing most of the time.How can identify which queries doing more hard parses.what is mean by% Non-Parse CPU: 98.76

3) Memory Usage %: 96.25 96.64.It seems to be there is too much memory usage.Can you elaborate this usage about what could be the reasons for this to happen

4) global cache cr request is coming in the top wait evetns and top timed events.Is there some issue with RAC?

5) can you please explain about 5 CR Blocks Served (RAC) and 5 CU Blocks Served (RAC) and Top 5 ITL Waits per

Your help is appreciated!!



Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 2,101,521.49 18,932.15
Logical reads: 91,525.82 824.54
Block changes: 6,720.68 60.55
Physical reads: 5,644.92 50.85
Physical writes: 464.97 4.19
User calls: 922.79 8.31
Parses: 342.37 3.08
Hard parses: 1.52 0.01
Sorts: 324.18 2.92
Logons: 2.66 0.02
Executes: 2,131.75 19.20
Transactions: 111.00

% Blocks changed per Read: 7.34 Recursive Call %: 78.48
Rollback per transaction %: 22.43 Rows per Sort: 15.89

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.66 Redo NoWait %: 100.00
Buffer Hit %: 93.86 In-memory Sort %: 100.00
Library Hit %: 99.95 Soft Parse %: 99.56
Execute to Parse %: 83.94 Latch Hit %: 99.79
Parse CPU to Parse Elapsd %: 80.28 % Non-Parse CPU: 98.76

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 96.25 96.64
% SQL with executions>1: 34.19 32.67
% Memory for SQL w/exec>1: 39.87 40.47

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 10,406 42.54
db file sequential read 1,707,372 4,282 17.51
global cache cr request 2,566,822 2,369 9.68
db file scattered read 1,109,892 1,719 7.03
SQL*Net break/reset to client 17,287 1,348 5.51
-------------------------------------------------------------
Wait Events for DB: Instance:
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 1,707,372 0 4,282 3 8.5
global cache cr request 2,566,822 3,356 2,369 1 12.8
db file scattered read 1,109,892 0 1,719 2 5.5
SQL*Net break/reset to clien 17,287 0 1,348 78 0.1
buffer busy waits 312,198 11 1,082 3 1.6

Message was edited by:
user509266