Re: CPU consumption for single RAC service

On Wed, Apr 1, 2015 at 10:44 PM Karl Arao <karlarao_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?
> <-- 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