tracing session to see how the application was executed in Oracle
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: tracing session to see how the application was executed in Oracle

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    tracing session to see how the application was executed in Oracle

    Hi all,

    I have the application and I want to know how it executed behind the scene if I look into v$SQLAREA I don't know which one belong to that session and if I executed the script below

    SELECT A.SID,A.USERNAME,S.SQL_TEXT
    FROM V$SESSION A,V$SQLTEXT S
    WHERE A.SQL_ADDRESS = S.ADDRESS
    AND A.SQL_HASH_VALUE = S.HASH_VALUE
    ORDER BY A.USERNAME,A.SID,S.PIECE;


    sometime it doesn't capture. does anyone have a better scripts to capture the query that was executed from the application???
    Thanks



    BTW: how can we clear the V$SQLAREA ????

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    could set sql_trace=true in init.ora

    but that could produce u tons of information, not the best method

    try looking in v$sql as well

  3. #3
    Join Date
    May 2002
    Posts
    193
    Do the following ...

    Alter system set timed_statistics=true;
    select sid,serial#,username,machine,terminal from v$session
    set tracing for the particular session with this command:

    Execute sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);

    Go to your UDUMP directory. You should find some trace files here. Run:
    TKPROF trace_filename1, trace_reportname1
    TKPROF trace_filename2, trace_reportname2

    Look at the trace_reportname1, trace_reportname2, .. files for the SQL that the user is running.

    Regards,

    K.Diwakar

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    HI,

    As Davey has already pointed out, this could potentially provide you with tons of information, TKPROF and Tracing is usually used to tune the statement.

    You could do it the quick and dirty way by finding the SID of the user from v$session and query v$sqlarea with ..

    select sql_text from v$sqlarea where parsing_user_id =
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    create or replace trigger sqltrace
    after logon on database
    begin
      if user='XXXXX'
      then 
        execute immediate 'alter session set events ''10046 trace name context forever, level 4''';
      end if;
    end;
    /
    just change XXXX for any user you want to trace
    Last edited by pando; 12-04-2002 at 01:29 PM.

  6. #6
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    set head on
    set pagesize 5000
    set linesize 135
    column pu format a8 heading 'O/S|Login|ID' justify left
    column su format a8 heading 'Oracle|User ID' justify left
    column stat format a8 heading 'Session|Status' justify left
    column ssid format 999999 heading 'Oracle|Session|ID' justify right
    column sser format 999999 heading 'Oracle|Serial|No' justify right
    column spid format 999999 heading 'UNIX|Process|ID' justify right
    column txt format a28 heading 'Current Statment' justify center word
    column logon_time format a15
    column prog format a50 heading 'Program|running|they are' justify left
    select p.username pu,
    s.username su,
    s.status stat,
    s.sid ssid,
    s.serial# sser,
    lpad(p.spid,7) spid,
    to_char(s.logon_time,'DD-MON-RR hh24:mi') logon_time,
    substr(sa.sql_text,1,540) txt ,
    s.machine,
    s.program prog
    from v$process p,
    v$session s,
    v$sqlarea sa
    where p.addr=s.paddr
    and s.username is not null
    and s.sql_address=sa.address(+)
    and s.sql_hash_value=sa.hash_value(+)
    order by 1,2,7
    /
    column pu clear
    column su clear
    column stat clear
    column ssid clear
    column sser clear
    column spid clear
    /
    Giani

  7. #7
    Join Date
    Sep 2002
    Posts
    411
    Pando,

    thanks for the triggers but I have problem when I log in to the database as the user was traced.

    Redo thread mounted by this instance: 1

    Oracle process number: 23

    Windows thread id: 1788, image: ORACLE.EXE


    *** SESSION ID:(25.1839) 2002-12-04 09:52:39.107
    Skipped error 604 during the execution of TEST.SQLTRACE
    *** 2002-12-04 09:52:39.107
    ksedmp: internal or fatal error
    ORA-00604: error occurred at recursive SQL level 1
    ORA-02095: specified initialization parameter cannot be modified
    ORA-06512: at line 4


    it happened on two or three different instance that I have tried, is there anything else I have to run ???

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well you have not used my trigger because I dont change any parameter, also you must grant alter session privilege directly to trigger owner

  9. #9
    Join Date
    Sep 2002
    Posts
    411
    Originally posted by pando
    well you have not used my trigger because I dont change any parameter, also you must grant alter session privilege directly to trigger owner
    what do you mean ??? I thought I only have to change XXXX to the user I want to trace and also I have grant

    grant alter session to TEST

    but it still doesn't work

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well it works for me 100% so I am not sure what you have done

    run alter session set event in a sqlplus session and see if you can do that

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