Home » RDBMS Server » Performance Tuning » Find out what query is running for specific session? (oracle 9.2.0.8)
Find out what query is running for specific session? [message #349080] Thu, 18 September 2008 16:31 Go to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Hello All,

One my business user is complaining that,
when he click the specific link in the website,
it is running slow. What i want to do is,
i need to capture the sql code during the
click and see what is wrong on that select statement.

I run a query(joined with v$sqlarea, v$session,
v$session_io) and it is showing several SQL CODE.
How can i find out which sqlcode is related to
the specific user. One option would
be machine name. The java code is hitting the
application server and application server points
to database.
So the machine name is displayed in v$session
as application server name. Many people are
using the website and it is hard to identify
the specific user. Another option is SID.
v$sqlarea is showing many sql code and i am
not able find what is the SID for that user.

Finally, i asked developer to dig the java code
to find out what sql code is running in the
specific link.
He will get back to me soon. But my quesiton is,
Is there any other way, i can track what query
is running in the database while clicking the link??

Any comments appreciated.
Re: Find out what query is running for specific session? [message #349081 is a reply to message #349080] Thu, 18 September 2008 16:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
The use of connection pooling greatly complicates any solution.

The application must be coded to facilitate such tracking/debugging

The app. server can populate V$SESSION.CLIENT_INFO and/or CLIENT_IDENTIFIER via DBMS_APPLICATION call.



http://technology.amis.nl/blog/?p=1794

[Updated on: Thu, 18 September 2008 17:10] by Moderator

Report message to a moderator

Re: Find out what query is running for specific session? [message #349300 is a reply to message #349081] Fri, 19 September 2008 14:32 Go to previous message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Thank you.
Previous Topic: SQL query performance issue
Next Topic: How to avoid TableAccessFull
Goto Forum:
  


Current Time: Sat Dec 10 13:00:53 CST 2016

Total time taken to generate the page: 0.04857 seconds