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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx> 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@xxxxxxxxx> 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
