Hi,
ASH captures USER_ID, it should be simple to get the estimates from there
if diag pack is licensed.
Maris
On Wed, Mar 21, 2018, 19:01 Sayan Malakshinov <xt.and.r@xxxxxxxxx> wrote:
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
