Re: CPU consumption for single RAC service

From: Krishna K <krishna.setwin_at_gmail.com>
Date: Wed, 01 Apr 2015 18:29:32 +0000
Message-ID: <CAF6DRCqExLQNrY2kfv2Lf-c92rSTq-6ky5mPLoN46qa9K-gKOA_at_mail.gmail.com>

In OEM 12c for the database, we have Database service --> % CPU Time load The service_name has average value. is that CPU consumed at host level?

On Wed, Apr 1, 2015 at 12:57 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>
> Karl,
>
> You're absolutely right (of course).
> I must have been looking at one URL while clicking on the other - or doing
> something equally bizarre. I even checked three times before sending the
> email !
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* Karl Arao [karlarao_at_gmail.com]
> *Sent:* 01 April 2015 15:34
> *To:* Jonathan Lewis
>
> *Cc:* “oracle-l_at_freelists.org”
> *Subject:* Re: CPU consumption for single RAC service
> Thanks! I think those are correct URLs, I'm also attaching the first
> two scripts
>
> -Karl
>
> On Wed, Apr 1, 2015 at 4:53 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>
>>
>
>
>
> --
> 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 - 20:29:32 CEST