commit 对性能的影响


class BenchmarkIntermittentCommits
{
public static void main( String[] args ) throws Exception
{
Connection conn = null;
try
{
conn = JDBCUtil.getConnection( "fuyou", "fuyou", "orcl" );
JRunstats.prepareBenchmarkStatements( conn );
JRunstats.markStart( conn );
_doInsertCommitInLoop( conn );
JRunstats.markMiddle( conn );
_doInsertCommitOutsideLoop( conn );
JRunstats.markEnd( conn );
}
finally
{
JRunstats.closeBenchmarkStatements( conn );
JDBCUtil.close( conn );
}
}
private static void _doInsertCommitInLoop( Connection conn ) throws SQLException
{
String stmtString = "insert into t1( x ) values ( ? )";
PreparedStatement pstmt = null;
try
{
pstmt = conn.prepareStatement( stmtString );
for( int i=0; i < NUM_OF_RECORDS; i++ )
{
pstmt.setInt( 1, 1 );
pstmt.executeUpdate();
conn.commit();
}
}
finally
{
JDBCUtil.close( pstmt );
}
}
private static void _doInsertCommitOutsideLoop( Connection conn ) throws SQLException
{
String stmtString = "insert into t1( x ) values ( ? )";
PreparedStatement pstmt = null;
try
{
pstmt = conn.prepareStatement( stmtString );
for( int i=0; i < NUM_OF_RECORDS; i++ )
{
pstmt.setInt( 1, 1 );
pstmt.executeUpdate();
}
conn.commit();
}
finally
{
JDBCUtil.close( pstmt );
}
}

private static final int NUM_OF_RECORDS = 10000;
}



结果 :

数据库URL:jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=127.0.0.1))(CONNECT_DATA=(SID=orcl)))
------- Benchmark Results --------
Results from RUNSTATS utility
Run1 ran in 4083 hsecs
Run2 ran in 126 hsecs
run 1 ran in 3240.48% of the time

