Re: ADDM report

From: Ron Warshawsky <ronwarshawsky_at_yahoo.com>
Date: Thu, 16 Nov 2006 12:35:31 -0800 (PST)
Message-ID: <885819.93310.qm@web30614.mail.mud.yahoo.com>

 

  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_at_gmail.com> 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.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 16 2006 - 14:35:31 CST