- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: oracle users and resource utilization
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-23-2009 08:12 AM
тАО07-23-2009 08:12 AM
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-23-2009 08:44 AM
тАО07-23-2009 08:44 AM
Re: oracle users and resource utilization
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2009 10:11 PM
тАО07-27-2009 10:11 PM
Solution-> 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2009 10:34 PM
тАО07-27-2009 10:34 PM
Re: oracle users and resource utilization
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=
-Santosh
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2009 10:43 PM
тАО07-27-2009 10:43 PM
Re: oracle users and resource utilization
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-27-2009 11:01 PM
тАО07-27-2009 11:01 PM
Re: oracle users and resource utilization
many thanks to all for your suggestions.
I'm going to try them, mainly the one with Ent. Manager.
Cheers
Leonardo.