Re: CPU usage for an indivdual user

Thanks!
There is something wrong with the db_cpu_percent calculation, its giving numbers over 100 in some cases, but I should be able to track that down.

On Wed, Mar 21, 2018 at 3:45 PM, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> I forgot to mention v$sess_time_model:
>
> /*db_cpu_pct,t_sql_pct,t_plsql_pct,t_parse_pct,t_hard_parse_pct,t_failed_parse_pct
> - these columns are percentage of session DB Time*/
> /* You can uncomment SID column to get stats per session */
> with time_model as (
> select s.service_name,s.username,s.module
> --,s.sid
> ,m.stat_name,m.value
> from v$sess_time_model m,v$session s
> where m.sid=s.sid
> and nvl(username,'SYS')!='SYS'
> and m.stat_name in (
> 'DB time'
> ,'DB CPU'
> ,'parse time elapsed'
> ,'hard parse elapsed time'
> ,'sql execute elapsed time'
> ,'failed parse elapsed time'
> ,'PL/SQL execution elapsed time'
> )
> )
> ,agg as (
> select *
> from time_model
> pivot (
> sum(value)
> for stat_name in (
> 'DB time' as db_time
> ,'DB CPU' as db_cpu
> ,'sql execute elapsed time' as t_sql
> ,'PL/SQL execution elapsed time' as t_plsql
> ,'parse time elapsed' as t_parse
> ,'hard parse elapsed time' as t_hard_parse
> ,'failed parse elapsed time' as t_failed_parse
> )
> )
> )
> select
> a.service_name
> ,a.username
> ,a.module
> -- ,a.sid
> ,db_time
> ,round((100/db_time)*db_cpu ,2) as db_cpu_pct
> ,round((100/db_time)*t_sql ,2) as t_sql_pct
> ,round((100/db_time)*t_plsql ,2) as t_plsql_pct
> ,round((100/db_time)*t_parse ,2) as t_parse_pct
> ,round((100/db_time)*t_hard_parse ,2) as t_hard_parse_pct
> ,round((100/db_time)*t_failed_parse,2) as t_failed_parse_pct
> ,db_cpu
> ,t_parse
> ,t_hard_parse
> ,t_failed_parse
> ,t_sql
> ,t_plsql
> from agg a
> order by db_cpu_pct desc
> /
>
>
> On Wed, Mar 21, 2018 at 11:43 PM, Andrew Kerber <andrew.kerber_at_gmail.com>
> wrote:
>
>> Yes, appreciate the input. It looks like there is no short term
>> solution.So I will look into configuring individual services and having the
>> users change their connection string to use the service specific to their
>> application.
>>
>> On Wed, Mar 21, 2018 at 3:21 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>>
>>> I think these are very good and get the session oriented information
>>> about the user.
>>>
>>>
>>>
>>> Applications also drive CPU utilization from the “background” processes.
>>> This may be irrelevant for Andrew’s case, but the amount of CPU usage
>>> driven by a session yet not attributed to that session can vary a lot.
>>>
>>>
>>>
>>> Another metric that may be useful in this case is time occupying various
>>> memory latches, where in addition to any CPU the user may be burning they
>>> may be preventing another session from getting to the CPU for useful work.
>>>
>>>
>>>
>>> Good luck, and I hope what Sayan has highlighted is plenty for your case.
>>>
>>>
>>>
>>> mwf
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freeli
>>> sts.org] *On Behalf Of *Sayan Malakshinov
>>> *Sent:* Wednesday, March 21, 2018 1:00 PM
>>> *To:* Andy Sayer
>>> *Cc:* Andrew Kerber; ORACLE-L
>>> *Subject:* Re: CPU usage for an indivdual user
>>>
>>>
>>>
>>> Hi Andrew,
>>>
>>>
>>>
>>> Usually I use one of these standard ways:
>>>
>>>
>>>
>>> 1. analyze it in real time, for example using v$sessmetric:
>>>
>>>
>>>
>>> select s.username,s.module,m.*
>>>
>>> from v$sessmetric m,v$session s
>>>
>>> where m.session_id=s.sid
>>>
>>> and nvl(username,'SYS')!='SYS';
>>>
>>>
>>>
>>> 2. We can create separate services for different apps/users and use
>>> service statistics: v$service_stats,dba_hist_service_stat
>>>
>>>
>>>
>>> 3. We can enable statistic gathering for any combinations of service
>>> names and modules using dbms_monitor.serv_mod_act_stat_enable and
>>> analyze v$serv_mod_act_stats.
>>>
>>>
>>>
>>> For example we can enable it for all currently connected users:
>>>
>>>
>>>
>>> declare
>>>
>>> e_already_enabled exception;
>>>
>>> pragma exception_init(e_already_enabled, -13864);
>>>
>>> begin
>>>
>>> for r in (select distinct service_name, module
>>>
>>> from v$session s
>>>
>>> where s.username!='SYS')
>>>
>>> loop
>>>
>>> begin
>>>
>>> dbms_monitor.serv_mod_act_stat_enable(
>>>
>>> service_name => r.service_name,
>>>
>>> module_name => r.module
>>>
>>> );
>>>
>>> dbms_output.put_line('"'||r.service_name||'","'||r.module ||'"
>>> - enabled');
>>>
>>> exception
>>>
>>> when e_already_enabled then
>>>
>>> dbms_output.put_line('"'||r.service_name||'","'||r.module
>>> ||'" - e_already_enabled');
>>>
>>> end;
>>>
>>> end loop;
>>>
>>> end;
>>>
>>> /
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Mar 21, 2018 at 7:35 PM, Andy Sayer <andysayer_at_gmail.com> wrote:
>>>
>>> Hi Andrew
>>>
>>>
>>>
>>> This may be over thinking it, or it might be a perfect set up for what
>>> you’ll end up doing anyway.
>>>
>>>
>>>
>>> You can create resource manager consumer group mapping’s for each of
>>> your application usernames, each user gets defaulted to a different
>>> consumer group. Their cpu stats (and other info) will get aggregated by
>>> consumer group and sent to v$rsrcMgrMetric and v$rsrcMgrMetric_history.
>>>
>>>
>>>
>>> You can then use this information to plan a resource management plan if
>>> you wished.
>>>
>>>
>>>
>>> Hope that helps,
>>>
>>> Andrew
>>>
>>>
>>>
>>> On Wed, 21 Mar 2018 at 15:51, Andrew Kerber <andrew.kerber_at_gmail.com>
>>> wrote:
>>>
>>> I have a case where I need to find out how much CPU individual oracle
>>> users are using. It looks like we have a high percentage of dynamic sql
>>> that is causing a lot of CPU usage due to hard parsing, but we are having
>>> trouble nailing it down to an individual application. Each application runs
>>> under its own username, so I need some way to figure out the CPU
>>> utilization for each username. Any ideas?
>>>
>>>
>>>
>>> --
>>>
>>> Andrew W. Kerber
>>>
>>> 'If at first you dont succeed, dont take up skydiving.'
>>>
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Best regards,
>>> Sayan Malakshinov
>>>
>>> Oracle performance tuning engineer
>>>
>>> Oracle ACE Associate
>>> http://orasql.org
>>>
>>
>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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