ADDM report
- From: "Derya Oktay" <deryaoktay@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Nov 2006 12:46:02 +0200
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:
