Re: CPU consumption for single RAC service
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: Krishna K <krishna.setwin@xxxxxxxxx>
- Date: Wed, 1 Apr 2015 01:51:17 -0500
I would first use the DBA_HIST_SERVICE_STAT, and I use this script
https://db.tt/j2przPCu
it shows the following columns per service
db_time
db_cpu
phy_reads
log_reads
aas
And then, if I want to drill down a bit further to see the workload
distribution across app schemas I would make use of DBA_HIST_SQLSTAT here
https://db.tt/43Z5ccuu
usually you would be able to tell the distribution of workload by
parse_schema or module then you can break it down by sql type, sql_id,
instance. Of course your measure here would be the CPU and AAS.
I would correlate the two data set to get a good workload characterization
across the services and the apps. There's a time series component so you
should be able to easily drill down on specific time periods.
But take note that 'DB CPU' is just the accounted CPU. There's an
unaccounted-for DB Time component which is the CPU Wait. And CPU time spent
on resource manager. And with those scripts above I don't factor that in. I
usually run this modified top events to get the CPU Wait
https://db.tt/5hAzJES9 and validate it with this other script
https://db.tt/xdMfYXVe to get the view from the OS level (OSSTAT). So you
should be able to tell which nodes is hitting the CPU capacity and if you
need to place the services to lower utilization nodes.
Also, the DBA_HIST_ACTIVE_SESS_HISTORY view have a SERVICE_HASH column. if
you really want a fine grained data where the total CPU accounting is
factored in. because ASH instruments the CPU Wait as "on cpu". You can dump
the ASH data just like what I did here
http://bit.ly/1ujUfzn and then mine
it.
But if you are just after the relative distribution of workload the first
two scripts would be fine.
-Karl
On Tue, Mar 31, 2015 at 11:53 AM, Krishna K <krishna.setwin@xxxxxxxxx>
wrote:
Hi Guys,
I have a question regarding CPU consumption for RAC services.
My environment is RAC 8 node cluster. I have a service which is running on
4 nodes. we have nearly 60+ services on all 8 nodes.
I want to get CPU consumed by 1 service for the past 7 days. can anybody
point me in right direction?
Thanks in advance.
krishna
--
Karl Arao
Blog: karlarao.wordpress.com
Wiki: karlarao.tiddlyspot.com
Twitter: @karlarao <
http://twitter.com/karlarao>
Other related posts:
