Performance Tuning on SQL's Over DB link
Hi ,
I have observe the following sql statement behave differently in the given senarios;
The following sql statement was fired in two differnt sessions;
sql statement (A) :-
select dt.*,lm.*,(fn_get_details(lm.col1)*nvl(lm.col3,0))) as amount
from tab1@dblink1 lm,tab2@dblink dt
where lm.col1 <> 'C' and
lm.col2 = 'A' and
lm.col3 = dt.col1
session 1. issued the statement "set transaction read only"; and then execute the sql statement(A).
session 2. just execute the sql statement(A)
results :
session 1. return the results in quick time.
session 2. executing the statement for long time and not retunning a single record.
please let me know how to add the "set transaction read only" for all sessions for a perticular db user. I have already tryied database trigger and it doen't work;
see trigger details below;
CREATE OR REPLACE TRIGGER SYSTEM.test1
AFTER LOGON
ON DATABASE
BEGIN
IF (USER = 'RPT_USER') THEN
SET TRANSACTION READ ONLY;
END IF;
END test1;
Thanks
KDev