This discussion is archived
7 Replies Latest reply: Aug 5, 2011 7:27 AM by 851356 RSS

SQL Tuning Advisor (STA) recommends indexes that already exists?

851356 Newbie
Currently Being Moderated
I have enabled the STA to run twice a week, while we are going live with a new application in a new database. I want to insure we have good performance as the load goes up. I was not involved in the original design, which there not not much of, I've discovered. Since we are licensed for it, it seemed like a safe thing to turn on.

The STA ran last night and the report tells me it looked at 736 SQLs with findings, 143 without findings, and skipped 0.

What puzzles me is that, in the "Statistics Finding Summary", it's reporting stale stats on some tables which I know are up to date, as I ran stats on them yesterday and there were no updates to them.

Worse, in the "Index Finding Summary", it reports indexes needed that already do, in fact, exist.

Is there some reason for this? Is there some way to get the STA to be aware of these and avoid the erroneous findings so that I can concentrate on real ones?

Thanks in advance for any suggestions.

PDP
  • 1. Re: SQL Tuning Advisor (STA) recommends indexes that already exists?
    sb92075 Guru
    Currently Being Moderated
    pdp0617 wrote:
    I have enabled the STA to run twice a week, while we are going live with a new application in a new database. I want to insure we have good performance as the load goes up. I was not involved in the original design, which there not not much of, I've discovered. Since we are licensed for it, it seemed like a safe thing to turn on.

    The STA ran last night and the report tells me it looked at 736 SQLs with findings, 143 without findings, and skipped 0.

    What puzzles me is that, in the "Statistics Finding Summary", it's reporting stale stats on some tables which I know are up to date, as I ran stats on them yesterday and there were no updates to them.

    Worse, in the "Index Finding Summary", it reports indexes needed that already do, in fact, exist.

    Is there some reason for this? Is there some way to get the STA to be aware of these and avoid the erroneous findings so that I can concentrate on real ones?

    Thanks in advance for any suggestions.

    PDP
    do EXPLAIN PLAN show index being used?


    do as below so we can know complete Oracle version & OS name.

    Post via COPY & PASTE complete results of
    SELECT * from v$version;
  • 2. Re: SQL Tuning Advisor (STA) recommends indexes that already exists?
    Mark D Powell Guru
    Currently Being Moderated
    pdp, I do not use the tool in question, but for the " in the "Index Finding Summary", it reports indexes needed that already do, in fact, exist." issue have you verified that the indexes in question are 1 - usable and 2 - visible?

    The Oracle performance and tuning features always have to be taken with a grain of salt and sometimes the result is just flat-out wrong.

    HTH -- Mark D Powell --
  • 3. Re: SQL Tuning Advisor (STA) recommends indexes that already exists?
    851356 Newbie
    Currently Being Moderated
    Hi Mark,

    Thanks for your interest, yes, the index is being used . Here is the evidence that you were asking about.

    It has existed for many weeks, now as the info below will show.

    Here is the STA run info, the run was from 2011-07-28 at 11 PM:
    ========================================

    Advisory Type Name Description User Status Start Time Duration (seconds) Expires In (days)
    SQL Tuning Advisor SYS_AUTO_SQL_TUNING_TASK Automatic SQL Tuning Task SYS COMPLETED Jul 28, 2011 11:00:03 PM 865 UNLIMITED


    Here is part of the Index Finding Summary
    =========================================

    Index Finding Summary
    Table Name Schema References Index Columns
    EINSTANCE_ARCH EINTAKE 74 GLOBAL_PATIENT_ID

    Drilling into that table, we see the following SQL_ID listed first, "g9uf2kmyvc66y" which is just one of many.

    Here is 1 if the sqls, which use the "global_patient_id" as a predicate
    (with the many columns that are actually selected not listed as they are not germain:
    =====================================================================================

    SELECT *
    FROM (SELECT '0' AS locked,
    (... a whole lot of columns selected)
    FROM einstance_arch a
    LEFT OUTER JOIN
    patient d
    ON a.global_patient_id = d.patient_id
    LEFT OUTER JOIN
    referral e
    ON a.instance_id = e.einstance_id,
    einstance_states_arch b
    WHERE a.current_state = b.inst_state_id
    AND a.current_state_id != 15
    AND ( a.global_patient_id = 496 )
    ORDER BY a.instance_id ASC)
    WHERE ROWNUM <= :1

    Here's an explain plan extracted from Toad, the index in question is identified by ==>:
    ======================================================

    Plan
    SELECT STATEMENT ALL_ROWS Cost: 19 Bytes: 4,528 Cardinality: 4
    1 INDEX UNIQUE SCAN INDEX (UNIQUE) EINTAKE.STATES_PK Cost: 0 Bytes: 14 Cardinality: 1
    3 SORT AGGREGATE Bytes: 6 Cardinality: 1
    2 INDEX RANGE SCAN INDEX EINTAKE.DOCUMENT_ATTR_LNK_INST_ID_IDX2 Cost: 1 Bytes: 6 Cardinality: 1
    18 COUNT STOPKEY
    17 VIEW EINTAKE. Cost: 19 Bytes: 4,528 Cardinality: 4
    16 SORT ORDER BY STOPKEY Cost: 19 Bytes: 1,544 Cardinality: 4
    15 NESTED LOOPS
    13 NESTED LOOPS Cost: 18 Bytes: 1,544 Cardinality: 4
    11 NESTED LOOPS OUTER Cost: 10 Bytes: 1,296 Cardinality: 4
    8 HASH JOIN OUTER Cost: 9 Bytes: 1,228 Cardinality: 4
    5 TABLE ACCESS BY INDEX ROWID TABLE EINTAKE.EINSTANCE_ARCH Cost: 6 Bytes: 1,152 Cardinality: 4
    ==> 4 INDEX RANGE SCAN INDEX EINTAKE.EINSTANCE_ARCH_GLBL_PT_ID_IDX3 Cost: 1 Cardinality: 5
    7 TABLE ACCESS BY INDEX ROWID TABLE EINTAKE.PATIENT Cost: 2 Bytes: 19 Cardinality: 1
    6 INDEX UNIQUE SCAN INDEX (UNIQUE) EINTAKE.PATIENT_PK Cost: 1 Cardinality: 1
    10 TABLE ACCESS BY INDEX ROWID TABLE EINTAKE.REFERRAL Cost: 1 Bytes: 17 Cardinality: 1
    9 INDEX RANGE SCAN INDEX EINTAKE.REFERRAL_EINSTANCE_ID Cost: 0 Cardinality: 1
    12 INDEX UNIQUE SCAN INDEX (UNIQUE) EINTAKE.EINSTANCE_STATES_ARCH_PK Cost: 1 Cardinality: 1
    14 TABLE ACCESS BY INDEX ROWID TABLE EINTAKE.EINSTANCE_STATES_ARCH Cost: 2 Bytes: 62 Cardinality: 1

    Here is the DDL info on the index in question:
    ============================

    select object_name, created, last_ddl_time from user_objects where object_name = 'EINSTANCE_ARCH_GLBL_PT_ID_IDX3';

    OBJECT_NAME CREATED LAST_DDL_TIME
    ------------------------------ --------------------- ---------------------
    EINSTANCE_ARCH_GLBL_PT_ID_IDX3 2011 07 11 11:22:36 2011 07 11 11:22:36

    1 row selected.

    Regards,
    Paul
  • 4. Re: SQL Tuning Advisor (STA) recommends indexes that already exists?
    Mohamed Houri Pro
    Currently Being Moderated
    Is the suggested index match exactly the existing index?

    I mean the order of the columns in the index, are they at the same positions in the existing index and in the suggested one?

    Mohamed Houri
  • 5. Re: SQL Tuning Advisor (STA) recommends indexes that already exists?
    851356 Newbie
    Currently Being Moderated
    Hi Mohamed,

    Yes, it is absolutely the same column in the index as in the query.

    Here is the predicate: AND ( a.global_patient_id = 496 )

    And here is the index ddl without all the storage parm, etc.:
    CREATE INDEX EINTAKE.EINSTANCE_ARCH_GLBL_PT_ID_IDX3 ON EINTAKE.EINSTANCE_ARCH (GLOBAL_PATIENT_ID)

    As I tried to make clear, I can go in now and find the SQL its issuing recommendations on and see that the actual execution plan is using it. I use Oracle Ent. Mgr. Grid Control to do that.

    Here is my theory, but I'm not sure how to prove it. The tuning advisor is looking into the AWR and finding past runs, still, from when the index was not present, and plans from those historic SQLs that used different access methods.

    I'd like to think there is some way, somehow to tell it to not look at old plans, but only into the current SQL cached in memory.

    But, I dont know how to prove that or how to make it run and give current recommendations.

    Regards,
    Paul
  • 6. Re: SQL Tuning Advisor (STA) recommends indexes that already exists?
    851356 Newbie
    Currently Being Moderated
    OK, for anyone that respondeded to this post, I believe I've found the answer. I was looking at the run from last night and noticed a section that I had not looked at or used before.

    When you click on the SQL tuning run of interest, you are taken to a page called "Automatic SQL Tuning Result Summary". There are several sections to this page. I had been going to the bottom when it opened, to look at the "Index Finding Summary".

    But from the top, there is "Task Status", then "Summary Time Period", "Overall Task Statistics", and "Profile Effect Statistics".

    It appears that it is the "Summary Time Period" that is the key I was looking for here. There are 6 setting you can choose. The default is "Last 31 Days".

    So my suspicion that it was looking at a much longer time period then since the previous run was correct. If I pick "Last 24 Hours", for example, it only recommends indexes that currently do not exist.

    Thanks for all who responded to this post.

    Regards,
    Paul
  • 7. Re: SQL Tuning Advisor (STA) recommends indexes that already exists?
    851356 Newbie
    Currently Being Moderated
    I forgot to mark this as answered, which I believe it now is.

    Paul

Legend

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