This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 22, 2012 7:10 AM by Osama_Mustafa RSS

Good execution plan but poor performance (duration of the query)

fcjunic Newbie
Currently Being Moderated
Hello,

I have recently noticed that a query that exists for some years has just went down in performance (Before 0.5sec ; Now 20 sec).
The only change I made is to gather statistics on a specific index (and on the table of that index) used in the query.
(Note : I gathered new statistics not in the context of improving this specific query but for another purpose)

The new execution plan of the query is pretty good (Cost : 25) and all the where clauses are using index. But as I said, the query is very slow now...
Do I miss something ? Do I have to do extra operations ? What can I do to get back a quick response for the query ?

Thanks !
  • 1. Re: Good execution plan but poor performance (duration of the query)
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Can you post more information from AWR
    DB Verion
    OS Version
  • 2. Re: Good execution plan but poor performance (duration of the query)
    Dom Brooks Guru
    Currently Being Moderated
    The new execution plan of the query is pretty good (Cost : 25)
    Cost is but an estimate (and one which should probably be ignored).

    Most importantly - are the row estimates throughout the plan accurate.

    Do you have any information from when the query was good?
    Does your AWR retention extend back to a good period (see DBA_HIST_SQLSTAT if licensed for AWR)?

    Approach as per sql tuning thread:
    [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request
  • 3. Re: Good execution plan but poor performance (duration of the query)
    fcjunic Newbie
    Currently Being Moderated
    DB version : 10.1.0.5.0
    OS Version : HP-UX Itanium B.11.23 ia64

    The query was good before I gather statistics on the index and the table (i.e yesterday).

    AWR information     :

    General_
    Snapshot Retention (days)          7
    Snapshot Interval (minutes)          60
    Collection Level          TYPICAL
    Next Snapshot Capture Time          Nov 22, 2012 12:00:46 PM
         
    Manage Snapshots and Preserved Snapshot Sets_
    Snapshots          182
    Preserved Snapshot Sets          0
    Latest Snapshot Time          Nov 22, 2012 11:00:46 AM
    Earliest Snapshot Time          Nov 14, 2012 10:00:43 PM


    What do I have the query in DBA_HIST_SQLSTAT ?

    Thanks !
  • 4. Re: Good execution plan but poor performance (duration of the query)
    Antonio Navarro Pro
    Currently Being Moderated
    First of all, sql statments is a live entity.

    Have You execution plan changed?, are you sure before and now it is executing the same sql plan hash value?

    If yes, plan is the same, maybe more data, change parameter, etc. in this case you can use dbms_sqltune for try a new better plan.

    If no, plan hava changed, force to all plan (maybe outlines)

    HTH
    Antonio NAVARRO.
  • 5. Re: Good execution plan but poor performance (duration of the query)
    fcjunic Newbie
    Currently Being Moderated
    I don't know if the execution plan is the same that before because I don't know the old execution plan.
    I assume they are different ! There are no more data in the table...
  • 6. Re: Good execution plan but poor performance (duration of the query)
    Dom Brooks Guru
    Currently Being Moderated
    The query was good before I gather statistics on the index and the table (i.e yesterday).
    You can pull plan differences easily from AWR using DBMS_XPLAN.DISPLAY_AWR, e.g.
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('<your sql id>'));
    What do I have the query in DBA_HIST_SQLSTAT ?
    Here is one example query I use for looking at executions by day, by plan.
    Some of these columns may not be in 10.1.
    select trunc(sn.end_interval_time) dt
    ,      st.sql_id
    ,      st.plan_hash_value
    --,      st.instance_number inst
    ,      sum(st.fetches_delta) fch
    ,      sum(rows_processed_delta) rws
    ,      sum(executions_delta)     execs
    ,      round(sum(elapsed_time_delta)/1000/1000)   elp
    ,      round(sum(cpu_time_delta)/1000/1000)       cpu
    ,      sum(buffer_gets_delta)    gets
    ,      round(sum(iowait_delta)/1000/1000)         io
    ,      round(sum(clwait_delta)/1000/1000)         cl
    ,      round(sum(disk_reads_delta))         p_reads
    ,      round(sum(direct_writes_delta))        p_writes
    from   dba_hist_snapshot sn
    ,      dba_hist_sqlstat  st
    where  st.snap_id            = sn.snap_id
    and    sn.instance_number = st.instance_number
    and    st.sql_id             = '<your sql id>'
    group by trunc(sn.end_interval_time), st.sql_id, st.plan_hash_value--, st.instance_number
    order by trunc(sn.end_interval_time) desc; 
  • 7. Re: Good execution plan but poor performance (duration of the query)
    fcjunic Newbie
    Currently Being Moderated
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('<your sql id>'));
    I already know the execution plan of the query (but I can't get the old execution plan)

    By the way, if we only check the execution plan we will not answer to my issue because the current execution plan seems good !

    Thanks for the help but I still can not solve my problem
  • 8. Re: Good execution plan but poor performance (duration of the query)
    Dom Brooks Guru
    Currently Being Moderated
    I've given you information about how to see if the old and new plans are in AWR on the basis that if they are then you can see what the changes in execution plan were.

    If they're not there, then fine - AWR only captures the top N statements, significant at a database level.
    I assume you have checked rather than have simply dismissed this as irrelevant.
    the current execution plan seems good !
    It's a fair assumption that the current execution plan is not good.

    Have you even read the tuning thread yet?
    HOW TO: Post a SQL statement tuning request - template posting

    Execution plans are based on estimates.
    If the estimates are significantly inaccurate, then there's a good chance the plan is suboptimal.

    Follow the instructions in the tuning thread about how to validate estimates vs actuals.
  • 9. Re: Good execution plan but poor performance (duration of the query)
    fcjunic Newbie
    Currently Being Moderated
    Ok I will clarify the situation : I have no information about the old execution plan...

    The only thing I know is since I gather statistics on a specific index and its table, the duration of the query is very long !

    Can someone advise me on what I can check or improve to get back a quick time response for the query ?

    Here is the current execution plan :
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('5fuzc5hdruv23'));
    
    
    SQL_ID 5fuzc5hdruv23
    --------------------
    SELECT REF_STRUCT_RAPAAJ REF_STRUCT_RAPAAJ,TRANS_DATE TRANS_DATE,DESCRIPTN DESCRIPTN,REF_STRUCT_BULVERS 
    REF_STRUCT_BULVERS,PERIOD PERIOD,RALL RALL,TREFERENCE TREFERENCE,DEBIT DEBIT,CREDIT CREDIT,MPR MPR,MCPT 
    MCPT,AMOUNT AMOUNT,SIMP SIMP,SSV SSV,COMMENTAIRE COMMENTAIRE,FIRST_DATE FIRST_DATE  FROM V_ABIL_PPAT_3  WHERE 
    MPR = 67  AND SIMP = 276  ORDER BY FIRST_DATE, REF_STRUCT_BULVERS, TRANS_DATE,MCPT
     
    Plan hash value: 3577895524
     
    ------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | TQ/Ins |IN-OUT|
    ------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                 |       |       |    24 (100)|          |        |      |
    |   1 |  SORT AGGREGATE                        |                 |     1 |    33 |            |          |        |      |
    |   2 |   HASH JOIN                            |                 |     2 |    66 |    10   (0)| 00:00:01 |        |      |
    |   3 |    TABLE ACCESS BY INDEX ROWID         | SIMP            |     2 |    24 |     3   (0)| 00:00:01 |        |      |
    |   4 |     INDEX RANGE SCAN                   | SIMP_ACCNT_CODE |     2 |       |     1   (0)| 00:00:01 |        |      |
    |   5 |    TABLE ACCESS BY INDEX ROWID         | MCPT            |     6 |   126 |     7   (0)| 00:00:01 |        |      |
    |   6 |     INDEX RANGE SCAN                   | MCPT_REF_STRUCT |     4 |       |     3   (0)| 00:00:01 |        |      |
    |   7 |  SORT ORDER BY                         |                 |     1 |   118 |    24  (13)| 00:00:01 |        | PCWP |
    |   8 |   SORT GROUP BY                        |                 |     1 |   118 |    24  (13)| 00:00:01 |        | PCWP |
    |   9 |    NESTED LOOPS OUTER                  |                 |     1 |   118 |    22   (5)| 00:00:01 |        | PCWP |
    |  10 |     NESTED LOOPS OUTER                 |                 |     1 |   105 |    20   (5)| 00:00:01 |        | PCWP |
    |  11 |      NESTED LOOPS OUTER                |                 |     1 |    93 |    18   (6)| 00:00:01 |        | PCWP |
    |  12 |       NESTED LOOPS                     |                 |     1 |    89 |    18   (6)| 00:00:01 |        | PCWP |
    |  13 |        TABLE ACCESS BY INDEX ROWID     | SIMP            |     1 |    12 |     2   (0)| 00:00:01 |        | PCWP |
    |  14 |         INDEX UNIQUE SCAN              | SIMP_PK         |     1 |       |     1   (0)| 00:00:01 |        | PCWP |
    |  15 |        TABLE ACCESS BY INDEX ROWID     | MCPT            |     1 |    77 |    18   (6)| 00:00:01 |        | PCWP |
    |  16 |         BITMAP CONVERSION TO ROWIDS    |                 |       |       |            |          |        | PCWP |
    |  17 |          BITMAP AND                    |                 |       |       |            |          |        | PCWP |
    |  18 |           BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |        | PCWP |
    |  19 |            INDEX RANGE SCAN            | MCPT_MPR        |    53 |       |     2   (0)| 00:00:01 |        | PCWP |
    |  20 |           BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |        | PCWP |
    |  21 |            SORT ORDER BY               |                 |       |       |            |          |        | PCWP |
    |  22 |             INDEX RANGE SCAN           | MCPT_SIMP       |    53 |       |     9   (0)| 00:00:01 |        | PCWP |
    |  23 |       REMOTE                           |                 |     1 |     4 |     0   (0)|          |  IP,_E | PCWP |
    |  24 |      INDEX RANGE SCAN                  | MCPT_MHIS_UK    |     1 |    12 |     2   (0)| 00:00:01 |        | PCWP |
    |  25 |     TABLE ACCESS BY INDEX ROWID        | MHIS            |     1 |    13 |     2   (0)| 00:00:01 |        | PCWP |
    |  26 |      INDEX UNIQUE SCAN                 | MHIS_PK         |     1 |       |     1   (0)| 00:00:01 |        | PCWP |
    ------------------------------------------------------------------------------------------------------------------------
  • 10. Re: Good execution plan but poor performance (duration of the query)
    sb92075 Guru
    Currently Being Moderated
    fcjunic wrote:
    Ok I will clarify the situation : I have no information about the old execution plan...

    The only thing I know is since I gather statistics on a specific index and its table, the duration of the query is very long !
    EXPLAIN PLAN shows query takes less than 2 seconds & most folks do not consider this to be "very long".

    was EXPLAIN PLAN generated from same DB that you claim has a problem?
  • 11. Re: Good execution plan but poor performance (duration of the query)
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Your Execution Time Take Less 2 Sec which Poor Performance you are talking about !!! Define Poor Performance
  • 12. Re: Good execution plan but poor performance (duration of the query)
    Max Seleznev Explorer
    Currently Being Moderated
    You mentioned that you gathered new stats on the specific table and index and noticed performance degradation.

    As a quick fix you can try and restore old stats and see if it improves things.

    1. By default stats history is kept for 31 days. You can check TABSTATS_HISTORY for the details on the particular table.
    2. Preserve current stats for the objects in question (DBMS_STATS.IMPORT_*) in case you need to restore them.
    3. Find the time when new stats were collected. You can check _TABLES or the above mentioned history view.
    4. If you have the stats prior to your last stats modification (history would show that) use DBMS_STATS.RESTORE_* procedure to restore the old version.
    5. Check if the query improves.
    6. Generate execution plan of the successful query and keep it where it's available for reference.

    Good luck,
    Max
  • 13. Re: Good execution plan but poor performance (duration of the query)
    Dom Brooks Guru
    Currently Being Moderated
    I have no information about the old execution plan...
    Yep, that's the message we understood in your reply above.
    We've already covered that as being a nice to have but not necessary.
    Can someone advise me on what I can check or improve to get back a quick time response for the query ?
    This is a classic sql tuning request.
    See tuning thread previously mentioned (twice).

    If you refuse to follow the steps and find out where the time is going and/or compare estimates in the execution plan to the actual execution metrics, what do you expect anyone to do.

    We can all sit around for a couple of days saying "yes, the estimates look good".
    But you've got some parallel going by the look of it, some REMOTE work, some btree bitmap conversions...

    If you don't find out where the time is really going, all you can expect is guesses.

    The steps in the tuning thread are really quite clear.

    Just do at least a little bit of what is actually required...
  • 14. Re: Good execution plan but poor performance (duration of the query)
    fcjunic Newbie
    Currently Being Moderated
    Sorry,

    While I was writing the post, my colleague solve the problem (he gathers statistics with compute option instead of estimate option).

    So I posted the new execution plan that effectively takes 1 second...

    Thanks...
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points