Home » RDBMS Server » Performance Tuning » Getting queries
Getting queries [message #184825] Fri, 28 July 2006 04:04 Go to next message
Messages: 2
Registered: July 2006
Location: Wales UK
Junior Member

I have a problem with a 3rd party application. Every now and then it freezes the whole company operation. If I access the database directly the response is instantanious. I am able to run large queries without any problems. It has been suggested that perhaps the 3rd party application has a un-tuned query which is locking the application. Is there any way of getting all the queries currently running on the database along with the running times.
Re: Getting queries [message #184836 is a reply to message #184825] Fri, 28 July 2006 04:43 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
'Freezes the whole company operation'
'If I access the database directly the response is instantanious'

mean that what ever is frozen isn't in the database.
This being Oracle, (and not ome other databases that shall remain nameless), Writes do not block Reads.

I would suspect network congestion to be honest.

Still, here's a query that will do what you want.
Sessions curently doing something are marked 'ACTIVE', and the last_call_et column is effectively the time since the status last changed (i for Active sessions it's how long they've been doing something, and for inactive ones it's how long they've not been doing anything)

select se.sid,se.username,se.last_call_et,se.status,sq.sql_text
from   v$session se
      ,v$sqlarea sq
where  se.sql_hash_value = sq.hash_value;
Previous Topic: Help need .. Urgent
Next Topic: DBA_FREE_SPACE view in 10g taking more time
Goto Forum:

Current Time: Thu Aug 17 03:06:36 CDT 2017

Total time taken to generate the page: 0.29377 seconds