whos running what

I want to know who is slowing the database.
Someone is running the query from hell.
I know wich query, but i dont know who is running it.
Could someone give me a query to find top bad sql statement and to fing who is running it?

I have statspack and some query i use to find bad sql statement.

select a.disk_reads,a.buffer_gets, B.SQL_TEXT
from v$sqlarea a,v$sqltext B
where A.disk_reads > 10000
AND A.ADDRESS = B.ADDRESS
order by A.disk_reads desc, b.piece;

This give me top sql statement to tune. But i dont know who.

I want to tell that user to stop, cuse hes slowing the entire database.

Check the V$SESSION_LONGOPS.

Sam

Join V$SQLTEXT to V$SESSION (V$SQLTEXT.ADRESS = v$SESSION.SQL_ADDRESS) to see which session is running which SQL statement.

This showsql from Tom which I ran while running 2 sessions gives me the 'Shoot to kill' liscense...

Code:

---


C:\WINNT\system32>sqlplus

SQL*Plus: Release 9.2.0.3.0 - Production on Tue Sep 30 21:29:27 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: appdev@nick817
Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

[email protected]L> column status format a10
[email protected]L> set feedback off
[email protected]L> set serveroutput on
[email protected]L>
[email protected]L> select username, sid, serial#, process, status
  2  from v$session
  3  where username is not null
  4  /

USERNAME                              SID    SERIAL# PROCESS  STATUS
------------------------------ ---------- ---------- --------- ----------
SCOTT                                  8        31 3548:1896 INACTIVE
APPDEV                                  9        23 964:1776  ACTIVE
[email protected]L>
[email protected]L> column username format a20
[email protected]L> column sql_text format a55 word_wrapped
[email protected]L>
[email protected]L> set serveroutput on size 1000000
[email protected]L> declare
  2      x number;
  3  begin
  4      for x in
  5      ( select username||'('||sid||','||serial#||
  6                  ') ospid = ' ||  process ||
  7                  ' program = ' || program username,
  8              to_char(LOGON_TIME,' Day HH24:MI') logon_time,
  9              to_char(sysdate,' Day HH24:MI') current_time,
 10              sql_address, LAST_CALL_ET
 11          from v$session
 12        where status = 'ACTIVE'
 13          and rawtohex(sql_address) <> '00'
 14          and username is not null order by last_call_et )
 15      loop
 16          for y in ( select max(decode(piece,0,sql_text,null)) ||
 17                            max(decode(piece,1,sql_text,null)) ||
 18                            max(decode(piece,2,sql_text,null)) ||
 19                            max(decode(piece,3,sql_text,null))
 20                                sql_text
 21                      from v$sqltext_with_newlines
 22                      where address = x.sql_address
 23                        and piece < 4)
 24          loop
 25              if ( y.sql_text not like '%listener.get_cmd%' and
 26                  y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
 27              then
 28                  dbms_output.put_line( '--------------------' );
 29                  dbms_output.put_line( x.username );
 30                  dbms_output.put_line( x.logon_time || ' ' ||
 31                                        x.current_time||
 32                                        ' last et = ' ||
 33                                        x.LAST_CALL_ET);
 34                  dbms_output.put_line(
 35                            substr( y.sql_text, 1, 250 ) );
 36              end if;
 37          end loop;
 38      end loop;
 39  end;
 40  /
--------------------
APPDEV(9,23) ospid = 964:1776 program = sqlplus.exe
Tuesday  21:29  Tuesday  21:29 last et = 0
SELECT USERNAME || '('  || SID  || ','  || SERIAL#  || ') ospid = '  || PROCESS  || ' program = '
|| PROGRAM  USERNAME,TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME,TO_CHAR(SYSDATE,' Day HH24:MI')
CURRENT_TIME,SQL_ADDRESS,LAST_CALL_ET  FROM V$SESSI
[email protected]L>
[email protected]L>
[email protected]L>
[email protected]L> /
--------------------
APPDEV(9,23) ospid = 964:1776 program = sqlplus.exe
Tuesday  21:29  Tuesday  21:30 last et = 0
SELECT USERNAME || '('  || SID  || ','  || SERIAL#  || ') ospid = '  || PROCESS  || ' program = '
|| PROGRAM  USERNAME,TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME,TO_CHAR(SYSDATE,' Day HH24:MI')
CURRENT_TIME,SQL_ADDRESS,LAST_CALL_ET  FROM V$SESSI
[email protected]L>
[email protected]L>
[email protected]L> select username||'('||sid||','||serial#||')' username,
  2        module,
  3        action,
  4        client_info
  5  from v$session
  6  where module||action||client_info is not null;

USERNAME            MODULE
-------------------- ------------------------------------------------
ACTION                          CLIENT_INFO
-------------------------------- ----------------------------------------------------------------
SCOTT(8,31)          SQL*Plus


APPDEV(9,23)        SQL*Plus

---