orcel向mysql推送数据_数据通过数据库链接从Oracle推送到Microsoft SQL Server

我们使用Oracle hetrogeneous服务创建了从Oracle数据库到MS sql server数据库的数据库链接 .

Oracle DB中有一个表A，SQL Server DB中有一个表B.表结构不同 . 我从Oracle dB中选择了一些行，并使用Insert语句通过Database链接推送到mssqlserver DB .

此查询的性能很慢 . 我检查了Oracle日志 . Saying HS代理等待事件意味着等待SQL服务器完成插入和响应 .

能否请您在SQL服务器端建议性能改进方法 . 对于1000个记录，它需要24秒 . 我们的目标是在3-5分钟内推动100000记录 . 我每1000次成功插入一次提交 .

用于插入的查询块粘贴在下面 .

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;

/