SQL*Plus copy命令处理大批量数据复制(三)
rraysize与copycommit
scott@SYBO2SZ> set arraysize 2000
scott@SYBO2SZ> set copycommit 5000
--再次调用脚本
scott@SYBO2SZ> @/users/robin/dba_scripts/custom/temp/cp_cmd.sql
PL/SQL procedure successfully completed.
Array fetch/bind size is 2000. (arraysize is 2000)
Will commit after every 5000 array binds. (copycommit is 5000)
Maximum long size is 5000. (long is 5000)
Table CP_BIG_TB created.
1000000 rows selected from scott@sybo2sz.
1000000 rows inserted into CP_BIG_TB.
1000000 rows committed into CP_BIG_TB at scott@sybo2sz.
PL/SQL procedure successfully completed.
The elapsed time is 24.65 seconds.
The undo size is 0
The redo size is 0
PL/SQL procedure successfully completed.
--从上面的结果可知，后者耗用的时间明显低于前者，节约了近一半的时间
7、测试用到的脚本
[sql]
robin@SZDB:~/dba_scripts/custom/temp> more cp_cmd.sql
SET SERVEROUTPUT ON;
VARIABLE start_time NUMBER;
VARIABLE end_time NUMBER;
VARIABLE v_s_undo NUMBER;
VARIABLE v_s_redo NUMBER;
VARIABLE v_e_undo NUMBER;
VARIABLE v_e_redo NUMBER;
VARIABLE v_diff_dt NUMBER;
VARIABLE v_diff_undo NUMBER;
VARIABLE v_diff_redo NUMBER;
--Author : Robinson
BEGIN
SELECT DBMS_UTILITY.get_time INTO :start_time FROM DUAL;
SELECT b.VALUE
INTO :v_s_undo
FROM v$statname a, v$mystat b
WHERE a.statisti
c# = b.statistic# AND LOWER (a.name) = 'undo change vector size';
SELECT b.VALUE
INTO :v_s_redo
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'redo size';
END;
/
COPY from scott/tiger@sybo2sz -
to scott/tiger@sybo2sz -
create cp_big_tb -
using -
select * from big_table;
BEGIN
SELECT DBMS_UTILITY.get_time INTO :end_time FROM DUAL;
SELECT b.VALUE
INTO :v_e_undo
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'undo change vector size';
SELECT b.VALUE
INTO :v_e_redo
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'redo size';
END;
/
BEGIN
:v_diff_dt := round((:end_time - :start_time)/100,2);
:v_diff_undo := :v_e_undo - :v_s_undo;
:v_diff_redo := :v_e_redo - :v_s_redo;
DBMS_OUTPUT.put_line ('The elapsed time is ' || TO_CHAR (:v_diff_dt)||' seconds.');
DBMS_OUTPUT.put_line ('The undo size is ' || TO_CHAR (:v_diff_undo));
DBMS_OUTPUT.put_line ('The redo size is ' || TO_CHAR (:v_diff_redo));
END;
/