DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: whos running what

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Check the V$SESSION_LONGOPS.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Join V$SQLTEXT to V$SESSION (V$SQLTEXT.ADRESS = v$SESSION.SQL_ADDRESS) to see which session is running which SQL statement.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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
    
    appdev@NICK817.TARRY.LOCAL> column status format a10
    appdev@NICK817.TARRY.LOCAL> set feedback off
    appdev@NICK817.TARRY.LOCAL> set serveroutput on
    appdev@NICK817.TARRY.LOCAL>
    appdev@NICK817.TARRY.LOCAL> 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
    appdev@NICK817.TARRY.LOCAL>
    appdev@NICK817.TARRY.LOCAL> column username format a20
    appdev@NICK817.TARRY.LOCAL> column sql_text format a55 word_wrapped
    appdev@NICK817.TARRY.LOCAL>
    appdev@NICK817.TARRY.LOCAL> set serveroutput on size 1000000
    appdev@NICK817.TARRY.LOCAL> 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
    appdev@NICK817.TARRY.LOCAL>
    appdev@NICK817.TARRY.LOCAL>
    appdev@NICK817.TARRY.LOCAL>
    appdev@NICK817.TARRY.LOCAL> /
    --------------------
    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
    appdev@NICK817.TARRY.LOCAL>
    appdev@NICK817.TARRY.LOCAL>
    appdev@NICK817.TARRY.LOCAL> 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
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width