Question: I have strange question for which i did not found the solution. I have the below query which takesmore than 5 minutes to execute. The query performance is very slow even i have few records which will get inserted. If i remove the insert statement and runs only select query statement then the query execute within 2 to 5 seconds. What is the problem behind this ? How can i improve insert query performance in this case ?

```
INSERT INTO TEMP_SERVICE_OPTION_EXTRACTION(ID,ICC,ASSIGNED_ANUMBER_ID,SERVICE_ID,PRODUCT_OPTION_ID,STATUS_ID,END_DATE,ORDER_NUMBER)
Select DISTINCT(SO.ID),SIM.ICC,SIM.ASSIGNED_ANUMBER_ID,SO.SERVICE_ID,SO.PRODUCT_OPTION_ID,SO.STATUS_ID,SO.END_DATE,DCR.SHOP_ORDER_NUMBER FROM
SIMCARD@FONIC_RETAIL SIM 
    JOIN SERVICE_OPTION@FONIC_RETAIL SO ON SO.SERVICE_ID=SIM.ASSIGNED_TO_SERVICE_ID
JOIN DANGAARD_CONFIRM_RECORD@FONIC_RETAIL DCR ON DCR.ICC = SIM.ICC
 where SO.STATUS_ID IN (20,40)
and SO.ID < to_char(SYSDATE - numtodsinterval (  1,'MINUTE' ), 'YYYYMMDDHH24MISS')||'0000'
 and SO.ID > to_char(SYSDATE - numtodsinterval ( 1, 'HOUR' ), 'YYYYMMDDHH24MISS')||'0000'

```

Answer: 
use anonymous PL/SQL block with a for loop in such case (only 1 row will be inserted in each step):

```
begin
for r in (
  Select DISTINCT (SO.ID),SIM.ICC,SIM.ASSIGNED_ANUMBER_ID,SO.SERVICE_ID,SO.PRODUCT_OPTION_ID,SO.STATUS_ID,SO.END_DATE,DCR.SHOP_ORDER_NUMBER
  FROM SIMCARD@FONIC_RETAIL SIM 
  JOIN SERVICE_OPTION@FONIC_RETAIL SO ON SO.SERVICE_ID=SIM.ASSIGNED_TO_SERVICE_ID
  JOIN DANGAARD_CONFIRM_RECORD@FONIC_RETAIL DCR ON DCR.ICC = SIM.ICC
  where SO.STATUS_ID IN (20,40)
    and SO.ID < to_char(SYSDATE - numtodsinterval (  1,'MINUTE' ), 'YYYYMMDDHH24MISS')||'0000'
    and SO.ID > to_char(SYSDATE - numtodsinterval ( 1, 'HOUR' ), 'YYYYMMDDHH24MISS')||'0000'
) loop
  INSERT INTO TEMP_SERVICE_OPTION_EXTRACTION(ID,ICC,ASSIGNED_ANUMBER_ID,SERVICE_ID,PRODUCT_OPTION_ID,STATUS_ID,END_DATE,ORDER_NUMBER)
  VALUES (r.ID,r.ICC,r.ASSIGNED_ANUMBER_ID,r.SERVICE_ID,r.PRODUCT_OPTION_ID,r.STATUS_ID,r.END_DATE,r.SHOP_ORDER_NUMBER);
  COMMIT;
end loop;
end;

```

When this does not help, you have to `ALTER TABLE TEMP_SERVICE_OPTION_EXTRACTION SHRINK SPACE` (as suggested in [Slow query execution in an empty table. (after deleting a large amount of inserts)](https://stackoverflow.com/questions/4408212/slow-query-execution-in-an-empty-table-after-deleting-a-large-amount-of-insert)).
Since you're using DB Links for all tables, you have to check HOW the join is implemented (EXEC PLAN). It might be joined either on "your" side or on "remote" side. Try to create a view on remote side with these joins. If joined on your side Oracle has to fetch "all" the rows from all the tables, put them into TEMP and then performs the query.

PS: Oracle docs also explains under with conditions, distributed query might use indexes on remote side.

Use Google and search for "db link" and "distributed query performance"