Re: ETL Query Performance Bad Only on Sundays

Hi Mohamed,

The SQL profile is not being used and is not observed at the end of the execution plan. I observed that the SQL ID was different each day, the SQL PLAN HASH VALUE was sometimes different and the FORCE MATCHING SIGNATURE was not always the same.

We ended up having to re-write the query because while it ran 25 - 50 minutes on good days, it ran for upwards of 8 hours on our most recent bad day with an unprecedented number of records (1.5 million). This particular day the SQL was also ending with an unprecedented ORA-01555 error.

Since the query was re-written it runs much faster now and consistently around 10 minutes. We were also able to process the 1.5 million without any issues. Unfortunately, we did observe that once again the query ran long again last Sunday. So it's back to the drawing board on the Sunday issue. Below are the SQL IDs, PLAN HASH VALUES, and FORCE MATCHING SIGNATURE for the latest re-written query so you can seem what I'm describing.

We have been instructed to work on this further for now until the next fire takes precedence. :) Hoping we can collect better data to understand what's going on here.

Appreciate all your help!

Thanks,

Alfredo

SQL_ID SQL_PLAN_HASH_VALUE FORCE_MATCHING_SIGNATURE RUN_DATE

------------- ------------------- ------------------------- ---------------
3g63tm1k566v0          3668602406       4440774388075220172 02-MAR-18
gt86u1xaam7z7           301166263       1538686123961058741 03-MAR-18
7prhgy4vw7d5s          4023966549      16018329654708048986 03-MAR-18
7f4qr1wyxuugh          3618744509       2535530000544872714 04-MAR-18
a2qw88cmnjzky          4023966549      15639595515267364172 05-MAR-18
6q0zpkj0fwa00           836944587      15967387503128582473 06-MAR-18
a2qw88cmnjzky          3668602406      15639595515267364172 06-MAR-18
8p2pm50z9j6gu           836944587      18004139757617944414 07-MAR-18
c4r9wt3k1d422          4023966549      11054838893286711406 07-MAR-18
259t91dchv9w3          4023966549      12903350575977829138 07-MAR-18
87sdpzy7vz6fg           836944587       6762615563733337087 08-MAR-18
c4r9wt3k1d422          3668602406      11054838893286711406 08-MAR-18

On Tue, Feb 27, 2018 at 2:35 PM, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

> Alfredo
>
> When you say it is not working does this mean that you observed that the
> SQL profile is mentioned by the Note at the bottom of the execution plan to
> be used but the plan_hash_value of your execution plan is not the one that
> should normally be produced by the SQL Profile? Or there is no Note about
> the SQL profile at the end of the execution plan?
>
> Bear in mind as well that if your query contains in IN-LIST elements and
> that the number of elements in this LIST changes from execution to
> execution then the force_match of the SQL Profile will not produce the same
> force matching signature
>
> Best Regards
>
> Mohamed Houri
>
>
> 2018-02-27 18:51 GMT+01:00 Alfredo Abate <alfredo.abate_at_gmail.com>:
>
>> 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
>>>
>>>
>>>
>>
>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Member of Oraworld-team <http://www.oraworld-team.com/>
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>
>

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