Re: ETL Query Performance Bad Only on Sundays

From: Alfredo Abate <>
Date: Tue, 27 Feb 2018 11:51:17 -0600
Message-ID: <>


That's an interesting theory you provided. Let me see if I can gather enough information to see if this might be the case.



On Wed, Feb 21, 2018 at 10:54 AM, Jonathan Lewis <> wrote:

> 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: <> on
> behalf of Alfredo Abate <>
> 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
> 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
> --

Received on Tue Feb 27 2018 - 18:51:17 CET

Original text of this message