Compare Redo log size between commit outside of loop and inside of loop

Here is my test on commit outside of loop and inside of loop. It will show the generated redo size will be much less when commit outside of loop.

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.6.0 – Production
PL/SQL Release 9.2.0.6.0 – Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 – Production
NLSRTL Version 9.2.0.6.0 – Production

SQL> select name,value from v$sysstat where name like ‘redo size’;

NAME VALUE
—————————————————————- ———-
redo size 88813560

SQL> desc t
Name Null? Type
—————————————– ——– —————————-
C1 NUMBER

SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select name,value from v$sysstat where name like ‘redo size’;

NAME VALUE
—————————————————————- ———-
redo size 91160824

SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select name,value from v$sysstat where name like ‘redo size’;

NAME VALUE
—————————————————————- ———-
redo size 96090180

SQL> select 91160824-88813560
2 commit1 from dual;

COMMIT1
———-
2347264 –redo size when commit outside of loop

SQL> select 96090180-91160824 commitN from dual;

COMMITN
———-
4929356 –redo size when commit inside of loop, more than doulbe of previous

So commit outside of loop whenever possible.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: