Oracle updating chunks big table datingbug net

Rated 4.45/5 based on 563 customer reviews

Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1 0.02 0.02 reliable message 1 0.00 0.00 enq: RO - fast object reuse 1 0.00 0.00 os thread startup 256 0.09 23.61 PX Deq: Join ACK 7 0.00 0.00 PX Deq: Parse Reply 15 0.09 0.19 PX Deq Credit: send blkd 35 0.00 0.00 PX qref latch 5 0.00 0.00 PX Deq: Execute Reply 1141 1.96 30.30 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.05 0.05 We can see here that the Parallel Co-ordinator spent 23.61 seconds (of the 57.94 elapsed) simply starting up the parallel threads, and 30.3 seconds waiting for them to do their stuff.

And here are the wait events for just ONE of the parallel threads from the same test case: Elapsed times include waiting on following events: Event waited on Times Max.

Wait Total Waited ---------------------------------------- Waited ---------- ------------ PX Deq: Execution Msg 8 0.13 0.22 PX Deq: Msg Fragment 1 0.00 0.00 library cache load lock 3 0.19 0.30 db file sequential read 872 0.11 16.47 read by other session 13 0.06 0.25 latch: cache buffers chains 3 0.01 0.02 The Parallel PL/SQL spent just 11.85 seconds starting parallel threads, compared to 23.61 seconds for PARALLEL DML.

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.

In this test, we apply the 100K updated rows in Global Temporary Table TEST to permanent table TEST. UPDATE with nested SET subquery 941.4 891.5 31.5 4. RUN 1 RUN 2 ----------------------------------- ----- ----- 1. As a result, we end up updating almost 100% of the blocks.

There are 3 runs: RUN 1 RUN 2 RUN 3 ----------------------------------- ----- ----- ----- 1. This makes it a good candidate for hash joins and full scans to out-perform indexed nested loops.

Leave a Reply