Re: CPU consumption for single RAC service

From: Karl Arao <karlarao_at_gmail.com>
Date: Wed, 1 Apr 2015 22:43:57 -0500
Message-ID: <CACNsJnc0Yzu5AJ3d-e0yFoz2iy5U3-2D2skpkBPxhUszJXQr+A_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? <-- not sure about this, but you can test it through benchmarking on a test environment. try saturating the box with more than the CPU capacity and if this metric just goes 100% where in fact it is 150% then it's only the DB CPU. Also to get the percentage Host CPU% equivalent of the usage of each service. Check the last two rows of the attached excel sheet. Feel free to modify the script.

The yellow cells are derived from the blue cells: dur_sec - snap duration interval in secs cpus - # of CPUs of the box
cap - CPU capacity in secs
util pct - service cpu usage / capacity

-Karl

On Wed, Apr 1, 2015 at 1:29 PM, Krishna K <krishna.setwin_at_gmail.com> wrote:

> 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>
>>
>

-- 
Karl Arao
Blog: karlarao.wordpress.com
Wiki: karlarao.tiddlyspot.com
Twitter: _at_karlarao <http://twitter.com/karlarao>




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




application/vnd.openxmlformats-officedocument.spreadsheetml.sheet attachment: service_cpupct.xlsx

Received on Thu Apr 02 2015 - 05:43:57 CEST