Aggregator | Books | Industry News | Firefox Plugins | Social | Links

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)