Performance problem when process/users transmitting large amount of data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Performance problem when process/users transmitting large amount of data

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    Performance problem when process/users transmitting large amount of data

    We get a performance problem on our database when a user/process transmits large amounts of data. It causes all our interfaces to grind to a halt. This user/process is not consistant, and we hit this problem once or twice a day. Does anyone know how I can track this user/process? Thanks.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How does the user transmit data?

    Is he using SQLLDR to load data or using dblink to insert rows from db to another db ?

    Tamil

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    The question is, how do we trace the user/process that is causing this data surge?

  4. #4
    Join Date
    Oct 2000
    Posts
    211
    Hi,
    What is your requirement? If you want to trace a single session(obviously you should not have any problems in identifying the problem user), you can do so setting the 10046 event with level 8(just the wait events). You can alternately generate the statspack report for that user alone.
    So again, what is your requirement?

    Thanks
    Manjunath

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by ssmith
    The question is, how do we trace the user/process that is causing this data surge?

    The answer is, "what is a data surge, and what do you mean by 'transmit'?"
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by ssmith
    The question is, how do we trace the user/process that is causing this data surge?
    Run the following at the time of the heavy transaction to find out the query, sid, oracle username, os username etc..

    set serveroutput on
    declare
    x number;
    begin
    for x in
    ( select username||'('||a.sid||','||a.serial#||
    ') ospid = ' || a.process ||
    ' program = ' || a.program||' machine ='|| a.machine username,
    to_char(a.LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    a.sql_address, a.LAST_CALL_ET, a.sql_hash_value, b.event,b.p1,b.p2, b.p3
    from v$session a, v$session_wait b
    where status = 'ACTIVE'
    and rawtohex(sql_address) <> '00'
    and username is not null and a.sid=b.sid 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 || ' ' || x.sql_hash_value);
    dbms_output.put_line(substr( y.sql_text, 1, 250 ) );
    dbms_output.put_line( x.event || ' ' || x.p1 || ' ' || x.p2 || ' ' || x.p3 );

    end if;
    end loop;
    end loop;
    end;
    /
    -nagarjuna

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