Updating using explicit cursors

If the proportion of updated blocks increases, then the average cost of finding those rows decreases; the exercise becomes one of tuning the data access rather than tuning the update.Why is the Parallel PL/SQL (Method 8) approach much faster than the Parallel DML MERGE (Method 7)? Below we see the trace from the Parallel Coordinator session of Method 7: MERGE /* first_rows */ INTO test USING test5 new ON (= new.pk) WHEN MATCHED THEN UPDATE SET fk = , fill = call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.02 0 4 1 0 Execute 1 1.85 57.91 1 7 2 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 1.87 57.94 1 11 3 100000 Misses in library cache during parse: 1 Optimizer mode: FIRST_ROWS Parsing user id: 140 Rows Row Source Operation ------- --------------------------------------------------- 128 PX COORDINATOR (cr=7 pr=1 pw=0 time=57912088 us) 0 PX SEND QC (RANDOM) : TQ10002 (cr=0 pr=0 pw=0 time=0 us) 0 INDEX MAINTENANCE TEST (cr=0 pr=0 pw=0 time=0 us)(object id 0) 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us) 0 PX SEND RANGE : TQ10001 (cr=0 pr=0 pw=0 time=0 us) 0 MERGE TEST (cr=0 pr=0 pw=0 time=0 us) 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us) 0 PX SEND HYBRID (ROWID PKEY) : TQ10000 (cr=0 pr=0 pw=0 time=0 us) 0 VIEW (cr=0 pr=0 pw=0 time=0 us) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL TEST5 (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID TEST (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN TEST_PK (cr=0 pr=0 pw=0 time=0 us)(object id 141439) Elapsed times include waiting on following events: Event waited on Times Max.Of course, as you decrease the percentage of blocks updated, the balance will swing in favour of Nested Loops; but this trace demonstrates that MERGE definitely has it's place in high-volume updates.MERGE /* FIRST_ROWS*/ INTO test USING test2 new ON (= new.pk) WHEN MATCHED THEN UPDATE SET fk = , fill = ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 95331 | 7261K| 191K (1)| | 1 | MERGE | TEST | | | | | 2 | VIEW | | | | | | 3 | NESTED LOOPS | | 95331 | 8937K| 191K (1)| | 4 | TABLE ACCESS FULL | TEST2 | 95331 | 4468K| 170 (3)| | 5 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 2 (0)| | 6 | INDEX UNIQUE SCAN | TEST_PK | 1 | | 1 (0)| ------------------------------------------------------------------------------- call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 4 1 0 Execute 1 57.67 829.77 95323 383225 533245 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 57.68 829.78 95323 383229 533246 100000 Misses in library cache during parse: 1 Optimizer mode: FIRST_ROWS Parsing user id: 140 Rows Row Source Operation ------- --------------------------------------------------- 1 MERGE TEST (cr=383225 pr=95323 pw=0 time=127458586 us) 100000 VIEW (cr=371028 pr=75353 pw=0 time=619853020 us) 100000 NESTED LOOPS (cr=371028 pr=75353 pw=0 time=619653018 us) 100000 TABLE ACCESS FULL TEST2 (cr=750 pr=386 pw=0 time=505310 us) 100000 TABLE ACCESS BY INDEX ROWID TEST (cr=370278 pr=74967 pw=0 time=615942540 us) 100000 INDEX UNIQUE SCAN TEST_PK (cr=200015 pr=227 pw=0 time=4528703 us)(object id 141439) Elapsed times include waiting on following events: Event waited on Times Max.DECLARE CURSOR rec_cur IS SELECT * FROM test4; TYPE num_tab_t IS TABLE OF NUMBER(38); TYPE vc2_tab_t IS TABLE OF VARCHAR2(4000); pk_tab NUM_TAB_T; fk_tab NUM_TAB_T; fill_tab VC2_TAB_T; BEGIN OPEN rec_cur; LOOP FETCH rec_cur BULK COLLECT INTO pk_tab, fk_tab, fill_tab LIMIT 1000; EXIT WHEN pk_tab. This is to keep the playing field level when comparing to the other methods, which also perform primary key lookups on the target table. With hundreds of rows represented by each block in the index, the chances of two sessions attempting to lock the same block are quite high.A Hash join may or may not be faster, that's not the point - I could increase the size of the target TEST table to 500M rows and Hash would be slower for sure. The very clear lesson here: don't update bitmap indexed tables in parallel sessions; the only safe parallel method is PARALLEL DML.The UPDATE portion of the code works in an identical fashion to the Implicit Cursor Loop, so this is not really a separate "UPDATE" method as such.

Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 19606 0.37 41.24 db file scattered read 4720 0.52 34.20 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.03 0.03 That's a pretty significant difference: the same method (MERGE) is 6-7 times faster when performed as a Hash Join.

I include it here because it allows us to compare the cost of context-switches to the cost of updates.

DECLARE CURSOR c1 IS SELECT * FROM test6; rec_cur c1%rowtype; BEGIN OPEN c1; LOOP FETCH c1 INTO rec_cur; EXIT WHEN c1%notfound; UPDATE test SET fk = rec_, fill = rec_WHERE pk = rec_cur.pk; END LOOP; CLOSE C1; END; / This is the simplest PL/SQL method and very common in hand-coded PL/SQL applications.

Although the number of physical disk blocks and Current Mode Gets are about the same in each test, the Hash Join method performs multi-block reads, resulting in fewer visits to the disk.

All 8 methods above were benchmarked on the assumption that the target table is arbitrarily large and the subset of rows/blocks to be updated are relatively small.

Leave a Reply