Re: ETL Query Performance Bad Only on Sundays

Jonathan,

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

Thanks!

Alfredo

On Wed, Feb 21, 2018 at 10:54 AM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> 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: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Alfredo Abate <alfredo.abate_at_gmail.com>
> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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