Forum Stats

  • 3,781,172 Users
  • 2,254,486 Discussions
  • 7,879,604 Comments

Discussions

AWR and low-intensive SQL

639308
639308 Member Posts: 25
edited Jan 26, 2009 9:58PM in General Database Discussions
Hi all,

It's only been recently that I've been learning a small bit more about the AWR. One script I found (by Burleson) claims to report index invocation counts, etc. It also claims that if the index is not reported on, that's because it wasn't used, and hence isn't in the AWR.

I disagree with this. For example, I ran a simple statement which, according to the execution plan, would use index X -- I know this isn't a guarantee that it did use the index, but it executed quickly on a 1.2 million row table, so I can be pretty sure the index was used. But, this index usage never showed up in the AWR (yes, I did wait for later snapshots to be taken).

Then I read somewhere, that low intensive SQL, by default, isn't recorded anywhere in the AWR. Because it's not really interesting. Is this correct? Doesn't that make using the AWR for this sort of thing useless?

If so, can anyone suggest a reliable way of finding unused indexes using the AWR, besides index monitoring?

FYI, the script is something like this:

select p.object_name, count(*)
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and p.object_owner = 'MYSCHEMA' and p.operation = 'INDEX'
group by p.object_name;




Regards,
Cormac
Tagged:
«1

Answers

  • 3530
    3530 Member Posts: 459
    Hi,

    Use set autotrace on while you are executing the SQL, You will get the execution plan for SQL you executed. So you will be able to decide if INDEX is used or not.

    Then you can cross check with your AWR query result.

    Dilip Patel.
  • Hi,

    See if the following post helps you in any way.

    http://www.oracle-base.com/articles/10g/IndexMonitoring.php

    Regards,
    Naveed.
  • 639308
    639308 Member Posts: 25
    edited Jan 21, 2009 7:36AM
    Hi Dilip,

    As I said in my original post, I have done this, and it suggests the index is being used and it is NOT showing up in the AWR.


    Regards,
    Cormac
  • 639308
    639308 Member Posts: 25
    Hi Naveed,

    As I said in the original post, I would like a way besides index monitoring, if possible. I would prefer more than a "yes/no" answer. I thought this would be possible via the AWR. Perhaps it isn't.



    Regards,
    Cormac
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    credmond wrote:
    Hi all,

    It's only been recently that I've been learning a small bit more about the AWR. One script I found (by Burleson) claims to report index invocation counts, etc. It also claims that if the index is not reported on, that's because it wasn't used, and hence isn't in the AWR.

    I disagree with this. For example, I ran a simple statement which, according to the execution plan, would use index X -- I know this isn't a guarantee that it did use the index, but it executed quickly on a 1.2 million row table, so I can be pretty sure the index was used. But, this index usage never showed up in the AWR (yes, I did wait for later snapshots to be taken).

    Then I read somewhere, that low intensive SQL, by default, isn't recorded anywhere in the AWR. Because it's not really interesting. Is this correct? Doesn't that make using the AWR for this sort of thing useless?

    If so, can anyone suggest a reliable way of finding unused indexes using the AWR, besides index monitoring?
    Cormac,

    I think you hit the nail on the head, since you've exactly identified the deficiency of the AWR approach described.

    Remember, the AWR is based on snapshots and some of the data contained in the snapshots is only sampled. So your "low-intensive" SQL won't be captured by default. Note you can modify the default behaviour using DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS, but depending on the size of your shared pool and the number of statements this might not be feasible respectively still miss a significant number of SQLs, and therefore miss some important indexes.

    So I think the answer to your question, if there is a reliable way of finding unused indexes using the AWR, is NO.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
    Randolf Geist
  • 639308
    639308 Member Posts: 25
    Hi,

    Thanks for the response. I suppose old-fashioned monitoring is the way to go! At least I know what was going on :) ...



    Regards,
    Cormac
  • 681022
    681022 Member Posts: 4
    edited Jan 21, 2009 9:11AM
    Hello Chris

    *"low intensive SQL, by default, isn't recorded anywhere in the AWR"*

    It depends on your setting for your SQL collection thresholds.

    I set them very low to collect all SQL (except transient SQL between snspshots), and then remove the rows from the SQL tables after I run the reports.

    *"One script I found (by Burleson) claims to report index invocation counts"*

    You can ask Don Burleson at that link below. It's often has over a hundred users online, someone who can illuminate you're question:

    http://dbaforums.org/oracle/index.php?showforum=2
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,845 Gold Crown
    Oldeolthof wrote:

    *"low intensive SQL, by default, isn't recorded anywhere in the AWR"*

    It depends on your setting for your SQL collection thresholds.

    I set them very low to collect all SQL (except transient SQL between snspshots), and then remove the rows from the SQL tables after I run the reports.
    The AWR doesn't use thresholds, it uses a "Top N" setting, which defaults to 30 for statistics_level = "typical" and 100 for statistics_level = "all".

    You can change this by setting the "topnsql" parameter through the procedure dbms_workload_repository.modify_snapshot_settings(), but picking a number is a bit hit or miss, and using the special value of "maximum" could be a little nasty on a big system.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking" Carl Sagan
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,845 Gold Crown
    >
    Thanks for the response. I suppose old-fashioned monitoring is the way to go! At least I know what was going on :) ...
    Your observations about the AWR are correct; but even "old-fashioned monitoring" leaves much to be desired. It is perfectly reasonable to have indexes which never get used in execution plans but are still necessary for the safe running of the system.

    e.g. Foreign key indexes that have been created to avoid locking issues, unique indexes that exist to protect uniqueness constraints, but don't (or perhaps rarely) get used in queries.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    Stephen Hawking

    "Science is more than a body of knowledge; it is a way of thinking" Carl Sagan
  • 639308
    639308 Member Posts: 25
    Hi Jonathon,

    Thanks for the reply. Trouble is, we have many indexes which were probably added simply for precautionary purposes, and we'd like to drop a few to gain better insert/update performance. A lot of components query our tables, so I actually guess 95% of the indexes are actually used -- but some of the used indexes may not be necessary, i.e., we could drop them, and take a performance hit now and again on some rare SQL but gain better inserts/updates which is far more common.

    That is why, to me, monitoring isn't very helpful; it's only "yes/no". I'd like some more concrete statistics.


    Thanks,
    Cormac
This discussion has been closed.