不好意思,记错了,是bulk binds.所用时间为普通INSERT的3/32 请看: How Do Bulk Binds Improve Performance? The assigning of values to PL/SQL variables in SQL statements is called binding. The binding of an entire collection at once is called bulk binding. Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth. For example, the following DELETE statement is sent to the SQL engine just once, with an entire nested table: DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN ... FORALL i IN depts.FIRST..depts.LAST DELETE FROM emp WHERE deptno = depts(i); END; In the example below, 5000 part numbers and names are loaded into index-by tables. Then, all table elements are inserted into a database table twice. First, they are inserted using a FOR loop, which completes in 32 seconds. Then, they are bulk-inserted using a FORALL statement, which completes in only 3 seconds. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15)); Table created. SQL> GET test.sql 1 DECLARE 2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER; 3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; 4 pnums NumTab; 5 pnames NameTab; 6 t1 NUMBER(5); 7 t2 NUMBER(5); 8 t3 NUMBER(5); 9 PROCEDURE get_time (t OUT NUMBER) IS 10 BEGIN SELECT TO_CHAR(SYSDATE,’SSSSS’) INTO t FROM dual; END; 11 BEGIN 12 FOR j IN 1..5000 LOOP -- load index-by tables 13 pnums(j) := j; 14 pnames(j) := ’Part No. ’ || TO_CHAR(j); 15 END LOOP; 16 get_time(t1); 17 FOR i IN 1..5000 LOOP -- use FOR loop 18 INSERT INTO parts VALUES (pnums(i), pnames(i)); 19 END LOOP; 20 get_time(t2); 21 FORALL i IN 1..5000 -- use FORALL statement 22 INSERT INTO parts VALUES (pnums(i), pnames(i)); 23 get_time(t3); 24 DBMS_OUTPUT.PUT_LINE(’Execution Time (secs)’); 25 DBMS_OUTPUT.PUT_LINE(’---------------------’); 26 DBMS_OUTPUT.PUT_LINE(’FOR loop: ’ || TO_CHAR(t2 - t1)); 27 DBMS_OUTPUT.PUT_LINE(’FORALL: ’ || TO_CHAR(t3 - t2)); 28* END; SQL> / Execution Time (secs) --------------------- FOR loop: 32 FORALL: 3 PL/SQL procedure successfully completed. To bulk-bind input collections, you use the FORALL statement. To bulk-bind output collections, you use the BULK COLLECT clause.
是不是可以考虑前面先别在内存中存放那么多记录呢?我现在也要处理几千万的数据,可以考虑分表啊,我的按key并行取出记录,处理后成批往表里写。to KingSunSha(弱水三千):index disabled 是什么意思? drop 还是 alter index disable?后者似乎有限制。
我觉得首先把INDEX删掉,然后重建,速度会快许多的
再来: improving PL/SQL performance Using the NOCOPY Compiler Hint Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT parameter. When you call the subprogram, the IN parameter is passed by reference. That is, a pointer to the IN actual parameter is passed to the corresponding formal parameter. So, both parameters reference the same memory location, which holds the value of the actual parameter. By default, the OUT and IN OUT parameters are passed by value. That is, the value of the IN OUT actual parameter is copied into the corresponding formal parameter. Then, if the subprogram exits normally, the values assigned to the OUT and IN OUT formal parameters are copied into the corresponding actual parameters. When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference. In the following example, you ask the compiler to pass IN OUT parameter my_ staff by reference instead of by value: DECLARE TYPE Staff IS VARRAY(200) OF Employee; PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ... Remember, NOCOPY is a hint, not a directive. So, the compiler might pass my_staff by value despite your request. Usually, however, NOCOPY succeeds. So, it can benefit any PL/SQL application that passes around large data structures. In the example below, 25000 records are loaded into a local nested table, which is passed to two local procedures that do nothing but execute NULL statements. However, a call to one procedure takes 21 seconds because of all the copying. With NOCOPY, a call to the other procedure takes much less than 1 second. SQL> SET SERVEROUTPUT ON SQL> GET test.sql 1 DECLARE 2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE; 3 emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize 4 t1 NUMBER(5); 5 t2 NUMBER(5); 6 t3 NUMBER(5); 7 PROCEDURE get_time (t OUT NUMBER) IS 8 BEGIN SELECT TO_CHAR(SYSDATE,’SSSSS’) INTO t FROM dual; END; 9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS 10 BEGIN NULL; END; Using the NOCOPY Compiler Hint 11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS 12 BEGIN NULL; END; 13 BEGIN 14 SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788; 15 emp_tab.EXTEND(24999, 1); -- copy element 1 into 2..25000 16 get_time(t1); 17 do_nothing1(emp_tab); -- pass IN OUT parameter 18 get_time(t2); 19 do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter 20 get_time(t3); 21 DBMS_OUTPUT.PUT_LINE(’Call Duration (secs)’); 22 DBMS_OUTPUT.PUT_LINE(’--------------------’); 23 DBMS_OUTPUT.PUT_LINE(’Just IN OUT: ’ || TO_CHAR(t2 - t1)); 24 DBMS_OUTPUT.PUT_LINE(’With NOCOPY: ’ || TO_CHAR(t3 - t2)); 25* END; SQL> / Call Duration (secs) -------------------- Just IN OUT: 21 With NOCOPY: 0
Waiting......
目前的运行状态是用SQLLDR倒入960万行纪录用4分钟(INDEX预先DISABLE掉),然后REBUILD INDEX用15-20分钟.服务器配置: IBM RS6000 H80, 18个CPU, 32GB RAM, 655GB HDD
你说先把index disable 掉,怎么才能把它disable掉呢???
请看:
How Do Bulk Binds Improve Performance?
The assigning of values to PL/SQL variables in SQL statements is called binding.
The binding of an entire collection at once is called bulk binding. Bulk binds improve
performance by minimizing the number of context switches between the PL/SQL
and SQL engines. With bulk binds, entire collections, not just individual elements,
are passed back and forth. For example, the following DELETE statement is sent to
the SQL engine just once, with an entire nested table:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
...
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
END;
In the example below, 5000 part numbers and names are loaded into index-by
tables. Then, all table elements are inserted into a database table twice. First, they
are inserted using a FOR loop, which completes in 32 seconds. Then, they are
bulk-inserted using a FORALL statement, which completes in only 3 seconds.
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
Table created.
SQL> GET test.sql
1 DECLARE
2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
4 pnums NumTab;
5 pnames NameTab;
6 t1 NUMBER(5);
7 t2 NUMBER(5);
8 t3 NUMBER(5);
9 PROCEDURE get_time (t OUT NUMBER) IS
10 BEGIN SELECT TO_CHAR(SYSDATE,’SSSSS’) INTO t FROM dual; END;
11 BEGIN
12 FOR j IN 1..5000 LOOP -- load index-by tables
13 pnums(j) := j;
14 pnames(j) := ’Part No. ’ || TO_CHAR(j);
15 END LOOP;
16 get_time(t1);
17 FOR i IN 1..5000 LOOP -- use FOR loop
18 INSERT INTO parts VALUES (pnums(i), pnames(i));
19 END LOOP;
20 get_time(t2);
21 FORALL i IN 1..5000 -- use FORALL statement
22 INSERT INTO parts VALUES (pnums(i), pnames(i));
23 get_time(t3);
24 DBMS_OUTPUT.PUT_LINE(’Execution Time (secs)’);
25 DBMS_OUTPUT.PUT_LINE(’---------------------’);
26 DBMS_OUTPUT.PUT_LINE(’FOR loop: ’ || TO_CHAR(t2 - t1));
27 DBMS_OUTPUT.PUT_LINE(’FORALL: ’ || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 32
FORALL: 3
PL/SQL procedure successfully completed.
To bulk-bind input collections, you use the FORALL statement. To bulk-bind output
collections, you use the BULK COLLECT clause.
drop 还是 alter index disable?后者似乎有限制。
improving PL/SQL performance
Using the NOCOPY Compiler Hint
Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT
parameter. When you call the subprogram, the IN parameter is passed by reference.
That is, a pointer to the IN actual parameter is passed to the corresponding formal
parameter. So, both parameters reference the same memory location, which holds
the value of the actual parameter.
By default, the OUT and IN OUT parameters are passed by value. That is, the value
of the IN OUT actual parameter is copied into the corresponding formal parameter.
Then, if the subprogram exits normally, the values assigned to the OUT and IN OUT
formal parameters are copied into the corresponding actual parameters.
When the parameters hold large data structures such as collections, records, and
instances of object types, all this copying slows down execution and uses up
memory. To prevent that, you can specify the NOCOPY hint, which allows the
PL/SQL compiler to pass OUT and IN OUT parameters by reference.
In the following example, you ask the compiler to pass IN OUT parameter my_
staff by reference instead of by value:
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...
Remember, NOCOPY is a hint, not a directive. So, the compiler might pass my_staff
by value despite your request. Usually, however, NOCOPY succeeds. So, it can
benefit any PL/SQL application that passes around large data structures.
In the example below, 25000 records are loaded into a local nested table, which is
passed to two local procedures that do nothing but execute NULL statements.
However, a call to one procedure takes 21 seconds because of all the copying. With
NOCOPY, a call to the other procedure takes much less than 1 second.
SQL> SET SERVEROUTPUT ON
SQL> GET test.sql
1 DECLARE
2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;
3 emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
4 t1 NUMBER(5);
5 t2 NUMBER(5);
6 t3 NUMBER(5);
7 PROCEDURE get_time (t OUT NUMBER) IS
8 BEGIN SELECT TO_CHAR(SYSDATE,’SSSSS’) INTO t FROM dual; END;
9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
10 BEGIN NULL; END;
Using the NOCOPY Compiler Hint
11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
12 BEGIN NULL; END;
13 BEGIN
14 SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788;
15 emp_tab.EXTEND(24999, 1); -- copy element 1 into 2..25000
16 get_time(t1);
17 do_nothing1(emp_tab); -- pass IN OUT parameter
18 get_time(t2);
19 do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
20 get_time(t3);
21 DBMS_OUTPUT.PUT_LINE(’Call Duration (secs)’);
22 DBMS_OUTPUT.PUT_LINE(’--------------------’);
23 DBMS_OUTPUT.PUT_LINE(’Just IN OUT: ’ || TO_CHAR(t2 - t1));
24 DBMS_OUTPUT.PUT_LINE(’With NOCOPY: ’ || TO_CHAR(t3 - t2));
25* END;
SQL> /
Call Duration (secs)
--------------------
Just IN OUT: 21
With NOCOPY: 0
谢谢大家!
给分的。