RE: CPU consumption for single RAC service
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: “oracle-l@xxxxxxxxxxxxx” <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Apr 2015 09:53:00 +0000
Karl,
Nice scripts - but the first two links both point to the one for sql stats.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Karl Arao [karlarao@xxxxxxxxx]
Sent: 01 April 2015 07:51
To: Krishna K
Cc: “oracle-l@xxxxxxxxxxxxx”
Subject: Re: CPU consumption for single RAC service
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<
mailto: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<
http://karlarao.wordpress.com>
Wiki: karlarao.tiddlyspot.com<
http://karlarao.tiddlyspot.com>
Twitter: @karlarao<
http://twitter.com/karlarao>
Other related posts:
