Re: CPU consumption for single RAC service

On Wed, Apr 1, 2015 at 4:56 AM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Karl,
>
> Nice scripts - but the first two links both point to the one for sql
> stats.
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Karl Arao [karlarao_at_gmail.com]
> *Sent:* 01 April 2015 07:51
> *To:* Krishna K
> *Cc:* “oracle-l_at_freelists.org”
>
> *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_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