Hi,

We have set of Mainframe programs which connects to Oracle database and queries set of tables. Our application and DB servers are in 2 different locations (which we cannot change at the moment), which i believe is causing considerable delays when application executes query(select/update/delete/Insert) in a loop. We have verified the queries in Oracle and it seems pretty good (Basic select by querying primary key column)

We have used ANSI Dynamic approach for bulk processing Insert/Update/Delete and we see significant improvement in performance. Also, we are using Prefetch option while selecting bulk data, which is also helping us to improve performance.

Do we have a similar method using which can improve performance of SELECT queries in loop.? Or Could you please suggest what is the best way to improve the performance.

Note : Currently querying 17K records (Looping through 17K primary key values), takes around 20mts to complete. We have verified queries from Oracle end they run within few milli seconds in Oracle.

oracle version : 11gR2

Please let me know if you need more information

Thanks for your help

select x from t where id = 1;
select y from t1 where pk = 10;
select z from t2 where col = 99;
...
...

procedure P(p_id int, p_pk int, p_col int, 
            out_id date, out_y int, out_z varchar2) is
begin
select x into out_x from t where id = p_id;
select y into out_y from t1 where pk = p_pk;
select z into out_z from t2 where col = p_col;
...
...
end;