Re: Very unstable execution plan

From: Rajiv Iyer <raju.rgi_at_gmail.com>
Date: Wed, 5 Jun 2013 17:24:53 +0530
Message-ID: <CADxvSwM-OgO6AK5P6A=nqc6h-xmAZZkae9m2-wE_4vaKJrazHQ_at_mail.gmail.com>

Ok this is interesting. As Jonathan suggested, the order of joining to the two copies of the table has changed.
Step 14 in predicate clause of plan without stats, accesses VU alias :   14 - access("VU"."VERTRAG_EXT_REF"=:B1 AND "VU"."DEKLARATION_GUELTIG_VON"=:B2 AND "VU"."DEKLARATION_GUELTIG_BIS"=:B3)        filter(("VU"."DEKLARATION_GUELTIG_VON"=:B1 AND "VU"."DEKLARATION_GUELTIG_BIS"=:B2))
Step 14 in predicate clause of plan without stats accesses VU2 alias:   14 - access("VU2"."VERTRAG_EXT_REF"=:B1 AND "VU2"."DEKLARATION_GUELTIG_BIS">:B2 AND
"VU2"."DEKLARATION_GUELTIG_VON"<=:B3)

       filter(("VU2"."DEKLARATION_GUELTIG_VON"<=:B1 AND "VU2"."DEKLARATION_GUELTIG_BIS">:B2)) On Wed, Jun 5, 2013 at 4:51 PM, Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>wrote:

> Rajiv Iyer, 05.06.2013 11:34:
> > Can you please share the predicate section for both the cases (with and
> without stats gathered)?
> > It looks like for one of the conditions for table TMP_VU_SPARTE, index
> IDX_TMP_VU_SPARTE may not be suitable.
> >There are far more rows being processed through this index as compared to
> the final rows returned by the table.
> > Based on the filtering conditions you may require a different index on
> that table.
>
> I have attached both plans as retrieved using
>
> SELECT *
> FROM table(dbms_xplan.display_cursor('202urtp1qwkpa', null, 'BYTES
> COST NOTE ROWS ALLSTATS PREDICATE '));
>
> after running the statement twice with and without statistics on the table.
>
> I have also attached the full output of EXPLAIN PLAN for both situations.
>
> This is the index definition:
>
> CREATE INDEX IDX_TMP_VU_SPARTE
> ON TMP_VU_SPARTE (VERTRAG_EXT_REF ASC, ERSETZT_AM ASC, ERSTELLT_AM
> ASC, DEKLARATION_GUELTIG_VON ASC, DEKLARATION_GUELTIG_BIS ASC);
>
> The table itself is defined like this:
>
> CREATE TABLE TMP_VU_SPARTE
> (
> VERTRAG_EXT_REF VARCHAR2(255 Byte),
> VU_SPARTE_SCHLUESSEL VARCHAR2(100 Byte),
> VU_SPARTE_NAME VARCHAR2(255 Byte),
> DEKLARATION_GUELTIG_VON DATE NOT NULL,
> DEKLARATION_GUELTIG_BIS DATE NOT NULL,
> ERSTELLT_AM TIMESTAMP(6),
> ERSETZT_AM TIMESTAMP(6),
> DEKLARATION_ID NUMBER(18) NOT NULL,
> DEKLARATION_EXT_REF VARCHAR2(255 Byte) NOT NULL
> )
>
>
> The actual join in the view is something like this:
>
> SELECT ....
> FROM tmp_vu_sparte vu
> INNER JOIN tmp_vu_sparte vu2
> ON vu.vertrag_ext_ref = vu2.vertrag_ext_ref
> AND vu2.deklaration_gueltig_von <= vu.deklaration_gueltig_von
> AND vu2.deklaration_gueltig_bis > vu.deklaration_gueltig_von
> AND vu2.erstellt_am < vu.ersetzt_am
> AND vu2.ersetzt_am > vu.erstellt_am
>
> Note that this part on itself will *always* use a FTS on the table (which
> is understandable) - the index is only used when the view is accessed as
> part of the co-related update.
>
>
> Regards
> Thomas
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 05 2013 - 13:54:53 CEST