ETL Query Performance Bad Only on Sundays

From: Alfredo Abate <>
Date: Wed, 21 Feb 2018 10:11:05 -0600
Message-ID: <>


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.



Received on Wed Feb 21 2018 - 17:11:05 CET

Original text of this message