Home » RDBMS Server » Server Administration » cpu usage by a db
cpu usage by a db [message #563725] Thu, 16 August 2012 11:33 Go to next message
akh555
Messages: 3
Registered: August 2012
Junior Member
Hi,

I have three oracle 10g databases (db1, db2, db3)on a linux box.

can anyone help me with a command at the OS level to find total cpu used by each of the above databases.

thank you for your help in advance.
Re: cpu usage by a db [message #563728 is a reply to message #563725] Thu, 16 August 2012 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
>can anyone help me with a command at the OS level to find total cpu used by each of the above databases.
Does not exist.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Thu, 16 August 2012 11:41]

Report message to a moderator

Re: cpu usage by a db [message #563729 is a reply to message #563728] Thu, 16 August 2012 12:09 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
As a sort of "starting point" here is the process we use when we want to figure out "why the hell are the disk drives trashing":

1) we have a look with atop which oracle process is doing the trashing.
/forum/fa/10365/0/

2) we look find out which session that is with the following query:

SELECT DISTINCT 
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.client_info,
       sql_text
 FROM  v$session s
  JOIN v$process p 
    ON p.addr = s.paddr
       left outer join v$sql q ON q.SQL_ID = s.SQL_ID
WHERE  spid = 4544 


Where spid is the OS process ID that we found in step 1 (which in this case also was responsible for the 41% CPU load.

So it might be possible to write a script that dumps the information obtained from v$process and v$session from all three instances and combines that with the CPU information obtained by top or atop or any other tool that can get CPU loads per process.
Re: cpu usage by a db [message #563734 is a reply to message #563729] Thu, 16 August 2012 12:38 Go to previous messageGo to next message
akh555
Messages: 3
Registered: August 2012
Junior Member
Thomas,

thanks for your quick reply and your suggestion is very useful. however i was wondering
if there is a way to use the 'ps -e ' command with some other options
to get the total cpu usage of a particular oracle database instance.

Re: cpu usage by a db [message #563735 is a reply to message #563734] Thu, 16 August 2012 12:40 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
>if there is a way to use the 'ps -e ' command with some other options
>to get the total cpu usage of a particular oracle database instance.

OS knows nothing about any such "database instance"; which is a collection of cooperating OS processes.
Re: cpu usage by a db [message #563738 is a reply to message #563735] Thu, 16 August 2012 12:48 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
As BlackSwan said, the OS does know nothing about which process ID belongs to which instance. On the other hand, Oracle knows nothing about the actual load on the CPUs.

So there is no ready-made built-in way to get that information.
Re: cpu usage by a db [message #563762 is a reply to message #563738] Thu, 16 August 2012 18:48 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I have 3 databases sharing the same physical box. I use the following sql
to track daily logical reads (CPU usage) verses Physical reads (I/O Waits).
ENWEBP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                     LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- ---------- ---------- ---------
NWEBP     2012-08-08 Wednesday 6308643744   73509364      98.8
NWEBP     2012-08-09 Thursday  7946062816   78316516      99.0
NWEBP     2012-08-10 Friday    7330510848   81205405      98.9
NWEBP     2012-08-11 Saturday  6988093088   85095405      98.8
NWEBP     2012-08-12 Sunday    6952708240  211627037      97.0
NWEBP     2012-08-13 Monday    7728220896  266624156      96.7
NWEBP     2012-08-14 Tuesday   7174948288  145966131      98.0
NWEBP     2012-08-15 Wednesday 6764335664   82613590      98.8
NWEBP     2012-08-16 Thursday  4191946624   53904206      98.7

ENWEBP1P > @e ndocp1p
Connected.
ENDOCP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                     LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- ---------- ---------- ---------
NDOCP     2012-08-08 Wednesday 1459664624   93145496      94.0
NDOCP     2012-08-09 Thursday  1558740016  102841429      93.8
NDOCP     2012-08-10 Friday    1572944736  107151108      93.6
NDOCP     2012-08-11 Saturday  1621583904   89352225      94.8
NDOCP     2012-08-12 Sunday    1415023152   82021291      94.5
NDOCP     2012-08-13 Monday    1541693696   90910527      94.4
NDOCP     2012-08-14 Tuesday   1982910096  704681299      73.8
NDOCP     2012-08-15 Wednesday 1278312160   78303441      94.2
NDOCP     2012-08-16 Thursday   798850736   40793303      95.1

ENDOCP1P > @e nalfp1p
Connected.
ENALFP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                     LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- ---------- ---------- ---------
NALFP     2012-08-08 Wednesday   79650960     136172      99.8
NALFP     2012-08-09 Thursday    97558736     190804      99.8
NALFP     2012-08-10 Friday     140384592     345480      99.8
NALFP     2012-08-11 Saturday    38125824     121594      99.7
NALFP     2012-08-12 Sunday      51670896      11068     100.0
NALFP     2012-08-13 Monday      82026656     320363      99.6
NALFP     2012-08-14 Tuesday     46048560      29682      99.9
NALFP     2012-08-15 Wednesday  120670096     301510      99.8
NALFP     2012-08-16 Thursday    36570144       8372     100.0

ENALFP1P > list
  1  select d.name database,l.dat "Day",total_logical logical,total_physical physical,
  2  total_logical/(total_physical+total_logical)*100 Hit_ratio
  3  from alan_physical p, alan_logical l,v$database d
  4* where p.dat=l.dat
Re: cpu usage by a db [message #563763 is a reply to message #563762] Thu, 16 August 2012 18:50 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
If I have a problem with the cpu high on a box, I do a snapshot that
shows me the physical and logical reads per minute on each instance
to identify the problem instance.
http://www.orafaq.com/forum/mv/msg/183335/563279/0/#msg_563279
Re: cpu usage by a db [message #563764 is a reply to message #563763] Thu, 16 August 2012 19:22 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I left out part of the sql to get the daily hit ratios. The full sql follows.
SET LINES 200
SET WRAP OFF
col "DAY" for a20
col hit_ratio for 999.9
set termout off
drop table alan_logical purge;
drop table alan_physical purge;
break on report
compute sum of total_daily_logical_reads on report
create table alan_logical as
select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAT",
sum(b.logical_READS_DELTA) total_logical
from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
where a.object_id=b.OBJ#
and b.logical_READS_DELTA>0
and c.instance_number=(select instance_number from v$instance)
and c.snap_id=b.snap_id
group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
order by 1,2
/
create table alan_physical as
select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAT",
sum(b.physical_READS_DELTA) total_physical
from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
where a.object_id=b.OBJ#
and b.physical_READS_DELTA>0
and c.instance_number=(select instance_number from v$instance)
and c.snap_id=b.snap_id
group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
order by 1,2
/
set termout on
select d.name database,l.dat "Day",total_logical logical,total_physical physical,
total_logical/(total_physical+total_logical)*100 Hit_ratio
from alan_physical p, alan_logical l,v$database d
where p.dat=l.dat;
Re: cpu usage by a db [message #564613 is a reply to message #563764] Sun, 26 August 2012 12:44 Go to previous messageGo to next message
akh555
Messages: 3
Registered: August 2012
Junior Member
Thanks guys for your quick response ... the above queries are very helpful ! Smile
Re: cpu usage by a db [message #564708 is a reply to message #564613] Mon, 27 August 2012 14:15 Go to previous messageGo to next message
John Watson
Messages: 8319
Registered: January 2010
Location: Global Village
Senior Member
You might have more answers if you had said what operating system you use. Let's assume it is Linux.
Assuming that each database instance is running under a different user (as it should be), then use top to extract the processes run by that user, and send the results to awk to sum up the CPU usage. So for user prod,

top -b -n1 -u prod | awk '{ sum += $9; } END { print sum; }'


Re: cpu usage by a db [message #564709 is a reply to message #564708] Mon, 27 August 2012 14:22 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
>Assuming that each database instance is running under a different user (as it should be),

where is this documented?
I ask because I have always just run every DB from OS user "oracle";
regardless of the number of DBs on any given system.
Re: cpu usage by a db [message #564710 is a reply to message #564709] Mon, 27 August 2012 14:28 Go to previous message
John Watson
Messages: 8319
Registered: January 2010
Location: Global Village
Senior Member
So that you can do things like summing up CPU usage per user! I would think that security and separation of duties would be important too. I have had it drilled into me for as long as I can remember that shared accounts (for OS or database) are a no-no. Purely an administration thing of course, no technical significance.
Previous Topic: Hash_Area_Size Unit in "show parameter"
Next Topic: Ora Errors
Goto Forum:
  


Current Time: Mon Aug 10 04:01:26 CDT 2020