Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ADDM report

Re: ADDM report

From: John Kanagaraj <john.kanagaraj_at_gmail.com>
Date: Thu, 16 Nov 2006 16:08:59 -0800
Message-ID: <2ead3a60611161608n2456cdf3r9cc62938bf6add65@mail.gmail.com>


Derya,

ADDM uses "DB Time" as a common currency to compare work done within the database. You had a total of (3 hr * 60 min * 60 seconds = 10800 seconds) * (16 CPUs/node) = 172800 usable CPU seconds available per node. Of this, your "DB Time" was 122,489 seconds or about 70%, so ADDM reported that as a high value. The issue you have, and this is valid, is that you expected this work to be shared across two nodes, but ADDM did not understand that, possibly because the "CPU_COUNT" would still be 16 when the ADDM report module checks this value. So you are correct in that Oracle is calculating this incorrectly. However, that does not change the fact that there is extremely high cpu usage and we will look at that a little later. [Btw, Can you check if your 'sar' collected the same information? (sar -uq would would report both CPU usage and Queue size information).]

The point is that "DB Time" is a counter that combines all of the CPU time used in parsing, executing and waiting, and if this is high (as it seems to be on your report), then you should investigate it further. As pointed out in recommendation 2, you should look at the various Top SQLs named by their SQL_IDs. (You don't need to use the DB Control GUI to get this, but that may be the easiest route). Tuning them will reduce the "DB Time" and hence ease the system.

I have covered some of the background and +/- of ADDM in Section III of the "Oracle 10g Insider Solutions" if you are interested in reading more. Look at v$SYS_TIME_MODEL and V$SESS_TIME_MODEL for more details, and the history of these in the AWR. (Read the book is all I can say now :)

ADDM is not perfect as you can see, but the AWR/ADDM/ASH/Metrics + Performance Manageability Framework that Oracle has provided in 10g is a major step forward.

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is always inevitable; Discouragement is invariably optional

--

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 16 2006 - 18:08:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US