Measure CPU utilization for a database

From: Sreejith S Nair <Sreejith.Sreekantan_at_ibsplc.com>
Date: Wed, 6 Apr 2011 14:54:14 +0530
Message-ID: <OFBB94AE87.2D5DD4A4-ON6525786A.00339F16-6525786A.0033A80C_at_ibsplc.com>

Friends,

I have 7 RAC database (10.2.0.4.0) running on two SunOS 5.10 Generic nodes. I would like to measure the CPU utilized by each *database* in these servers. I have arrived at the following calculations. Just wanted to verify whether my calculation is correct.

We thought, the best way to determine this is from the snapshots generated for each one hour by AWR.

{code}

SQL> select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where rownum<4 order by BEGIN_INTERVAL_TIME desc;

   SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME

---

---

---

     12885 03-APR-11 06.00.21.756 AM             03-APR-11 07.00.46.929 AM
     12869 02-APR-11 02.00.21.914 PM             02-APR-11 03.00.22.796 PM
     12868 02-APR-11 01.00.27.588 PM             02-APR-11 02.00.21.914 PM

{code}

I got snap_id for each one hour snapshots and from that I measure DB CPU in microseconds which gives me the CPU consumption for the database (not instance).
{code}

SQL> select SNAP_ID,STAT_NAME,VALUE from dba_hist_sys_time_model where SNAP_ID in (select SNAP_ID from dba_hist_snapshot where rownum<4)   2 and stat_name like '%CPU%' order by snap_id;

   SNAP_ID STAT_NAME VALUE

---

---

---

     12868 DB CPU  1.7605E+11
     12868 DB CPU  1.4244E+11
     12869 DB CPU  1.4246E+11
     12869 DB CPU  1.7609E+11
     12885 DB CPU  1.7692E+11
     12885 DB CPU  1.4333E+11

6 rows selected.
{code}

I see this as one way which I can run on all cluser dbs running here to find the CPU utlization.

I have also came across another calculation which gives the 'Host CPU Utlization'
{code}

SQL> select BEGIN_TIME,END_TIME,METRIC_NAME,MAXVAL from dba_hist_sysmetric_summary where rownum < 20 and metric_name like '%Host CPU Utilization%'
  2 order by maxval desc;

BEGIN_TIME END_TIME METRIC_NAME MAXVAL

----------------- ----------------- 
---------------------------------------------------------------- 
----------
31-MAR-2011 10:59 31-MAR-2011 12:00 Host CPU Utilization (%)   16.9292192
01-APR-2011 05:00 01-APR-2011 06:00 Host CPU Utilization (%)   16.2485165
31-MAR-2011 05:59 31-MAR-2011 07:00 Host CPU Utilization (%)   15.0713106
04-APR-2011 08:00 04-APR-2011 09:00 Host CPU Utilization (%)   10.9922138
29-MAR-2011 01:00 29-MAR-2011 01:59 Host CPU Utilization (%)   9.74810034
01-APR-2011 23:00 01-APR-2011 23:59 Host CPU Utilization (%)   9.62090932
31-MAR-2011 00:00 31-MAR-2011 01:00 Host CPU Utilization (%)   9.15222482
01-APR-2011 23:59 02-APR-2011 00:59 Host CPU Utilization (%)   9.06441271
02-APR-2011 03:00 02-APR-2011 03:59 Host CPU Utilization (%)   8.93059763

29-MAR-2011 05:59 29-MAR-2011 07:00 Host CPU Utilization (%) 8.8866913 29-MAR-2011 07:00 29-MAR-2011 08:00 Host CPU Utilization (%) 8.71551993

BEGIN_TIME END_TIME METRIC_NAME MAXVAL

----------------- ----------------- 
---------------------------------------------------------------- 
----------
01-APR-2011 03:00 01-APR-2011 03:59 Host CPU Utilization (%)   8.71339489
31-MAR-2011 23:00 01-APR-2011 00:00 Host CPU Utilization (%)   8.52208852
04-APR-2011 02:59 04-APR-2011 04:00 Host CPU Utilization (%)   8.44670199
29-MAR-2011 01:59 29-MAR-2011 03:00 Host CPU Utilization (%)   8.34799263
05-APR-2011 01:00 05-APR-2011 01:59 Host CPU Utilization (%)   8.23198162
04-APR-2011 23:00 05-APR-2011 00:00 Host CPU Utilization (%)   7.16664932

30-MAR-2011 16:59 30-MAR-2011 17:59 Host CPU Utilization (%) 7.07950784 30-MAR-2011 15:00 30-MAR-2011 16:00 Host CPU Utilization (%) 6.48964794
{code}

The documentation says
-http://download.oracle.com/docs/cd/B19306_01/em.102/b25986/rac_database.htm#sthref300 this is a database level metric. I am unclear on 1 ) Whether this is the host cpu utlization at a particular time ( not for each database, which is what I am looking for ) 2 ) If it is just host cpu utlization, I assume I will get same value when I run this for the same period on alll db running in these hosts. 3 ) How can we clearly measure the CPU consumption of the database, if it is part of a cluster in which there is a chance of CPU of one server getting used sometimes.

Any hints / advice are appreciated.

Thanks
SSN DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 06 2011 - 04:24:14 CDT