|Commit Enhancements in Oracle 10g Database Release 2 - Use the |
Peeush T said...Quite Informative and useful too .
Duplicate said...Not sure why the duplicate tests were performed:
"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
PROCEDURE do_loop (p_type IN VARCHAR2) AS
l_loops NUMBER := 50000;
type tt is table of commit_test%rowtype index by pls_integer;
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;
forall i in tx.first..tx.last
INSERT INTO commit_test VALUES tx(i);
DBMS_OUTPUT.put_line(RPAD('COMMIT_WRITE=' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start));
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.
DO NOT ask technical questions here! They will be deleted!
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!