Database Software

Hi All,

I am working on 4node 11gR2 RAC database and i took AWR report for one of my issue and not able to find the analyze what i need from this report. I have Few questions.

Below is some of my awr report.

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
qysqalccoredb03. Linux x86 64-bit 8 8 2 31.31

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 41218 28-Sep-11 00:30:12 56 1.1
End Snap: 41226 28-Sep-11 02:30:04 57 1.4
Elapsed: 119.88 (mins)
DB Time: 653.97 (mins)

Cache Sizes Begin End
~~~~~~~~ ---------- ----------
Buffer Cache: 8,448M 8,448M Std Block Size: 8K
Shared Pool Size: 1,600M 1,600M Log Buffer: 55,752K

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 36,085 92.0
log file sync 172,936 564 3 1.4 Commit
latch: cache buffers chains 370,933 485 1 1.2 Concurrenc
SQL*Net more data from client 172,713 170 1 .4 Network
SQL*Net message to client 4,671,686 8 0 .0 Network
^LHost CPU (CPUs: 8 Cores: 8 Sockets: 2)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
2.15 9.36 76.1 0.7 0.2 22.8

Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 62.9
% of busy CPU for Instance: 81.5
%DB time waiting for CPU - Resource Mgr: 0.0

Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 32,061.3 32,061.3
SGA use (MB): 10,432.0 10,432.0
PGA use (MB): 302.2 312.7
% Host Mem used for SGA+PGA: 33.48 33.51

^LRAC Statistics DB/Inst: CCOREQAL/ccoreqal3 Snaps: 41218-41226

We have total 6 databases installed on these servers.

I have two questions.

1.How to find the CPU capacity and RAM allocated for a each database?
2.In AWR report There are some Memory and instance CPU statiostics, what are those representing?

I have ran some of the queries in one of the database and got the below results, i am confused which one is correct for the memory and not able to find the CPU allocated for this DB.

SYS@ccoreqal3 SQL>select sum(bytes)/1024/1024/1024 from GV$SGASTAT;

SUM(BYTES)/1024/1024/1024
-------------------------
29.3142291

SYS@ccoreqal3 SQL>select sum(bytes)/1024/1024/1024 from v$sgastat;

SUM(BYTES)/1024/1024/1024
-------------------------
10.2035345

SYS@ccoreqal3 SQL>show sga

Total System Global Area 1.6702E+10 bytes
Fixed Size 2219952 bytes
Variable Size 7717519440 bytes
Database Buffers 8925478912 bytes
Redo Buffers 57090048 bytes


From this which one i need to consider. Please let me know.

Thanks in advance.