Re: CPU usage for an indivdual user

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_at_gmail.com> 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_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