Oracle Database Discussions

Hi,
I have a oracle 11g database and i am running an sql intensive application on it. The database performance seems to be fine for some time and after that cpu utilization goes upto 100% at this point of time the database does not respond at all for the application requests.

The database is running in AIX 5.1 on a VM setup with 2 CPU and 2 Cores - 4 logical CPU and 16 GB of RAM

I did AWR report during this time and the report says


Host CPU (CPUs: 4 Cores: 2 Sockets: )

Load Average Begin Load Average End %User %System %WIO %Idle
67.95 67.61 95.1 4.1 0.0 0.8
Instance CPU

%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
49.3 49.7 0.0


I also ran the ADDM report and the findings are as below


Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
-------------------- ------------------- ---------------
1 CPU Usage 28.22 | 93.43 1

Findings and Recommendations
----------------------------

Finding 1: CPU Usage
Impact is 28.21 active sessions, 93.43% of total activity.
----------------------------------------------------------
Host CPU was a bottleneck and the instance was consuming 100% of the host CPU.
All wait times will be inflated by wait for CPU.
Host CPU consumption was 100%.

Recommendation 1: Host Configuration
Estimated benefit is 28.22 active sessions, 93.43% of total activity.
---------------------------------------------------------------------
Action
Consider adding more CPUs to the host or adding instances serving the
database on other hosts.
Action
Also consider using Oracle Database Resource Manager to prioritize the
workload from various consumer groups.

I also ran cpu stats

sar -u 1 4 | grep -v AIX


System configuration: lcpu=4 ent=2.00 mode=Uncapped

11:00:55 %usr %sys %wio %idle physc %entc
11:00:56 94 6 0 0 2.00 100.0
11:00:57 96 4 0 0 2.00 100.0
11:00:58 96 4 0 0 2.00 100.0
11:00:59 95 5 0 0 2.00 100.0

Average 95 5 0 0 2.00 100.0

vmstat -l

System configuration: lcpu=4 mem=14336MB ent=2.00

kthr memory page faults cpu large-page
----- ----------- ------------------------ ------------ ----------------------- -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa pc ec alp flp
9 4 2797342 5130 0 0 0 4302 8618 0 469 16249 3023 30 6 62 3 0.72 36.0 0 0

Any help on what needs to be checked??

Thanks
Rajesh