Comments
Commit Enhancements in Oracle 10g Database Release 2 - Use the WRITE clause of the COMMIT command or the COMMIT_WRITE parameter to influence the way redo information is processed by commit operations. |
Peeush T said... Quite Informative and useful too . |
Duplicate said... Not sure why the duplicate tests were performed:COMMIT_WRITE=WAIT=IMMEDIATE,WAIT COMMIT_WRITE=NOWAIT=IMMEDIATE,WAIT COMMIT_WRITE=BATCH=BATCH,WAIT COMMIT_WRITE=IMMEDIATE=IMMEDIATE,WAIT "If only IMMEDIATE or BATCH is specified, but not WAIT or NOWAIT, then WAIT mode is assumed. If only WAIT or NOWAIT is specified, but not IMMEDIATE or BATCH, then IMMEDIATE mode is assumed." |
LoLo said... I agree with Duplicate.Moreover, the test "COMMIT_WRITE" is not significant. Example : COMMIT_WRITE=BATCH (default IMMEDIATE) : 78 COMMIT_WRITE=IMMEDIATE,WAIT : 133 |
LoLo said... Correction about my example :COMMIT_WRITE=BATCH (default WAIT) : 78 COMMIT_WRITE=BATCH,WAIT : 139 ==> not significant |
imarek said... Hello all,there is example with bulk processing. U can compare results :) SET SERVEROUTPUT ON DECLARE PROCEDURE do_loop (p_type IN VARCHAR2) AS l_start NUMBER; l_loops NUMBER := 50000; type tt is table of commit_test%rowtype index by pls_integer; tx tt; BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE=''' || p_type || ''''; EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test'; l_start := DBMS_UTILITY.get_time; FOR i IN 1 .. l_loops LOOP tx(i).id := i; tx(i).description := 'Description for ' || i; END LOOP; forall i in tx.first..tx.last INSERT INTO commit_test VALUES tx(i); tx.delete; COMMIT; DBMS_OUTPUT.put_line(RPAD('COMMIT_WRITE=' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start)); END; BEGIN do_loop('WAIT'); do_loop('NOWAIT'); do_loop('BATCH'); do_loop('IMMEDIATE'); do_loop('BATCH,WAIT'); do_loop('BATCH,NOWAIT'); do_loop('IMMEDIATE,WAIT'); do_loop('IMMEDIATE,NOWAIT'); END; / |
cnita said... imarek, it is not recommended to put 50000 records in a collection ... you should avoid this kind of approach ... or use limits when loading into collection, chunks ... regards |
Tim... said... cnita: Correct.imarek: The article is about the different types of commits. The example code is showing how the timing of each commit type varies. To measure the difference we need to do a lot of commits, not just one, so for this demonstration using a bulk operation is completely *not* what we want. I even mention in the text I need a commit for each insert in the example code. Cheers Tim... |
DO NOT ask technical questions here, that's what my forum is for!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!