Re: CPU usage for an indivdual user
Date: Wed, 21 Mar 2018 20:00:16 +0300
Message-ID: <CAOVevU6vod4=qtejMM8vrQwUcWK_Mh1RT4bZxG+XQpuMU0OO5A_at_mail.gmail.com>
Hi Andrew,
Usually I use one of these standard ways:
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 21 2018 - 18:00:16 CET