every time you commit ,there is additional information generated.
if you commit 1,000,000 records one at a time, you generate 1,000,000 commit records.
if you commit 1,000,000 records once, you generate one commit record.
big_table%ORA11GR2> create table t ( x int, y char(30) );
Table created.
big_table%ORA11GR2> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
big_table%ORA11GR2> begin
2 for i in 1..1000000
3 loop
4 insert into t (x,y) values ( i, 'x' );
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
big_table%ORA11GR2> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
big_table%ORA11GR2> begin
2 for i in 1..1000000
3 loop
4 insert into t (x,y) values ( i, 'x' );
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
big_table%ORA11GR2> exec runStats_pkg.rs_stop(100000);
Run1 ran in 8196 cpu hsecs
Run2 ran in 2688 cpu hsecs
run 1 ran in 304.91% of the time
Name Run1 Run2 Diff
STAT...cell physical IO interc 204,800 0 -204,800
STAT...physical read bytes 204,800 0 -204,800
STAT...physical read total byt 204,800 0 -204,800
LATCH.simulator hash latch 296,713 86,915 -209,798
STAT...session pga memory -131,072 196,608 327,680
STAT...redo ordering marks 366,752 12,030 -354,722
STAT...deferred (CURRENT) bloc 502,910 10 -502,900
STAT...messages sent 508,157 455 -507,702
LATCH.Consistent RBA 508,160 373 -507,787
LATCH.lgwr LWN SCN 508,520 367 -508,153
LATCH.mostly latch-free SCN 512,329 374 -511,955
STAT...commit cleanouts 1,000,034 292 -999,742
STAT...user commits 1,000,002 1 -1,000,001
STAT...commit cleanouts succes 1,000,024 20 -1,000,004
LATCH.session allocation 1,000,234 70 -1,000,164
LATCH.session idle bit 1,000,264 89 -1,000,175
STAT...opened cursors cumulati 2,000,336 1,000,071 -1,000,265
STAT...calls to get snapshot s 2,000,536 1,000,087 -1,000,449
LATCH.shared pool 1,001,652 1,039 -1,000,613
STAT...redo entries 2,036,225 1,029,248 -1,006,977
LATCH.messages 1,020,710 1,912 -1,018,798
STAT...calls to kcmgas 1,366,863 12,057 -1,354,806
LATCH.redo writing 1,524,398 1,348 -1,523,050
LATCH.redo allocation 1,528,000 2,801 -1,525,199
STAT...db block changes 4,037,263 2,041,313 -1,995,950
STAT...session logical reads 3,068,258 1,071,741 -1,996,517
LATCH.DML lock allocation 2,000,495 37 -2,000,458
STAT...db block gets 3,066,122 1,065,639 -2,000,483
STAT...db block gets from cach 3,066,122 1,065,639 -2,000,483
STAT...enqueue requests 2,001,183 557 -2,000,626
STAT...enqueue releases 2,001,184 557 -2,000,627
STAT...recursive calls 3,002,365 1,000,558 -2,001,807
LATCH.undo global data 3,022,545 17,555 -3,004,990
LATCH.enqueue hash chains 4,006,110 1,925 -4,004,185
LATCH.cache buffers chains 12,294,175 5,258,539 -7,035,636
STAT...undo change vector size 159,786,564 91,972,248 -67,814,316
STAT...redo size 720,572,880 302,892,988-417,679,892
STAT...logical read bytes from####################################
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
31,455,790 6,520,620 -24,935,170 482.40%
PL/SQL procedure successfully completed.
2x the redo