Home -> Mailing Lists -> Oracle-L -> RE: CPU usage for an indivdual user

RE: CPU usage for an indivdual user

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








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_freelists.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




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


Received on Wed Mar 21 2018 - 21:21:03 CET












Original text of this message