We have created a database link from Oracle database to MS sql server database using Oracle hetrogeneous services.
There is a table A in Oracle DB and Table B in SQL server DB. Table structures are different. I am selecting some rows from Oracle dB and pushing to mssqlserver DB via Database link using Insert statement.
The performance of this query is slow. I checked the Oracle logs. It is Saying HS agent wait event means waiting for SQL server to finish the insert and response back.
Could you please suggest performance improve methods in SQL server side. For 1000 records push it taking 24 seconds.Our Aim is to push 100000 record in 3-5 minutes. I am doing a commit on every 1000 successful inserts.
The query block used for insert is pasted below.
declare
v_id number:=0;
v_count number:=0;
cursor test_cur is
select Post_txn_balance,
Toll_revenue_type,
Extern_file_id,
Card_issuer_id,
Card_no,
Tx_seq_number,
Recv_extn_loc_id,
Device_no,
Revenue_date,
TO_CHAR(Posted_date,'YYYY-MM-DD')Posted_date,
Purse_ind,
Recv_extn_sp_id,
Lane_tx_type,
Lane_id,
Lane_type,
Full_fare_amount,
to_char(Tx_timestamp,'YYYY-MM-DD') Tx_timestamp
from t_table_a
where rownum<=10000;
test_cur_data test_cur%rowtype;
begin
dbms_output.put_line('Going to insert 10000 records ');
open test_cur;
loop
fetch test_cur into test_cur_data;
exit when test_cur%notfound;
v_id:=v_id+1;
v_count:=v_count+1;
INSERT INTO "t_table_b"@sqldb1(
"after_card_bal",
"app_sector",
"batch_number",
"card_issuer_id",
"card_number",
"card_status",
"card_txn_number",
"data_push_source",
"debit_credit_indicator",
"entry_loc_id",
"entry_loc_name",
"exit_loc_id",
"exit_loc_name",
"expiry_date",
"id_number",
"id_type",
"mfg_number",
"operational_date",
"posted_date",
"print_sp",
"lms_process_date",
"process_status",
"purse_flag",
"sp_id",
"t_type",
"terminal_id",
"terminal_type",
"txn_amount",
"txn_date_time",
"txn_source",
"txn_unique_id",
"ur_flag",
"vector_acct_number"
)
values
(
test_cur_data.Post_txn_balance,
test_cur_data.Toll_revenue_type,
test_cur_data.Extern_file_id,
test_cur_data.Card_issuer_id,
test_cur_data.Card_no,
'ACTIVE',
test_cur_data.Tx_seq_number,
'C',
'D',
'503',
'MALYASY',
test_cur_data.Recv_extn_loc_id,
'MALYASY',
'01-mar-2016',
'ABCD123',
'DFED12',
test_cur_data.Device_no,
test_cur_data.Revenue_date,
test_cur_data.Posted_date,
'-04',
'01-mar-2016',
'P',
test_cur_data.Purse_ind,
test_cur_data.Recv_extn_sp_id,
test_cur_data.Lane_tx_type,
test_cur_data.Lane_id,
test_cur_data.Lane_type,
test_cur_data.Full_fare_amount,
test_cur_data.Tx_timestamp,
'T',
v_id,
'P',
'123456'
);
IF v_count=1000 then
commit;
v_count:=0;
end if;
end loop;
commit;
v_id:=0;
v_count:=0;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while pushing data'||sqlerrm);
ROLLBACK;
end;
/
