Oracle line-by-row submission, batch submission and limit speed-raising method ____oracle

Source: Internet
Author: User
Tags commit
In an Oracle database, the more frequent the submission is, the better. On the contrary, bulk submissions can get better performance. This article gives you a simple demonstration of the performance difference between submitting a row to a bulk commit in an Oracle database. Finally, a method that can greatly change the performance is given.

1. Create a table t_ref and initialize 880,992 data for later experiments.
Sec@ora10g> CREATE TABLE T_ref as SELECT * from All_objects;

sec@ora10g> INSERT INTO T_ref select * from T;

220248 rows created.

Sec@ora10g>/
Sec@ora10g>/
Sec@ora10g>/
Sec@ora10g>/
Sec@ora10g>/

440496 rows created.

2. Write a simple pl/sql block to simulate progressive submission, and observe the execution time.
Our goal is to insert all the data from the T_ref table into T.
Sec@ora10g> Set Timing on

Sec@ora10g> DECLARE
2 BEGIN
3 for cur in (SELECT * from T_ref) LOOP
4 INSERT into T VALUES cur;
5 COMMIT;
6 End LOOP;
7 End;
8/

Pl/sql procedure successfully completed.

elapsed:00:03:12.77

A total of 3 minutes and 12 seconds per row commit.

3. To simulate the situation of bulk submissions.
sec@ora10g> truncate TABLE t;

Table truncated.

Sec@ora10g> DECLARE
2 V_count number;
3 BEGIN
4 for cur in (SELECT * from T_ref) LOOP
5 INSERT into T VALUES cur;
6 V_count: = V_count + 1;
7 IF v_count >= THEN
8 COMMIT;
9 End IF;
Ten end LOOP;
COMMIT;
End;
13/

Pl/sql procedure successfully completed.

elapsed:00:01:27.69

This time is 1 minutes 27 seconds, approximately half the time to commit the method line by row. This shows that Oracle should try to complete the work in bulk submissions.

4. Finally, we use Oracle's more advanced approach to complete the above task and experience the ultimate speed.
Sec@ora10g> DECLARE
2 CURSOR cur is
3 SELECT * from T_ref;
4 TYPE Rec is TABLE of t_ref%rowtype;
5 RECs Rec;
6 BEGIN
7 OPEN cur;
8 while (TRUE) LOOP
9 FETCH cur BULK COLLECT
into RECs LIMIT 100;
One forall i in 1. RECs. COUNT
INSERT into T VALUES RECs (i);
COMMIT;
The EXIT when Cur%notfound;
End LOOP;
Close cur;
The end;
18/

elapsed:00:00:09.75

At this point we just used less than 10 seconds to complete the task that took a few minutes to complete.

5. Summary
In an Oracle database, frequent commits can cause a lot of physical I/O to redo log, which can greatly limit the performance of the database. Therefore, in order to improve database performance, as much as possible batch submissions.
Oracle has a lot of good ways to be worth trying.

Good luck.

--The end--
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.