Re: Very unstable execution plan
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Jun 2013 09:08:09 +0200
Jonathan Lewis, 07.06.2013 20:18:
> Any progress ?
> I'm still curious about the german index problem.
I did re-send my answer, but apparently my email again didn't make it to the
list.
Seems there *is* some kind of size restriction on this list.
So here it is again, I uploaded all plans to my personal web-site in order to
reduce the size of the email.
> As far as stats go - I've often seen Oracle do better without than with; but
> more specifically
> I've seen it to better if you get rid of histograms.
> How are you collecing stats on the critical table, and what do the stats look
> like ?
They are gathered by the default Oracle job, nothing was configured there.
During my tests I recreate them using:
exec dbms_stats.gather_table_stats(ownname => 'DWH_ANALYSE_FEED_2_8_0',
tabname => 'TMP_VU_SPARTE',
cascade => true,
no_invalidate => false);
The stored execution plan when no statistics are available is here:
http://www.sql-workbench.net/dbms_xplan_no_stats.txt
The stored execution plan when statistics are collected (using the above call
to dbms_stats) is here: http://www.sql-workbench.net/dbms_xplan_with_stats.txt
The plan shown by explain without statistics:
http://www.sql-workbench.net/explain_no_stats.txt
The plan shown by explain _whith_ statistics:
http://www.sql-workbench.net/explain_with_stats.txt
The plan shown by explain with statistics and nls_sort set to german:
http://www.sql-workbench.net/explain_with_german.txt
This is a shortened version of the view definition, but it should contain all
essential parts:
create or replace view cxv_haupt_vu_sparte
select ...
from (
select ....
FROM (
SELECT ....
FROM (
SELECT pi.id AS pop_id,
pi.extrakt_ts AS pop_id_extrakt_ts,
greatest(vu.erstellt_am,vu2.erstellt_am) AS erstellt_am,
least(vu.ersetzt_am,vu2.ersetzt_am) AS ersetzt_am,
vu.vertrag_ext_Ref,
vu.deklaration_gueltig_von,
vu.deklaration_gueltig_bis,
vu.vu_sparte_Schluessel AS dkopf_vu_sparte_schluessel,
vu.vu_sparte_name AS dkopf_vu_sparte_name,
vu2.vu_sparte_Schluessel AS vu_sparte_schluessel,
vu2.vu_sparte_name AS vu_sparte_name,
vu.deklaration_ext_Ref AS vu_deklaration_ext_ref,
vu2.deklaration_ext_Ref AS vu2_deklaration_ext_ref
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
INNER JOIN dwh_core.pop_info pi
ON least (vu.ersetzt_am,vu2.ersetzt_am) > pi.extrakt_ts
AND greatest (vu.erstellt_am,vu2.erstellt_am) <= pi.extrakt_ts
) haupt_vu
)
)
GROUP BY pop_id,
deklaration_ext_ref,
deklaration_gueltig_von,
deklaration_gueltig_bis,
vertrag_ext_ref,
haupt_vu_sparte_Schluessel,
haupt_vu_Sparte_name;
I shortened the select lists a bit, some of them contain function calls to self
written functions and
some window functions to aggregate some data but as far as I can tell they
don't influence the plan at all.
As noted before, the self join on tmp_vu_sparte and the join to pop_info will
always result in a FTS
when simply used by its own or if the view is queried without any further
restrictions. As part of
the UPDATE statement this apparently enables the use of an index.
> How much change are you allowed to make to the problem query to stabilise it?
We have full control over it. We are actually checking if we can re-structure
that view completely in order to improve things.
> If nothing else, putting in a couple of hints in the outer select addressing
> the content of the view and telling Oracle
> to do dynamic sampling (level 2 may be sufficient) on the two copies of
> TMP_VU_SPARTE may be enough to sort the problem.
Interesting idea, although it feels like a hack to me. I will have a look at it.
> You'll probably want to get the outline dumped from a call to
> dbms_xplan.display_cursor() to get the correct query block names and aliases
> first.
> I've written up a blog note emulating the problem:
> http://jonathanlewis.wordpress.com/2013/06/07/same-plan/
Thanks, I will go through it.
Regards and thanks for the help.
Thomas
--
//www.freelists.org/webpage/oracle-l
Other related posts:
