Re: CPU consumption for single RAC service

From: Karl Arao <karlarao_at_gmail.com>
Date: Wed, 1 Apr 2015 01:51:17 -0500
Message-ID: <CACNsJncGFYqwXry67adjZ0je4VfrCP3ZCJCdWKyDcpG7K1PDVQ_at_mail.gmail.com>

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_at_gmail.com> 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: _at_karlarao <http://twitter.com/karlarao>

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

Received on Wed Apr 01 2015 - 08:51:17 CEST