Nothing to add to Sayan's suggestion - but I can't help wondering whether this
is a case where "up to date" statisics cause problems.
Maybe during the week the dates that people query for are so far outside the
know high value (i.e. a couple of days) that Oracle manages to massively
underestimate a critical cardinality and produce a good plan; then on Friday
night you bring the stats up to date and the queries that hit the system for a
few hours are either inside the high value, or sufficiently close to the high
value that they produce much higher cardinality estimates that produce
inefficient execution plans.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Alfredo Abate <alfredo.abate@xxxxxxxxx>
Sent: 21 February 2018 16:11
To: oracle-l-freelist
Subject: ETL Query Performance Bad Only on Sundays
Hello,
We have a query as part of our daily ETL coming from OBIEE (Informatica) that
pulls from 10 E-Business Suite tables as it's source. Monday - Saturday the
query executes at what we deem as an acceptable time. Lately we've noticed
that on Sundays the execution time degrades significantly. The query is using
hard coded values (no bind variables) for the last update date so it knows
where to start from on each subsequent run. Since the hard coded values are
generating unique SQL IDs it's making it difficult to implement something like
a SQL Profile. Since this is EBS, cursor sharing has to be kept at a value of
EXACT and we also can't use SQL Plan Baselines. Our BI analyst is looking into
if there is a way to pass bind variables. In the meantime I'm trying to
determine what else we can do. Our EBS database version is 11.2.0.3.
Additional Info:
We run the EBS Gather Schema Statistics weekly starting on Fridays on all
schemas/tables at 100% sample size and also use the option "Invalidate
Dependent Cursors". My assumption at this point is that this cursor
invalidation is occurring on Saturday for the tables involved and by the time
the ETL query kicks off again on early Sunday it is being impacted by this.
Monday - Saturday: SQL IDs are different but the SQL Plan Hash Values are the
same.
Sundays: SQL IDs are different but the SQL Plan Hash Value (i.e the "bad" plan)
is different than Monday - Saturday but consistently the same as other Sundays!
I'm still collecting some additional information but thought I would get this
on the list to see if any one has any additional thoughts or advice.
Thanks!
Alfredo
--
//www.freelists.org/webpage/oracle-l