Db cursor updating in same table oracle


db cursor updating in same table oracle-51

Wait Total Waited ---------------------------------------- Waited ---------- ------------ reliable message 1 0.00 0.00 enq: RO - fast object reuse 1 0.00 0.00 os thread startup 128 0.10 11.85 PX Deq: Join ACK 4 0.00 0.00 PX Deq: Parse Reply 46 0.00 0.10 PX Deq Credit: send blkd 128 0.00 0.06 SQL*Net message to client 2 0.00 0.00 PX Deq: Execute Reply 143 1.96 19.86 PX Deq: Signal ACK 4 0.00 0.00 enq: PS - contention 2 0.00 0.00 SQL*Net message from client 2 0.03 0.06Elapsed 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.

Update-wise, it looks as though it should perform the same as the Explicit Cursor Loop.

The difference is that the Implicit Cursor internally performs bulk fetches, which should be faster than the Explicit Cursor because of the reduced context switches. I generally recommend against it for high-volume updates because the SET sub-query is nested, meaning it is performed once for each row updated. Using BULK COLLECT and FORALL statements is the new de-facto standard for PL/SQL programmers concerned about performance because it reduces context switching overheads between the PL/SQL and SQL engines.

Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file scattered read 37 0.20 0.72 db file sequential read 94936 0.39 781.52 buffer exterminate 1 0.97 0.97 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.05 0.05 ********************************************************************************MERGE INTO test USING test2 new ON (= new.pk) WHEN MATCHED THEN UPDATE SET fk = , fill = --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Temp Spc| Cost (%CPU)| --------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 95331 | 7261K| | 46318 (3)| | 1 | MERGE | TEST | | | | | | 2 | VIEW | | | | | | | 3 | HASH JOIN | | 95331 | 8937K| 5592K| 46318 (3)| | 4 | TABLE ACCESS FULL| TEST2 | 95331 | 4468K| | 170 (3)| | 5 | TABLE ACCESS FULL| TEST | 10M| 458M| | 16949 (4)| --------------------------------------------------------------------------- call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.40 1 4 1 0 Execute 1 54.50 123.48 94547 82411 533095 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 54.53 123.88 94548 82415 533096 100000 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 140 Rows Row Source Operation ------- --------------------------------------------------- 1 MERGE TEST (cr=82411 pr=94547 pw=0 time=123480418 us) 100000 VIEW (cr=75424 pr=74949 pw=0 time=48081374 us) 100000 HASH JOIN (cr=75424 pr=74949 pw=0 time=47981370 us) 100000 TABLE ACCESS FULL TEST2 (cr=750 pr=335 pw=0 time=1207771 us) 9999999 TABLE ACCESS FULL TEST (cr=74674 pr=74614 pw=0 time=10033917 us) Elapsed times include waiting on following events: Event waited on Times Max.