Name Run1 Run2 Diff
LATCH.ktm global data 1 0 -1
LATCH.Shared B-Tree 1 0 -1
STAT...session cursor cache co 1 0 -1
LATCH.transaction branch alloc 1 0 -1
LATCH.list of block allocation 2 1 -1
STAT...session cursor cache hi 10 11 1
LATCH.rules engine aggregate s 1 0 -1
LATCH.archive control 1 0 -1
LATCH.ncodef allocation latch 1 0 -1
STAT...commit txn count during 0 1 1
LATCH.resmgr:actses change gro 1 0 -1
LATCH.KTF sga latch 1 0 -1
LATCH.event group latch 1 0 -1
LATCH.AWR Alerted Metric Eleme 1 0 -1
LATCH.object stats modificatio 1 0 -1
LATCH.KWQMN job cache list lat 1 0 -1
LATCH.session switching 1 0 -1
LATCH.kwqbsn:qsga 1 0 -1
STAT...sorts (rows) 2,339 2,338 -1
STAT...parse count (hard) 3 2 -1
STAT...parse time cpu 1 0 -1
STAT...workarea executions - o 7 6 -1
LATCH.sort extent pool 2 0 -2
LATCH.commit callback allocati 2 0 -2
LATCH.FAL request queue 2 0 -2
LATCH.FAL subheap alocation 2 0 -2
LATCH.cache buffer handles 12 10 -2
LATCH.OS process: request allo 2 0 -2
LATCH.channel handle pool latc 2 0 -2
LATCH.FOB s.o list latch 2 0 -2
LATCH.parameter table allocati 2 0 -2
LATCH.process group creation 2 0 -2
LATCH.process allocation 2 0 -2
STAT...parse time elapsed 2 0 -2
STAT...index fetch by key 6 4 -2
STAT...rows fetched via callba 2 0 -2
STAT...no work - consistent re 22 20 -2
LATCH.threshold alerts latch 2 0 -2
LATCH.rules engine rule set st 2 0 -2
LATCH.hash table modification 2 0 -2
LATCH.job_queue_processes para 2 0 -2
LATCH.kks stats 6 4 -2
LATCH.user lock 2 0 -2
LATCH.JS slv state obj latch 3 0 -3
LATCH.PL/SQL warning settings 4 1 -3
STAT...consistent gets - exami 22 25 3
LATCH.ksuosstats global area 3 0 -3
LATCH.transaction allocation 3 0 -3
LATCH.sequence cache 3 0 -3
STAT...table fetch by rowid 14 10 -4
LATCH.dummy allocation 4 0 -4
STAT...consistent changes 38 34 -4
LATCH.qmn task queue latch 4 0 -4
LATCH.resmgr:free threads list 4 0 -4
LATCH.resmgr:actses active lis 4 0 -4
LATCH.library cache load lock 4 0 -4
STAT...opened cursors cumulati 19 14 -5
LATCH.library cache pin alloca 12 7 -5
LATCH.resmgr group change latc 5 0 -5
STAT...sorts (memory) 7 1 -6
STAT...consistent gets 79 73 -6
STAT...consistent gets from ca 79 73 -6
LATCH.session state list latch 7 0 -7
STAT...parse count (total) 21 14 -7
STAT...buffer is not pinned co 36 28 -8
STAT...index scans kdiixs1 8 0 -8
STAT...shared hash latch upgra 8 0 -8
STAT...execute count 10,022 10,013 -9
LATCH.OS process 9 0 -9
LATCH.KMG MMAN ready and start 14 0 -14
LATCH.session timer 14 0 -14
LATCH.archive process latch 15 1 -14
STAT...cleanout - number of kt 1 16 15
STAT...active txn count during 1 16 15
LATCH.library cache lock alloc 21 6 -15
STAT...calls to kcmgcs 4 20 16
LATCH.active checkpoint queue 19 0 -19
LATCH.OS process allocation 20 0 -20
LATCH.client/application info 25 0 -25
STAT...change write time 28 3 -25
STAT...workarea memory allocat -29 0 29
LATCH.hash table column usage 41 0 -41
STAT...IMU Flushes 45 1 -44
STAT...recursive cpu usage 63 2 -61
STAT...redo entries 10,176 10,109 -67
LATCH.active service list 88 10 -78
STAT...redo ordering marks 4 85 81
STAT...free buffer requested 193 107 -86
LATCH.cache buffers lru chain 225 107 -118
LATCH.channel operations paren 197 8 -189
STAT...CPU used by this sessio 238 46 -192
STAT...CPU used when call star 241 47 -194
LATCH.object queue header oper 450 202 -248
LATCH.JS queue state obj latch 288 36 -252
LATCH.checkpoint queue latch 289 16 -273
LATCH.row cache objects 684 249 -435
LATCH.library cache lock 881 115 -766
LATCH.SQL memory manager worka 879 73 -806
LATCH.library cache pin 21,062 20,143 -919
STAT...IMU Redo allocation siz 12,120 11,040 -1,080
LATCH.simulator lru latch 3,535 673 -2,862
LATCH.simulator hash latch 3,565 694 -2,871
STAT...redo synch time 3,639 9 -3,630
STAT...DB time 3,884 59 -3,825
STAT...Elapsed Time 4,085 128 -3,957
STAT...deferred (CURRENT) bloc 5,003 7 -4,996
STAT...calls to kcmgas 10,010 92 -9,918
STAT...IMU commits 9,954 0 -9,954
STAT...commit cleanouts 10,008 24 -9,984
STAT...commit cleanouts succes 10,004 20 -9,984
STAT...messages sent 10,000 3 -9,997
STAT...user commits 10,000 1 -9,999
STAT...user calls 20,002 10,003 -9,999
STAT...SQL*Net roundtrips to/f 20,001 10,002 -9,999
STAT...redo synch writes 10,002 1 -10,001
LATCH.lgwr LWN SCN 10,005 3 -10,002
LATCH.mostly latch-free SCN 10,005 3 -10,002
LATCH.Consistent RBA 10,005 3 -10,002
LATCH.compile environment latc 10,010 4 -10,006
STAT...calls to get snapshot s 10,040 32 -10,008
STAT...recursive calls 10,212 126 -10,086
LATCH.shared pool 10,331 116 -10,215
LATCH.enqueues 10,772 110 -10,662
LATCH.library cache 31,936 20,346 -11,590
LATCH.post/wait queue 18,365 6 -18,359
STAT...db block changes 40,191 20,248 -19,943
STAT...enqueue releases 20,031 23 -20,008
STAT...enqueue requests 20,031 23 -20,008
LATCH.session allocation 20,104 78 -20,026
LATCH.dml lock allocation 20,163 12 -20,151
STAT...db block gets from cach 40,216 10,372 -29,844
STAT...db block gets 40,216 10,372 -29,844
STAT...session logical reads 40,295 10,445 -29,850
STAT...bytes received via SQL* 520,137 490,162 -29,975
LATCH.session idle bit 50,025 20,010 -30,015
LATCH.redo writing 30,061 11 -30,050
LATCH.messages 31,320 18 -31,302
STAT...bytes sent via SQL*Net 555,839 516,101 -39,738
LATCH.In memory undo latch 40,059 6 -40,053
LATCH.enqueue hash chains 41,080 125 -40,955
LATCH.redo allocation 50,041 13 -50,028
LATCH.undo global data 50,222 36 -50,186
STAT...session pga memory -65,536 0 65,536
LATCH.cache buffers chains 223,993 51,260 -172,733
STAT...session uga memory max 261,964 0 -261,964
STAT...session pga memory max 262,144 0 -262,144
STAT...undo change vector size 1,282,620 643,396 -639,224
STAT...redo size 5,095,516 2,378,912 -2,716,604
STAT...IMU undo allocation siz 3,674,944 880 -3,674,064

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
700,948 114,522 -586,426 612.06%


Runtime Execution Time Differences as seen by the client
Run1 ran in 4107 hsecs
Run2 ran in 129 hsecs
Run1 ran in 3167% of the time


可以看出区别了吧
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值