Re: CPU usage for an indivdual user

Yes, appreciate the input. It looks like there is no short term solution.So I will look into configuring individual services and having the users change their connection string to use the service specific to their application.

On Wed, Mar 21, 2018 at 3:21 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

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

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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