How to trace which user slow down oracle performance

[Yogeeraj_1]

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

[David_616]

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.

[Leopoldo González]

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)

[skt_skt]

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