- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: How to trace which user slow down oracle perfo...
Operating System - HP-UX
1753485
Members
4859
Online
108794
Solutions
Forums
Categories
Company
Local Language
юдл
back
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
юдл
back
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
Blogs
Information
Community
Resources
Community Language
Language
Forums
Blogs
Topic Options
- 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
тАО06-04-2007 09:26 PM
тАО06-04-2007 09:26 PM
Re: How to trace which user slow down oracle performance
hi,
It seems that the application using a specific username to run the programs/forms.
to find what SQL statement the "culprit" session is using, run:
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE username = &uname AND SID = &snum)
ORDER BY piece
If you take the uploading data, it would be something like:
e.g.
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE username = 'APPS' AND SID = '116')
ORDER BY piece
It will be quite difficult to trace back the user. You may also wish to verify at the application server level as well.
There should be a monitoring tool at the Oracle Application Level also.
hope this helps!
kind regards
yogeeraj
It seems that the application using a specific username to run the programs/forms.
to find what SQL statement the "culprit" session is using, run:
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE username = &uname AND SID = &snum)
ORDER BY piece
If you take the uploading data, it would be something like:
e.g.
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE username = 'APPS' AND SID = '116')
ORDER BY piece
It will be quite difficult to trace back the user. You may also wish to verify at the application server level as well.
There should be a monitoring tool at the Oracle Application Level also.
hope this helps!
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-04-2007 09:30 PM
тАО06-04-2007 09:30 PM
Re: How to trace which user slow down oracle performance
Hi,
If the PID belongs to a concurrent request you can use the sql below to know the user who submitted it.
SELECT DISTINCT fu.user_name
FROM v$process vp,
v$session vs,
v$sess_io vio,
v$transaction vt,
fnd_concurrent_requests fcr,
fnd_user fu
WHERE vp.addr = vs.paddr
AND vs.SID = vio.SID
AND vs.taddr = vt.addr(+)
AND fcr.os_process_id = vs.process
AND fu.user_id = fcr.requested_by
and vp.spid='13264' --DATABASE PID
Regards, David.
If the PID belongs to a concurrent request you can use the sql below to know the user who submitted it.
SELECT DISTINCT fu.user_name
FROM v$process vp,
v$session vs,
v$sess_io vio,
v$transaction vt,
fnd_concurrent_requests fcr,
fnd_user fu
WHERE vp.addr = vs.paddr
AND vs.SID = vio.SID
AND vs.taddr = vt.addr(+)
AND fcr.os_process_id = vs.process
AND fu.user_id = fcr.requested_by
and vp.spid='13264' --DATABASE PID
Regards, David.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-05-2007 09:32 AM
тАО06-05-2007 09:32 AM
Re: How to trace which user slow down oracle performance
I do these 3 steps:
1. Run top to get the heavyest process's PID,
2. then use ps -ef|grep to get related processes,
3. finally use Oracle's Enterprise Manager Console to find the session that matches one of the PIDs gotten in step 2.
You can edit the session (double click on it) to see the SQL text, execution plan, cost, etc...
Another thing to check: Usually the poor performance is caused by disk I/O contention, use "iostat" to check that condition.
Good luck.
(excuse me for my English hehe)
1. Run top to get the heavyest process's PID,
2. then use ps -ef|grep
3. finally use Oracle's Enterprise Manager Console to find the session that matches one of the PIDs gotten in step 2.
You can edit the session (double click on it) to see the SQL text, execution plan, cost, etc...
Another thing to check: Usually the poor performance is caused by disk I/O contention, use "iostat" to check that condition.
Good luck.
(excuse me for my English hehe)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-05-2007 04:07 PM
тАО06-05-2007 04:07 PM
Re: How to trace which user slow down oracle performance
use extract command to get the CPU/DISK/Mem deataisll. THis really helps to fidn out the slow ness from DB level. ALso refer the statspack data and look for any wait events.
extract -p -xp -b 06/04/07 23:00 -e 06/05/07 05:00 -r /var/opt/perf/reptproc_io -f extract_0604_11PM_5AM.out
extract -p -xp -b 06/04/07 23:00 -e 06/05/07 05:00 -r /var/opt/perf/reptproc_io -f extract_0604_11PM_5AM.out
- « Previous
-
- 1
- 2
- Next »
The opinions expressed above are the personal opinions of the authors, not of Hewlett Packard Enterprise. By using this site, you accept the Terms of Use and Rules of Participation.
News and Events
Support
© Copyright 2024 Hewlett Packard Enterprise Development LP