Re: Very unstable execution plan
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 05 Jun 2013 13:21:48 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
