Performance problem when process/users transmitting large amount of data

[ssmith]

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.

[tamilselvan]

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

[ssmith]

The question is, how do we trace the user/process that is causing this data surge?

[manjunathk]

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

[slimdave]

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'?"

[nagarjuna]

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;
/