Forum Stats

  • 3,838,735 Users
  • 2,262,395 Discussions
  • 7,900,743 Comments

Discussions

Strange SQL Performance on different times

Hesh
Hesh Member Posts: 635 Bronze Badge

Hi,

I have an ETL process which runs more than 2 hrs 20 min if executed between 5 am and 7 am.

But what we find surprisingly the same ETL batch process executes in 1 hr. 30 min on a different time other than 5 am to 7 am.

DBA team and Linux team confirmed that there are no other jobs running between 5 am and 7 am.

When I take a specific job to analyze what is going on, which was running fine on the odd timing it took 320 seconds where on regular it took 844 seconds...following is what I got from ASH.



Can someone please guide me what should I look into further...

Regards

Hesh

Tagged:

Answers

  • Luís Gustavo Lira
    Luís Gustavo Lira Member Posts: 119 Silver Badge

    Hi, did you check if this executions are using the same SQL plan hash value ?

  • Hesh
    Hesh Member Posts: 635 Bronze Badge

    Hi Luis,

    Thanks for your response.. Yes both the plan hash values are the same.. I have verified ASH

    Regards

    Hesh

  • Luís Gustavo Lira
    Luís Gustavo Lira Member Posts: 119 Silver Badge

    In this case I would start by generating AWR Compare Period Report to try to identify relevant differences in the environment as a whole.

    Check mainly in the Top Timed Events, what is consuming significant percentage of DB time and if there are big differences between the periods.

    You can also:

    - generate an AWR SQL Report for this specific SQL for each execution period.

    - run ASH Report for this SQL to both periods

    - trace the session that are running the query to collecting SQL Execution information

    - run a SQL Tuning Advisor for this query to check if there is any recomendation

  • Hesh
    Hesh Member Posts: 635 Bronze Badge

    Thanks Luis,

    In my initial post I have posted the ASH comparison for both the dates.. I am mentioning here again, direct path reads are the difference.

    What should I look into more from here? any specific information from AWR that can help me?




    Regards

    Hesh

  • Hesh
    Hesh Member Posts: 635 Bronze Badge

    From AWR I got user I/O is different ... we are reading a huge table...



    Regards

    Hesh

  • Luís Gustavo Lira
    Luís Gustavo Lira Member Posts: 119 Silver Badge

    During heavy batch periods, waits on direct path read are quite normal, but compare average wait time from both periods for direct path read to check if this indicates any issue related to I/O Performance Problems.

    If possible, place the AWR Top Timed Events here as well, it can be the compare period or one of each period to check for more details and if you have other related waits.

    Also check in ADDM report if there are any IO issue related. 

  • Hesh
    Hesh Member Posts: 635 Bronze Badge

    Thanks Luis,

    As that is a heavy job we schedule that job to execute alone. When the above mentioned Insert SQL runs, there is no other SQL runs, means no other SQL executions are going on. I even verified in ASH if there are any other SQL IDs, but nothing there.

    Regards

    Hesh

  • Hesh
    Hesh Member Posts: 635 Bronze Badge

    Hi,

    Any more inputs I can get to dig more into this please? like any doc or any blogs to understand more about User I/O, PGA and 'direct path reads' ... unfortunately I am getting nowhere with this. Appreciate your help on this.


    Regards

    Hesh

  • Sridhar_Nalla
    Sridhar_Nalla Member Posts: 8 Blue Ribbon

    Did you check the ETL server load between 5AM and 7AM. Either load on the ETL Server or network (5AM-7AM) causing the delay in the batch execution between 5AM and 7AM.