General
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle users and resource utilization

SOLVED
Go to solution
LBertoglio
Advisor

oracle users and resource utilization

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.
5 REPLIES
Steven E. Protter
Exalted Contributor

Re: oracle users and resource utilization

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Wim Rombauts
Honored Contributor
Solution

Re: oracle users and resource utilization

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.
SANTOSH S. MHASKAR
Trusted Contributor

Re: oracle users and resource utilization

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
SANTOSH S. MHASKAR
Trusted Contributor

Re: oracle users and resource utilization

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

Re: oracle users and resource utilization

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

Cheers
Leonardo.