SQL> ed Wrote file afiedt.buf 1 DECLARE 2 v_new_id NUMBER; 3 CURSOR c IS SELECT id_row FROM id FOR UPDATE; 4 BEGIN 5 runstats_pkg.rs_start; 6 for i in 1..10000 loop 7 OPEN c; 8 FETCH c INTO v_new_id; 9 -- do your work; 10 UPDATE id SET id_row = id_row + 1 WHERE CURRENT OF c; 11 CLOSE c; 12 end loop; 13 runstats_pkg.rs_middle; 14 for i in 1..10000 loop 15 UPDATE id SET id_row = id_row + 1 RETURNING id_row INTO v_new_id; 16 -- do your work; 17 end loop; 18 runstats_pkg.rs_stop; 19* END; SQL> / Run1 ran in 183 hsecs Run2 ran in 94 hsecs run 1 ran in 194,68% of the time Name Run1 Run2 Diff STAT...switch current to new b 10,000 10,001 1 LATCH.library cache lock alloc 5 4 -1 STAT...calls to kcmgcs 5 6 1 STAT...heap block compress 6 5 -1 LATCH.compile environment latc 1 0 -1 STAT...cleanout - number of kt 5 6 1 LATCH.OS process allocation 1 0 -1 STAT...active txn count during 5 6 1 LATCH.session timer 1 0 -1 LATCH.kks stats 0 1 1 STAT...opened cursors current 2 1 -1 STAT...messages sent 2 3 1 LATCH.Consistent RBA 2 3 1 LATCH.lgwr LWN SCN 2 3 1 LATCH.mostly latch-free SCN 2 3 1 LATCH.KMG MMAN ready and start 1 0 -1 STAT...enqueue releases 7 5 -2 STAT...enqueue requests 7 5 -2 STAT...opened cursors cumulati 9 11 2 STAT...parse count (hard) 5 3 -2 STAT...redo entries 10,012 10,014 2 STAT...cursor authentications 4 2 -2 LATCH.In memory undo latch 2 4 2 LATCH.undo global data 15 13 -2 LATCH.session allocation 96 98 2 LATCH.library cache pin alloca 9 6 -3 STAT...consistent changes 19 23 4 STAT...rows fetched via callba 0 4 4 STAT...index fetch by key 0 4 4 LATCH.library cache load lock 0 4 4 LATCH.redo allocation 6 11 5 LATCH.session idle bit 9 16 7 STAT...change write time 10 3 -7 LATCH.simulator hash latch 52 45 -7 STAT...index scans kdiixs1 0 8 8 STAT...parse count (total) 9 17 8 LATCH.active service list 0 8 8 STAT...shared hash latch upgra 0 8 8 LATCH.row cache objects 323 314 -9 STAT...session cursor cache hi 7 16 9 LATCH.simulator lru latch 31 21 -10 LATCH.redo writing 21 9 -12 LATCH.active checkpoint queue 12 0 -12 LATCH.channel operations paren 14 0 -14 STAT...hot buffers moved to he 16 0 -16 STAT...table fetch by rowid 10 26 16 STAT...consistent gets - exami 5 26 21 STAT...prefetched blocks aged 127 149 22 LATCH.messages 44 13 -31 LATCH.JS queue state obj latch 0 36 36 STAT...buffer is not pinned co 20 56 36 STAT...redo ordering marks 107 145 38 STAT...calls to kcmgas 10,107 10,146 39 STAT...workarea memory allocat 39 0 -39 STAT...free buffer requested 10,107 10,146 39 STAT...db block changes 20,139 20,183 44 LATCH.cache buffers lru chain 20,054 20,002 -52 STAT...free buffer inspected 330 270 -60 LATCH.enqueues 23 86 63 LATCH.enqueue hash chains 22 87 65 LATCH.object queue header oper 30,559 30,494 -65 LATCH.SQL memory manager worka 75 6 -69 LATCH.library cache lock 225 153 -72 STAT...recursive cpu usage 169 95 -74 STAT...Elapsed Time 188 97 -91 STAT...CPU used by this sessio 188 97 -91 LATCH.checkpoint queue latch 10,131 10,001 -130 LATCH.shared pool 466 10,212 9,746 STAT...db block gets from cach 20,245 10,326 -9,919 STAT...db block gets 20,245 10,326 -9,919 STAT...execute count 20,008 10,017 -9,991 STAT...table scan rows gotten 20,011 10,014 -9,997 STAT...table scans (short tabl 20,002 10,005 -9,997 STAT...no work - consistent re 70,017 50,048 -19,969 STAT...table scan blocks gotte 70,007 50,022 -19,985 LATCH.library cache pin 40,228 20,204 -20,024 LATCH.library cache 40,585 20,458 -20,127 STAT...recursive calls 40,055 10,135 -29,920 STAT...consistent gets from ca 110,034 70,102 -39,932 STAT...consistent gets 110,034 70,102 -39,932 STAT...calls to get snapshot s 70,014 30,035 -39,979 STAT...session logical reads 130,279 80,428 -49,851 STAT...session uga memory 57,976 0 -57,976 STAT...session pga memory 0 65,536 65,536 LATCH.cache buffers chains 290,931 191,158 -99,773 STAT...undo change vector size 843,872 1,156,712 312,840 STAT...redo size 2,413,000 3,040,948 627,948 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 433,949 303,474 -130,475 142.99% PL/SQL procedure successfully completed. Elapsed: 00:00:03.59