RE: Very unstable execution plan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 5 Jun 2013 08:53:12 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D48B1_at_exmbx06.thus.corp>

The answer to the first part of your posting is just so cute I think I'll have to emulate it and write a blog about it (though it might be nice to see the plan, including predicate section, as pulled from memory by dbms_xplan). The plan is "the same" - but the later steps involve nested loop joins into tmp_vu_sparte by index idx_tmp_sparte - I'll bet the order of joining to those two copies have changed, so the predicates involved are different, which is why the numbers of rows (hence CR gets, hence CPU time) is so different.

For the second part, check the predicate section of the plan for implicit conversions before you do anything else.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Thomas Kellerer [thomas.kellerer_at_mgm-tp.com] Sent: 05 June 2013 08:24
To: oracle-l_at_freelists.org
Subject: Very unstable execution plan

Hi,

we have an UPDATE statement that is run every night during the process of populating a test environment for our DWH. This is an automated build environment and therefor the tables are dropped and re-populated every night.

There is one UPDATE statement that is based on a largish view that shows a very erratic behaviour in terms of it's execution time.

cxv_haupt_vu_sparte is a view that incorporates several other tables, including TMP_VU_SPARTE which is the culprit for the bad behaviour. TMP_VU_SPARTE is rather small with only 106332 rows (inside the view it is joined against itself though which - if no index is used - will yield quite a big temporary result)

     126457     126457     126457        NESTED LOOPS  (cr=2038167 pr=1669 pw=0 time=26342292 us)
     126457     126457     126457         NESTED LOOPS  (cr=1995241 pr=1615 pw=0 time=26192173 us cost=7 size=853 card=1)
     141581     141581     141581          NESTED LOOPS  (cr=642683 pr=1066 pw=0 time=3039331 us cost=5 size=499 card=1)
      11225      11225      11225           TABLE ACCESS BY INDEX ROWID POP_INFO (cr=22473 pr=32 pw=0 time=109767 us cost=2 size=16 card=1)
      11225      11225      11225            INDEX UNIQUE SCAN PK_POP_INFO (cr=11248 pr=4 pw=0 time=51796 us cost=1 size=0 card=1)(object id 1790009)
     141581     141581     141581           TABLE ACCESS BY INDEX ROWID TMP_VU_SPARTE (cr=620210 pr=1034 pw=0 time=2889850 us cost=3 size=483 card=1)
    1732978    1732978    1732978            INDEX RANGE SCAN IDX_TMP_VU_SPARTE (cr=140952 pr=204 pw=0 time=2094982 us cost=2 size=0 card=1)(object id 1795724)
     126457     126457     126457          INDEX RANGE SCAN IDX_TMP_VU_SPARTE (cr=1352558 pr=549 pw=0 time=23078816 us cost=1 size=0 card=1)(object id 1795724)
     126457     126457     126457         TABLE ACCESS BY INDEX ROWID TMP_VU_SPARTE (cr=42926 pr=54 pw=0 time=94791 us cost=2 size=354 card=1)--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 05 2013 - 10:53:12 CEST