Updating fetch in plsql

I spend an inordinate proportion of design time of an ETL system worrying about the relative proportion of rows inserted vs updated.

I want to test on a level playing field and remove special factors that unfairly favour one method, so there are some rules: TEST (Update Source) - 100K rows TEST (Update target) - 10M rows Name Type Name Type ------------------------------ ------------ ------------------------------ ------------ PK NUMBER PK NUMBER FK NUMBER FK NUMBER FILL VARCHAR2(40) FILL VARCHAR2(40) Not many people code this way, but there are some Pro*C programmers out there who are used to Explicit Cursor Loops (OPEN, FETCH and CLOSE commands) and translate these techniques directly to PL/SQL.

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.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.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.------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 130K| 9921K| 258K (1)| | 1 | MERGE | TEST | | | | | 2 | VIEW | | | | | | 3 | NESTED LOOPS | | 130K| 11M| 258K (1)| | 4 | TABLE ACCESS FULL | TEST2 | 128K| 6032K| 172 (5)| | 5 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 2 (0)| | 6 | INDEX UNIQUE SCAN | TEST_PK | 1 | | 1 (0)| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 109K| 8325K| 1880 (1)| | | | | 1 | PX COORDINATOR | | | | | | | | | 2 | PX SEND QC (RANDOM) | : TQ10002 | 109K| 10M| 1880 (1)| Q1,02 | P-P | HYBRID (ROW| | 9 | VIEW | | | | | Q1,00 | PCWP | | | 10 | NESTED LOOPS | | 109K| 10M| 1880 (1)| Q1,00 | PCWP | | | 11 | PX BLOCK ITERATOR | | | | | Q1,00 | PCWC | | | 12 | TABLE ACCESS FULL | TEST2 | 107K| 5062K| 2 (0)| Q1,00 | PCWP | | | 13 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 48 | 0 (0)| Q1,00 | PCWP | | | 14 | INDEX UNIQUE SCAN | TEST_PK | 1 | | 0 (0)| Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------------- This is much easier to do with Data Stage than with native PL/SQL. Parallel PL/SQL 25.3 23.8 Surely no comparison of update methods could possibly be complete without a test of Bitmap index maintenance. The other intesting outcome is the differing impact of the bitmap index on SET-based updates vs transactional updates (SQL solutions vs PL/SQL solutions).The goal is to have several separate sessions applying UPDATE statements at once, rather than using the sometimes restrictive PARALLEL DML alternative. LAST UPDATE test SET fk = fk_tab(i) , fill = fill_tab(i) WHERE pk = pk_tab(i); cnt := cnt pk_tab. In this round, I have removed the Foreign Key used in Round 2, and included a Bitmap index on TEST. PL/SQL solutions seem to incur a penalty when updating bitmap indexed tables.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.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.

SHOW COMMENTS

Comments Updating fetch in plsql

  • Change list of SQL Parser for Java, C#,
    Reply

    MySQL SQL Parser for C#, and VC, VB, Delphi…

  • PL/SQL Optimization and Tuning - Oracle
    Reply

    This chapter explains how the PL/SQL compiler optimizes your code and how to write efficient PL/SQL code and improve existing PL/SQL code.…

  • PL/SQL-updating salary of an
    Reply

    I need to update the salary of employees from department 40 and 70. All employees from department 40 will have a 10% increase while employees…

  • PL/SQL Oracle tutorial video Learn
    Reply

    The PL/SQL Oracle training video explains basic and more advanced aspects of programming in Oracle 11g. It focuses on practical examples. Total length 3 h 2 min, video resolution 1280 x 720.…

  • Bulk Update Methods Compared
    Reply

    APPENDIX 1 - Nested Loops MERGE vs. Hash Join MERGE. Although we are updating only 1% of the rows in the table, those rows are…

  • SELECT - Oracle
    Reply

    Purpose. Use a SELECT statement or subquery to retrieve data from one or more tables, object tables, views, object views, or materialized views. If part or all of the result of a SELECT statement is equivalent to an existing materialized view, then Oracle Database may use the materialized view in place of one or more tables specified in the.…

  • Oracle / PLSQL WHERE CURRENT OF
    Reply

    This Oracle tutorial explains how to use the Oracle / PLSQL WHERE CURRENT OF statement with syntax and examples. If you plan on updating or deleting records that have been referenced by a Select For Update statement, you…

  • Plsql - What is the difference between
    Reply

    With explicit cursors, you have complete control over how to access information in the database. You decide when to OPEN the cursor, when to FETCH records from the cursor and therefore from the table or tables in the SELECT statement of the cursor how many records to fetch, and when to CLOSE the cursor.…

  • Oracle / PLSQL SELECT FOR UPDATE
    Reply

    This Oracle tutorial explains how to use the Oracle / PLSQL SELECT FOR UPDATE statement with syntax and examples. The SELECT FOR UPDATE statement allows you to lock the records in the cursor result set.…

  • Oracle EXAM 1Z0-144 - oraclechennai.in
    Reply

    For interactive and self-paced preparation of exam 1Z0-144, try our practice exams. Practice exams also include self assessment and reporting features!…

The Latest from altay-blog.ru ©