Re: CPU usage for an indivdual user

From: Sayan Malakshinov <>
Date: Wed, 21 Mar 2018 20:00:16 +0300
Message-ID: <>

 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:

  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')
            service_name => r.service_name,
            module_name  => r.module
         dbms_output.put_line('"'||r.service_name||'","'||r.module ||'" -
         when e_already_enabled then
            dbms_output.put_line('"'||r.service_name||'","'||r.module ||'"
- e_already_enabled');

   end loop;

On Wed, Mar 21, 2018 at 7:35 PM, Andy Sayer <> 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 <>
> 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

Received on Wed Mar 21 2018 - 18:00:16 CET

Original text of this message