1 2 Previous Next 16 Replies Latest reply on Dec 30, 2015 5:24 PM by Jonathan Lewis

    Update small table with slow subselect

    854418

      We have a very small Table (84 records) and do an update with a very slow subselect:

       

      UPDATE

        SMALL_TABLE T

      SET FCR7=

        (SELECT

          FCR7*100

        FROM SLOW_VIEW D

        WHERE D.MONAT = T.MONAT

        AND D.DL      = T.DL

        )

      ;

      This Update runs about 3-8 hours.

       

      The Select

      SELECT * FROM SLOW_VIEW;

      has a result of 63 rows and takes about 7 minutes.


      I could use PL/SQL with a loop for the select and inside the update, this should speed up from hours to minutes.

       

      Q: Is there a more elegant way for this update? Optimizer Hint?

       

      DB is Oracle 12c EE

      First lines of the Plan, parallel execution disabled:

      -----------------------------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                         | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |

      -----------------------------------------------------------------------------------------------------------------------------------------

      |   0 | UPDATE STATEMENT                  |                             |    84 |  1764 |       |   216M  (1)| 10:12:05 |       |       |

      |   1 |  UPDATE                           | SMALL_TABLE                 |       |       |       |            |          |       |       |

      |   2 |   TABLE ACCESS FULL               | SMALL_TABLE                 |    84 |  1764 |       |     3   (0)| 00:00:01 |       |       |

      |   3 |   VIEW                            | SLOW_VIEW                   | 19047 |   539K|       |  2574K  (1)| 00:07:18 |       |       |

      |   4 |    SORT ORDER BY                  |                             | 19047 |   576K|       |  2574K  (1)| 00:07:18 |       |       |


      Greetings,

      Joachim

        • 1. Re: Update small table with slow subselect
          oralicious

          alter session set sql_trace=true; 

           

          rerun;

           

          tkprof the output

           

          paste it here

          • 2. Re: Update small table with slow subselect

            >I could use PL/SQL with a loop for the select and inside the update, this should speed up from hours to minutes.


            PL/SQL is rarely faster than plain SQL

             

            https://community.oracle.com/message/1814597#1814597

            • 3. Re: Update small table with slow subselect
              Igor Laguardia-Oracle

              Clearly it's not an update issue, but a view issue.

               

              What is the cost of the view Select?

              Did you check the execution plan?

              Is it using indexes?

               

              Think like this, for each row in your update table an select on the view is ran, if it takes 7 min in order to run the view once, 3 - 8 hours you will have when you multiply 7x84.

               

              One thing that come across is, you are using only one field of your view. Isn't simple to just select direct the table that you want that result, with the view's where clause?

              • 4. Re: Update small table with slow subselect
                JimmyOTNC

                do you have to use the view? do you have index on MONAT and DL on the view/table?

                • 5. Re: Update small table with slow subselect
                  oralicious

                  Igor Laguardia-Oracle wrote:

                   

                  Clearly it's not an update issue, but a view issue.

                   

                   

                  Clearly you cant say that.  If there was an on update  trigger on the table that wouldnt show up in the autotrace but would in the sql trace. 



                   

                  -- create a 9 row table

                  create table t_up as select * from all_objects where rownum < 10;

                   

                  -- create a 150k row table

                  create table t_up2 as select * from all_objects;

                   

                   

                  -- every time I update t_up, do something silly like insert 150k rows to t_up2


                  create or replace trigger trig_up  before update on t_up

                     for each row

                       BEGIN

                          insert into t_up2

                                (select * from all_objects);

                         end;



                  -- now update t_up, 9 rows get updated but it takes 19 seconds


                  16:31:43 SQL> update t_up set object_name = 'BOO';

                   

                   

                  9 rows updated.

                   

                   

                  Elapsed: 00:00:19.66

                   

                   

                  Execution Plan

                  ----------------------------------------------------------

                  Plan hash value: 1638310970

                   

                   

                  ---------------------------------------------------------------------------

                  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

                  ---------------------------------------------------------------------------

                  |   0 | UPDATE STATEMENT   |      |     9 |   153 |     3   (0)| 00:00:01 |

                  |   1 |  UPDATE            | T_UP |       |       |            |          |

                  |   2 |   TABLE ACCESS FULL| T_UP |     9 |   153 |     3   (0)| 00:00:01 |

                  ---------------------------------------------------------------------------

                   

                   

                  Note

                  -----

                     - dynamic sampling used for this statement (level=2)

                   

                   

                   

                   

                  Statistics

                  ----------------------------------------------------------

                         1008  recursive calls

                        77183  db block gets

                      2311727  consistent gets

                            0  physical reads

                     72372940  redo size

                          844  bytes sent via SQL*Net to client

                          792  bytes received via SQL*Net from client

                            3  SQL*Net roundtrips to/from client

                        16201  sorts (memory)

                            0  sorts (disk)

                            9  rows processed

                   

                   

                  16:32:10 SQL>




                  Still think its clearly the view thats the problem?  Now, it may be the view, but you do not know that without the trace


                  (sorry for the format of above, I dont have buttons for coding)




                  • 6. Re: Update small table with slow subselect
                    Igor Laguardia-Oracle

                    Would you bet your monthly salary in a trigger, or in a 7min runtime view that is used in the update? Think that clearly you got my point.

                    • 7. Re: Update small table with slow subselect
                      Pavan Kumar

                      Hi,

                       

                      Can you post the plan for it once executing this query, does any indexes exists on those segments / (view - related segments)

                       

                      select t.row_id,FCR7*100

                      from  SMALL_TABLE T,

                      SLOW_VIEW D

                      where D.MONAT = T.MONAT

                        AND D.DL      = T.DL

                       

                      - Pavan Kumar N

                      • 8. Re: Update small table with slow subselect
                        oralicious

                        Igor Laguardia-Oracle wrote:

                         

                        Would you bet your monthly salary in a trigger, or in a 7min runtime view that is used in the update? Think that clearly you got my point.

                         

                        I wouldnt gamble it is or it isnt until I seen the trace.  then I would know what Im gambling on.

                        • 9. Re: Update small table with slow subselect
                          Andrew Sayer

                          From the look of the plan you are calculating the results of the view for each row in small table. Operation 4 in the plan seems to suggest that there is some sort of ordering inside the view which is preventing the view from being merged which may help your plan.

                           

                          Could you post the definition of the view?

                           

                          To prevent the view from being executed for each row you could do something like:

                           

                          create table slow_view_t
                          as 
                          select monat
                                ,dl
                            ,fcr7*100 fc7
                          from  slow_view;
                          create unique index slow_view_t_idx on slow_view_t (monat, dl);
                          UPDATE (SELECT t.fcr7 old_value
                                        ,d.fcr7 new_value
                                  from  SMALL_TABLE T
                            JOIN  slow_view_t_idx d
                              on  D.MONAT = T.MONAT
                            and  D.DL      = T.DL
                                )
                          SET old_value = new_value;
                          

                          But this could cause inconsistent results if the data that slow_view uses is updated between the create table starting and the update.

                           

                          There also may be a much better optimization that could be found if we looked at the definition of the view.

                          1 person found this helpful
                          • 10. Re: Update small table with slow subselect
                            854418

                            I did not find an answer to my question: Is there a more elegant way for this update? Optimizer Hint?

                             

                            I cant optimize the SLOW_VIEW. This is a complex view, based on other views with big tables.

                             

                            So the only question was to avoid the this view is queried 84 times.

                             

                            My solution is now to use PL/SQL:

                            FOR D IN

                            (SELECT FCR7, DL, MONAT FROM SLOW_VIEW

                            )

                            LOOP

                              UPDATE SMALL_TABLE T

                              SET FCR7      = D.FCR7*100

                              WHERE D.MONAT = T.MONAT

                            AND D.DL      = T.DL ;

                            END LOOP;

                             

                            The Update in my original Post took about 8 hours, now it is about 8 minutes.

                             

                            Joachim

                            • 11. Re: Update small table with slow subselect
                              JohnWatson2

                              Your PL/SQL technique is not equivalent to the original UPDATE statement. The UPDATE will update every row in SMALL_TABLE, setting FCR7 to the calculated value or to NULL if there is no match in the view. Your PL/SQL will not update any rows where there is no match.

                               

                              So one or the other is a bug waiting to bite

                              • 12. Re: Update small table with slow subselect
                                SomeoneElse

                                Maybe a simple merge would be better since you won't be updating non-matched rows (as John Watson suggested).

                                 

                                merge into small_table t

                                using slow_view d

                                on   (d.monat = t.monat and d.dl = t.dl)

                                when matched then update set t.fcr7 = d.fcr7 * 100;

                                • 13. Re: Update small table with slow subselect
                                  Jonathan Lewis

                                  A quick and dirty to do what you're currently doing, but in pure SQL with a single generation of the view data:

                                   

                                  UPDATE

                                    SMALL_TABLE T

                                  SET FCR7=

                                    (

                                       with D as (

                                       SELECT /*+ materialize */

                                              FCR7, MONAT, DL

                                       FROM SLOW_VIEW

                                       )

                                    select fcr7

                                    from D

                                    WHERE D.MONAT = T.MONAT

                                    AND D.DL      = T.DL

                                    )

                                  ;


                                  Your plan should then show TEMP TABLE TRANSFORMATION as the slow view result is copied into a global temporary table, and the last two lines of the plan should show something like:


                                      VIEW

                                         TABLE ACCESS FULL    SYS_TEMP_xxxxxxxxxxx


                                  After the view has been instantiated the resulting 63 row table will be scanned 84 times - which shouldn't take very long.


                                  Regards

                                  Jonathan Lewis

                                  http://jonathanlewis.wordpress.com




                                  UPDATE:  Added in the two columns which I'd originally missed from the factored subquery.


                                  1 person found this helpful
                                  • 14. Re: Update small table with slow subselect
                                    Vidar Eidissen

                                    The problem seems to be that you are running the slow subquery one time for each row in the small table. I if you do the math 74 rows x 7 mins, you end up at about 9 hours. Remove som time due to caching effect and you´ld probably end up somewhere in the range you mention.

                                     

                                    I suggest you rewrite the query to something where you incorporate the ids from the small table, maybe by using subquery factoring:

                                    with slow_view_data as

                                    (SELECT

                                        FCR7*100 new_value, D.MONAT, D.DL

                                      FROM SLOW_VIEW D

                                      WHERE (D.DL, D.MONAT) in (select DL, MONAT from SMALL_TABLE st)

                                      )

                                    UPDATE

                                      SMALL_TABLE T

                                    SET FCR7=

                                      (SELECT

                                        new_value

                                      FROM SLOW_VIEW_data D

                                      WHERE D.MONAT = T.MONAT

                                      AND D.DL      = T.DL

                                      )

                                    ;

                                     

                                    I haven´t tested this code, just showing you the outline here. The subquery, slow_view_data will be run only once an can be used as a regular table in the following statement. Let me know if it works or anything is unclear, and I´ll set up a tested example.

                                    1 2 Previous Next