RE: CPU usage for an indivdual user

From: Mark W. Farnham <>
Date: Wed, 21 Mar 2018 16:21:03 -0400
Message-ID: <027f01d3c152$22c485a0$684d90e0$>

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.  


From: [] 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:  


  e_already_enabled exception;

  pragma exception_init(e_already_enabled, -13864);


   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 ||'" - enabled');


         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,


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 - 21:21:03 CET

Original text of this message