1 2 Previous Next 16 Replies Latest reply on Dec 30, 2015 5:24 PM by Jonathan Lewis Go to original post
      • 15. Re: Re: Update small table with slow subselect
        854418

        Hello Jonathan,

         

        i have tried it, but the plan shows the same high cost as in the original update.

         

        UPDATE

        SMALL_TABLE t

        SET fcr7=

          ( WITH d AS

          (SELECT /*+ materialize */

            fcr7, dl, monat FROM SLOW_VIEW

          )

        SELECT fcr7*100 FROM d WHERE d.monat = t.monat AND d.dl = t.dl

          ) ;

         

         

        First lines of plan (parallel disabled):

        | Id  | Operation                   
        | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time| Pstart| Pstop |
        |   0 | UPDATE STATEMENT           
        |                         |84 |  1848 |   |   221M  (1)| 10:25:47 |   |   |
        |   1 |  UPDATE                    
        | SMALL_TABLE             |   |   |   |        |      |   |   |
        |   2 |   TABLE ACCESS FULL        
        | SMALL_TABLE             |84 |  1848 |   | 3   (0)| 00:00:01 |   |   |
        |   3 |   TEMP TABLE TRANSFORMATION|   |   |   |        |      |   |   |
        |   4 |    LOAD AS SELECT           
        | SYS_TEMP_0FD9DE01A_38BA523F |   |   |   |        |      |   |   |
        |   5 |     VIEW                    
        | SLOW_VIEW               | 19047 |   539K|   |  2631K  (1)| 00:07:27 |   |   |

         

        The inner Select is still executed 84 times.

         

        Greetings,

        Joachim

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

          Joachim,

           

          Have you tried executing the statement, or is your comment based on using your version of Explain Plan ?

          I would be surprised if the view is recreated 84 times given the TEMP TABLE TRANSFORMATION and the fact that the optimizer is usually quite smart.

          Here's a query run, with rowsource execution stats enabled, that shows the effect:

           

          First an update that doesn't use the subquery factoring method:

          update t3 set t2n2 = (select v1.t2n2 from v1 where v1.t1n1 = t3.t1n1

          and v1.t1n2 = t3.t1n2)

           

           

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

          | Id  | Operation                                | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

          |   0 | UPDATE STATEMENT                         |       |      1 |        |      0 |00:00:01.22 |   46745 |      1 |       |       |          |

          |   1 |  UPDATE                                  | T3    |      1 |        |      0 |00:00:01.22 |   46745 |      1 |       |       |          |

          |   2 |   TABLE ACCESS FULL                      | T3    |      1 |    200 |    200 |00:00:00.01 |       3 |      1 |       |       |          |

          |   3 |   VIEW                                   | V1    |    200 |      1 |    200 |00:00:01.22 |   46332 |      0 |       |       |          |

          |   4 |    SORT UNIQUE                           |       |    200 |      1 |    200 |00:00:01.21 |   46332 |      0 |  2048 |  2048 | 2048  (0)|

          |   5 |     NESTED LOOPS                         |       |    200 |      1 |  45000 |00:00:01.11 |   46332 |      0 |       |       |          |

          |   6 |      NESTED LOOPS                        |       |    200 |      1 |  45000 |00:00:00.34 |    1332 |      0 |       |       |          |

          |*  7 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    200 |      1 |   3000 |00:00:00.02 |     684 |      0 |       |       |          |

          |*  8 |        INDEX RANGE SCAN                  | T1_I1 |    200 |     15 |   3000 |00:00:00.01 |     408 |      0 |       |       |          |

          |*  9 |       INDEX RANGE SCAN                   | T2_I1 |   3000 |      1 |  45000 |00:00:00.11 |     648 |      0 |       |       |          |

          |  10 |      TABLE ACCESS BY INDEX ROWID         | T2    |  45000 |      1 |  45000 |00:00:00.31 |   45000 |      0 |       |       |          |

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

           

           

           

           

           

          Then the version with subquery factoring

           

          update t3 set t2n2 = (  with v0 as (   select/*+ materialize */

          t1n1, t1n2, t2n2   from v1  )  select   t2n2  from v0  where v0.t1n1 =

          t3.t1n1  and v0.t1n2 = t3.t1n2 )

           

           

           

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

          | Id  | Operation                   | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |

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

          |   0 | UPDATE STATEMENT            |                            |      1 |        |      0 |00:00:00.20 |    1185 |      1 |      1 |       |       |          |

          |   1 |  UPDATE                     | T3                         |      1 |        |      0 |00:00:00.20 |    1185 |      1 |      1 |       |       |          |

          |   2 |   TABLE ACCESS FULL         | T3                         |      1 |    200 |    200 |00:00:00.01 |       3 |      0 |      0 |       |       |          |

          |   3 |   TEMP TABLE TRANSFORMATION |                            |    200 |        |    200 |00:00:00.19 |     778 |      1 |      1 |       |       |          |

          |   4 |    LOAD AS SELECT           |                            |      1 |        |      0 |00:00:00.02 |     171 |      0 |      1 |  1040K|  1040K|          |

          |   5 |     VIEW                    | V1                         |      1 |  45000 |    200 |00:00:00.01 |     168 |      0 |      0 |       |       |          |

          |   6 |      HASH UNIQUE            |                            |      1 |  45000 |    200 |00:00:00.01 |     168 |      0 |      0 |  1558K|  1558K| 3032K (0)|

          |*  7 |       HASH JOIN             |                            |      1 |  45000 |  45000 |00:00:00.01 |     168 |      0 |      0 |  1969K|  1969K| 1713K (0)|

          |   8 |        TABLE ACCESS FULL    | T1                         |      1 |   3000 |   3000 |00:00:00.01 |      84 |      0 |      0 |       |       |          |

          |   9 |        TABLE ACCESS FULL    | T2                         |      1 |   3000 |   3000 |00:00:00.01 |      84 |      0 |      0 |       |       |          |

          |* 10 |    VIEW                     |                            |    200 |  45000 |    200 |00:00:00.17 |     603 |      1 |      0 |       |       |          |

          |  11 |     TABLE ACCESS FULL       | SYS_TEMP_0FD9D6609_90D2B09 |    200 |  45000 |  40000 |00:00:00.09 |     603 |      1 |      0 |       |       |          |

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

           

           

           

           

           

          Note that the TEMP TABLE TRANSFORMATION line is reported at starting 200 times, but the LOAD AS SELECT starts only once. This is you view instantiation and 7 minute run.

          The last two lines of the plan show the temporary table being scanned (63 very small rows in your case) 200 times.

           

          As a check I snapped the session stats for the query in case the optimizer was lying about the buffer visits in the tablescans of t1 and t2 - perhaps showing us the results of doing the join once when it had actually done it 200 times - but the session stats agreed with the plan: the view was instantiated just once.

           

           

           

          Regards

          Jonathan Lewis

          1 2 Previous Next