Re: CPU usage for an indivdual user
Date: Wed, 21 Mar 2018 23:45:04 +0300
Message-ID: <CAOVevU6pOVgRVA=pOsszXudChjALxLzsv=5ByEHCEf-OouyF+Q_at_mail.gmail.com>
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
select *
On Wed, Mar 21, 2018 at 11:43 PM, Andrew Kerber <andrew.kerber_at_gmail.com>
wrote:
--,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 (
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
/
> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 21 2018 - 21:45:04 CET