Hi,
I am not suppose to post my query here because of restriction,but i can tell you the scenareo,

Procedure p1 is
local variables;
begin
--I have loops to wait until the remote is getting or loading data from another remote.
--it sleeps for 1hour, if the remote is loaded data from another remote then I will load --data from this remote,

--here my insert to load from remote DB
INSERT /*+ APPEND */ into mytablename
(
columnnames
:
:
) --Totally 125 columns
SELECT
columnnames
.
:

from
remotetablename@dblinkname,
:
;
--totally 22 table from remoteDB
Where
Conditions
n-1 join conditions(n is number of tables)
includes some other conditions also
union all
SELECT
columnnames
.
:

from
remotetablename@dblinkname,
:
;
--totally 22 table from remoteDB
Where
Conditions
n-1 join conditions(n is number of tables)
includes some other conditions also

union all

SELECT
columnnames
.
:

from
remotetablename@dblinkname,
:
;
--totally 22 table from remoteDB
Where
Conditions
n-1 join conditions(n is number of tables)
includes some other conditions also



end p1;



--- This insert alone is taking 145 seconds.
the same insert is executed within the remote DB itself is taking 35seconds and the select alone is taking 1 second.

When the insert is executed from another DB(my databse) its taking 145seconds to load the data.


Hrishy,
I didn't execute the commands to trace the session from sqlplus,because I dont have
permission to do that, even I dont have access to execute my procedure. somebody else will execute it .I can see the output.

first I want to know is the time taken is reasonable or not?

Thanks
mallma