Re: runaway query

This query will show you who's running what...

select
 username,
 sid,
 serial#,
 status,
 decode(sql_text, NULL, 'NON-SQL (DDL, GRANT, etc.)', sql_text) sql_text from
 v$sql a,
 v$session s
where
 s.sql_address = a.address(+)
 and username is not null;

Also, you don't have to guess as to what Oracle session goes with a particular unix Oracle process. This query will tell you exactly which one it is.

select p.spid UNIXpid, s.sid, s.serial#, s.username, q.sql_text from

  v$process p,
  v$session s,
  v$sql q

  p.spid = <<unix pid>> and
  p.addr = s.paddr and
  q.address = s.sql_address and
  s.username is not null

<hegyvari_at_ardents.hu> wrote in message
news:MPG.1552085a526c4bc9989680_at_192.168.0.1...
> Greetings,
>
> I have a 8.0.5 server on Linux RedHat 6.1 and it seems as if a query
> would run away some time and slow down everyone else. It is not something
> that happens often. When I check top, I see an Oracle process running for
> a long time (13 minutes, f.e.). Then I start Instance manager, make a
> guess about which session corresponds to the long running process, kill
> it, after that the process goes away from top and everything goes on as
> usual.
>
> Questions:
> Anyone experiencing the similar thing in the same environment?
> How can I find out what SQL commands are running at a given time? I would
> be glad if I could see the actual SQL causing the grief.
>
> Best Regards,
>
> Hegyvari Krisztian
Received on Thu Apr 26 2001 - 09:15:14 CDT