skip to content »

www.gazetaperekrestok.ru

Cursor for updating in oracle

cursor for updating in oracle-78

Here is the statement that calls the Parallel Enabled Table Function: Note that we are using a SELECT statement to call a function that performs an UPDATE. You need to make the function an AUTONOMOUS TRANSACTION to stop it from throwing an error. For this round, I have created a parent table and a Foreign Key on the FK column. Given that most bitmap-indexed tables would have several such indexes (as bitmap indexes are designed to be of most use in combination), this shows that PL/SQL is virtually non-viable as a means of updating a large number of rows.But just bear with me, it is the closest PL/SQL equivalent I can make to a third-party ETL Tool such as Data Stage with native parallelism. For brevity, this time we'll just flush the buffer cache and run about 5 minutes worth of indexed reads to cycle the disk cache. Although we are updating only 1% of the rows in the table, those rows are almost perfectly distributed throughout the table.

cursor for updating in oracle-48

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.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 ---------- ------------ cursor: pin S wait on X 3 0.02 0.06 PX Deq: Execution Msg 16 1.96 10.94 PX Deq: Msg Fragment 2 0.00 0.00 latch: parallel query alloc buffer 7 5.89 7.52 db file sequential read 825 0.10 12.00 read by other session 17 0.06 0.18 log buffer space 1 0.03 0.03 PX Deq Credit: send blkd 1 0.02 0.02 PX Deq: Table Q Normal 28 0.19 0.35 latch: cache buffers chains 1 0.01 0.01 db file parallel read 1 0.11 0.11 From this, we can see that of the 30.3 seconds the Co-ordinator spent waiting for the parallel threads, this one spent 7.52 waiting for shared resources (latches) held by other parallel threads, and just 12 seconds reading blocks from disk.For comparison, here is the trace of the Co-ordinator session of a Parallel PL/SQL run: SELECT sum(column_value) FROM TABLE(test_parallel_update( CURSOR(SELECT * FROM TEST7) )) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.05 0.13 7 87 1 0 Execute 1 0.20 12.47 0 3 0 0 Fetch 2 0.21 20.13 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.46 32.74 7 90 1 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 140 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=32609316 us) 128 PX COORDINATOR (cr=3 pr=0 pw=0 time=252152371 us) 0 PX SEND QC (RANDOM) : TQ10000 (cr=0 pr=0 pw=0 time=0 us) 0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us) 0 VIEW (cr=0 pr=0 pw=0 time=0 us) 0 COLLECTION ITERATOR PICKLER FETCH TEST_PARALLEL_UPDATE (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 TEST7 (cr=0 pr=0 pw=0 time=0 us) Elapsed times include waiting on following events: Event waited on Times Max.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.

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.

The interesting thing about this method is that it performs a context-switch between PL/SQL and SQL for every FETCH; this is less efficient.

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.

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.

), how I might cluster rows together that are subject to updates, and what I might do if I just get too many updates to handle. The fastest way to update every row in the table is to rebuild the table from scratch. Case 2 is common in Data Warehouses and overnight batch jobs.