How to find out SQL run by a specific user
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to find out SQL run by a specific user

  1. #1
    Join Date
    Jan 2003
    Posts
    21

    How to find out SQL run by a specific user

    Hello All,

    We are running 8.1.7.4 db in MTS mode on Windows NT 4.

    Sometimes CPU utilization goes to 100% for 3 - 4 minutes and
    we find out the session utilizing max CPU by following query:

    SELECT SUBSTR (sn.NAME, 1, 30) parameter,
    ss.username || '(' || se.SID || ',' || ss.SERIAL# || ')' user_process,
    ss.OSUSER, ss.LAST_CALL_ET, se.VALUE, ss.MACHINE
    FROM v$session ss, v$sesstat se, v$statname sn
    WHERE se.statistic# = sn.statistic#
    AND sn.NAME LIKE '%CPU used by this session%'
    AND se.SID = ss.SID
    ORDER BY sn.NAME, se.VALUE DESC

    Now my manager has asked me to find out the queries run by
    users utilizing max CPU so that we can tune those queries.

    I have tried looking in v$sqlarea by joining ADDRESS colum with
    both SQL_ADDRESS and PREV_SQL_ADDR columns of v$session but it
    doesn't always give the results.

    I would like to know is some other sure shot way of getting the
    SQL. Any help in this regard will be highly appreciated.

    Best Regards,

    Kashif

  2. #2
    Join Date
    Jan 2003
    Location
    Delhi
    Posts
    63
    Top 10 cpu consuming session
    select a.sid, a.logon_time "log On ", spid, status, substr(a.program,1,15) prog, a.terminal,osuser, value/60/100 value
    from v$session a, v$process b, v$sesstat c
    where c.statistic#=12 and c.sid=a.sid
    and a.paddr = b.addr
    and trunc(value/60/100)>0
    and rownum<10
    order by value desc;

  3. #3
    Join Date
    Jan 2003
    Posts
    21
    Hello Mdash,

    Thanx for your reply.

    As i have stated in my earlier post, we are able to
    find out the top CPU consuming sessions. Now we are
    looking to find out the SQL run by these sessions.

    We would appreciate any help in this regard.

    Thank You,

    Kashif

  4. #4
    Join Date
    Jan 2003
    Location
    Delhi
    Posts
    63
    I think u can use statspack with snapshot level=6 .
    It will give you top sqls & also execution plan on the database.

  5. #5
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Since you've narrowed it down to the rogue session, try this script from Tom's site called showsql...

    Code:
    column status format a10
    set feedback off
    set serveroutput on
    
    select username, sid, serial#, process, status
    from v$session
    where username is not null
    /
    
    column username format a20
    column sql_text format a55 word_wrapped
    
    set serveroutput on size 1000000
    declare
        x number;
    begin
        for x in
        ( 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$session
           where status = 'ACTIVE'
             and rawtohex(sql_address) <> '00'
             and username is not null order by last_call_et )
        loop
            for y in ( select max(decode(piece,0,sql_text,null)) ||
                              max(decode(piece,1,sql_text,null)) ||
                              max(decode(piece,2,sql_text,null)) ||
                              max(decode(piece,3,sql_text,null))
                                   sql_text
                         from v$sqltext_with_newlines
                        where address = x.sql_address
                          and piece < 4)
            loop
                if ( y.sql_text not like '%listener.get_cmd%' and
                     y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
                then
                    dbms_output.put_line( '--------------------' );
                    dbms_output.put_line( x.username );
                    dbms_output.put_line( x.logon_time || ' ' ||
                                          x.current_time||
                                          ' last et = ' ||
                                          x.LAST_CALL_ET);
                    dbms_output.put_line(
                              substr( y.sql_text, 1, 250 ) );
                end if;
            end loop;
        end loop;
    end;
    /
    
    column username format a15 word_wrapped
    column module format a15 word_wrapped
    column action format a15 word_wrapped
    column client_info format a30 word_wrapped
    
    select username||'('||sid||','||serial#||')' username,
           module,
           action,
           client_info
    from v$session
    where module||action||client_info is not null;
    for more discussion on this check this out.

    Cheers...

    Tarry
    Last edited by Tarry; 09-16-2003 at 04:04 PM.
    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