Forum Stats

  • 3,728,027 Users
  • 2,245,521 Discussions
  • 7,853,250 Comments

Discussions

What is the possible cause that the same SQL sometimes run very fast and sometimes run very slow in

user7587483
user7587483 Member Posts: 1
edited December 2013 in SQL & PL/SQL

Our application is using the oracle as the database, and when running the batch process, it is find that for the same SQL, in most of the times, it can be completed within 2 minutes, but sometimes (once a week or once a month, and it occurs randomly), the SQL take around 30 minutes to be completed. Do anyone know what is the possible cause of this? Will it be the problem of the Oracle?

Answers

  • Hoek
    Hoek Member Posts: 16,076 Gold Crown

    Database version?

    Is your batch process a scheduled job with a fixed interval?

    Are there occasionally users perhaps locking tables your batch process uses?

    You should ask your DBA to investigate this using statspack or AWR, check what the various reports are telling you/him/her.

    Also, search http://asktom.oracle.com and this forum for more pointers...

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    could be a problem with two (or more) plans for the query that are used at different times. You could take a look at Kerry Osborne's script unstable_plans.sql, mentioned here: http://kerryosborne.oracle-guy.com/2008/10/unstable-plans/. Or write your own query on the corresponding AWR views (DBA_HIST_SQLSTAT, DBA_HIST_SNAPSHOT) - as long as you have the licence to use AWR (I think that's the diagnostic pack). If you don't have the licence you could check v$sql after the different performing executions and look for different PLAN_HASH_VALUEs (and of course then check the corresponding plans).

  • Natik Ameen
    Natik Ameen Member Posts: 12
    edited December 2013

    If I were in this position I would proceed in the following fashion. Compare the following between the time when the SQL runs as expected and the times when the SQL is much slower.

    1. OS stats

    2. SQL Plan

    3. Wait events in AWR reports

    4. Plan changes recorded in DBA_HIST_SQLSTAT table.

    4. Check if parallelism is degraded recorded in DBA_HIST_SYSSTAT (WHERE STAT_NAME ='Parallel operations downgraded to serial'))

    5. Use SQLT for specific SQL_ID (Doc ID 1454160.1)

    Let me know if I can help you further and we can post back the findings here if you like. Here are some articles on database and SQL profiling which can help - http://www.vitalsofttech.com/using-dynamic-performance-views/

    Thanks.

    - Natik Ameen

  • EdStevens
    EdStevens Member Posts: 28,034 Gold Crown
    Natik Ameen wrote:
    
    If I were in this position I would proceed in the following fashion. Compare the following between the time when the SQL runs as expected and the times when the SQL is much slower.
    
    1. OS stats
    2. SQL Plan
    3. Wait events in AWR reports
    4. Plan changes recorded in DBA_HIST_SQLSTAT table.
    4. Check if parallelism is degraded recorded in DBA_HIST_SYSSTAT (WHERE STAT_NAME ='Parallel operations downgraded to serial'))
    5. Use SQLT for specific SQL_ID (Doc ID 1454160.1)
    
    Let me know if I can help you further at <redacted> and we can post back the findings here if you like. Here are some articles on database and SQL profiling which can help - <redacted>
    
    Thanks.
    
    - Natik Ameen
    

    You should never post your email in a public forum unless you really enjoy having your mailbox flooded with offers for certain, ah, anatomical enhancement products and offer to get a large sum of money by helping launder money out of Nigeria.

    Your offer of help with email and web site really borders on abuse via spam. 

This discussion has been closed.