topic Re: oracle users and resource utilization in Operating System - HP-UX

oracle users and resource utilization

LBertoglio — Thu, 23 Jul 2009 15:12:46 GMT

Hi.
I have 1 hpux server (11.23) running 1 oracle 10.2 instance with 2 schemas (or users) in it, say "A" and "B". How can I understand how much CPU is consuming user A and how much user B ?
e.g if I see 60% used CPU by an oracle user process, how can I see which user is consuming it, and how much?
Many thanks in advance.
Leonardo.

Re: oracle users and resource utilization

Steven E. Protter — Thu, 23 Jul 2009 15:44:11 GMT

Shalom,

Oracle has a set of utilities called the stat pack. It keep statistics based on oracle users, not OS users.

This can give you an idea on resource use.

It will not help with actual CPU use, because user requests for all types of oracle request are submitted to the database which is a server process owned by the oracle user on the OS that you used to install the database.

SEP

Re: oracle users and resource utilization

Wim Rombauts — Tue, 28 Jul 2009 05:11:17 GMT

Oracle 19g R2 has a resource manager. You can easily confiure this using the Enterprise Manager (DBConsole is sufficient).
-> Create a Consumer group for every schema.
-> Assign each schema (and all other possible database accounts using this schema) to it's consumer group, and make sure this consumer group is also the default group for this schema.
-> Make a Resource Plan and put the 2 new consumer groups in there, together with mandatory consumer groups like OTHER_GROUPS and SYS_GROUP and activate the plan.

can use a query like this to have an idea of CPU usage :

select gv$rsrc_consumer_group.inst_id as inst_id, gv$rsrc_consumer_group.name, consumed_cpu_time, round(1000*consumed_cpu_time/total.cpu_time)/10 as cpu_perc, history.start_time
from gv$rsrc_consumer_group,
(select sum(consumed_cpu_time) as cpu_time from gv$rsrc_consumer_group) total,
(select inst_id, max(start_time) as start_time from gv$rsrc_plan_history
group by inst_id) history
where gv$rsrc_consumer_group.inst_id = history.inst_id

Of course, CPU usage isn't everything in a database. disk IO is another matter that may have a big impact.

Re: oracle users and resource utilization

SANTOSH S. MHASKAR — Tue, 28 Jul 2009 05:34:25 GMT

Hi,

If u know PID of process, then query the views v$session and v$process to know the user having that PID.

eg.

SQL> select a.username,a.schemaname,b.username from v$session a, v$process b where b.spid= and a.paddr=b.addr;

-Santosh

Re: oracle users and resource utilization

SANTOSH S. MHASKAR — Tue, 28 Jul 2009 05:43:29 GMT

Hi,

Since u use oracle 10.2, u can use oracle Enterprise Manager DBControl to get all the details u want using a mouse click.

I would strongly recommend to use Oralce EM for DBA work.

-Santosh

Re: oracle users and resource utilization

LBertoglio — Tue, 28 Jul 2009 06:01:32 GMT

Hi ,
many thanks to all for your suggestions.
I'm going to try them, mainly the one with Ent. Manager.

Cheers
Leonardo.