Re: ADDM report

 
  Hello Derya,
   
    I would suggest to generate statspack or AWR report for the problem time 
period and review what is the SQL/module that is causes CPU spike.
   
    Also you can compare "problem" time period with the "okay" time period to 
see exact differences - I use Performance Explorer-i for this.
   
  Thanks,
   
    Ron
   
  
Derya Oktay <deryaoktay@xxxxxxxxx> wrote:
  Hello,
We have a 2-node 10g RAC running on IBM P5 Series having 16 CPU each.

DETAILED ADDM REPORT FOR TASK 'TASK_12907' WITH ID 12907 
-------------------------------------------------------- 
Analysis Period: 14-NOV-2006 from 09:01:00 to 12:00:15 
Database ID/Instance: 3099024518/1 
Database/Instance Names: UYBS/UYBS1 
Host Name: db1 
Database Version: 10.1.0.4.0 
Snapshot Range: from 4795 to 4798 
Database Time: 122489 seconds 
Average Database Load: 11.4 active sessions 

FINDING 1: 65% impact (79164 seconds) 
------------------------------------- 
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. 
RECOMMENDATION 1: Host Configuration, 58% benefit (70549 seconds) ACTION: 
Consider adding more CPUs to the host or increasing the number of instances 
serving the database. 
RECOMMENDATION 2: SQL Tuning, 3.4% benefit (2886 seconds) ACTION: Run SQL 
Tuning Advisor on the SQL statement with SQL_ID "3dgqrgtasgupq". RELEVANT 
OBJECT: SQL statement with SQL_ID 3dgqrgtasgupq and PLAN_HASH 2542325397 
...


It seems to me a wrong advice from Oracle. 
I think Oracle is missing the number of CPUs, and when it sees the Average 
Database Load as11.4 active sessions, 
it considered that the system needs more CPU because it is more than 1 active 
session. I come to this point that "the average database load = Database Time / 
Analysis Period". This maybe as "the average database load = Database Time / 
Analysis Period / # of CPUS".
Also when we look at the OS point of view, average load of 16 CPUs in one node 
never reaches to %100. But the CPUs individually may have loads of %100 lasting 
little time. 
Am I correct that Oracle is wrong?
Regards,
Derya.

Other related posts: