SQL tuning
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SQL tuning

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    The foll. sql has a performance problem. How do I tune this?

    SELECT a.dt_tm, b.data_blob from audit_event a, audit_data_blob b WHERE a.evnt_id = b.evnt_id and serv_typ_id =5399 and dt_tm > sysdate - 1 ORDER BY a.dt_tm

    The explain plan is as follows:

    Full table scan on audit_data_blob and
    index(range scan) on audit_event_ndx_4(dt_tm)


    Even though there is an index on evnt_id column in audit_data_blob , it does not seem to use it.

    Why? Please let em know.

    thanks.


  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    You neglected to add the table alias to all the columns.

    Give us this info and maybe we can help

    - Chris

  3. #3
    Join Date
    Feb 2000
    Posts
    142
    Hi,
    Sorry for the delay. If there is no alias, does it make a difference? Please let me know.

    Anyway, the sql is as follows:

    SELECT a.dt_tm, b.data_blob from audit_event a, audit_data_blob b WHERE a.evnt_id = b.evnt_id and a.serv_typ_id =5399 and a.dt_tm > sysdate - 1 ORDER BY a.dt_tm

    Thanks.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    If you eventually had added a dt_tm column to table AUDIT_DATA_BLOB, then this statement would suddenly have broken.

    As a basic rule, always add a table name or alias prefix to every column in every multi-table statement.

    Now, as to why I asked for them - without knowing where the columns came from, how would I possibly have guessed what the statement was truly doing? My mind-reading powers seem to have been on the blink recently

    Okay, so now that I have at least some clue of what the statement is doing....

    It does indeed seem odd that the statement will not use an index on that column. Reasons for this might include:

    - The table is rather small and a full scan will be more efficient
    - There are actually few distinct values for that column in the table and every value has many records, meaning that a significant percentage of the table will be read (anything over 5%?)
    - The record size for B is very short (remember that a LOB is just a pointer). Therefore, *lots* of records fit in a given block. This will make the optimizer more likely to choose a table scan for the table because it will be very efficient.

    So those are the thoughts off the top of my head.

    Any of those sound like they might be the cause?

    - Chris

  5. #5
    Join Date
    Feb 2000
    Posts
    142
    The total no of records in audit_data_blob are: 2172614

    select count(distinct evnt_id) from audit_data_blob results in
    2171268 records.

    So, the table is huge.

    What seems to be the problem?

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    How about the last one? Is the record size really small?

    Another thought - do you have current statistics on that table?

    Can you post the exact output of the explain plan for the statement? Can you then add a hint to force the index and give us that plan as well?

    - Chris

  7. #7
    Join Date
    Feb 2000
    Posts
    142
    sorry for the delay.
    The exact explain plan is as follows:

    select statement cost = 92552
    2.1 sort (order by)
    3.1 hash join
    4.1 table access(by index rowid) - audit_event
    5.1 index(range scan) - audit_event_ndx_4(non-unique)
    4.2 table access(full) - audit_data_blob

    total no of records in audit_data_blob = 2210359
    total no of records in audit_event = 2209077

    The stats on the tables are taken every week. This is a production sql.

    Thanks.



  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Again, have you tried hinting the index? Is it faster using the index?

    Is the record size really small? - that is still my best bet

    How many records does this query return?

    - Chris

  9. #9
    Join Date
    Feb 2000
    Posts
    142
    The quety returns 105 records. IfI use the index hint on evnt_id, it's not faster. But, it should use the index, isn't it?

    Thanks.


  10. #10
    Join Date
    Feb 2000
    Posts
    142
    I am so sorry. I used the wrong index. The query returns 105 records. If I use the index, it is faster, but buffer_gets in v$sqlarea is not below 2000.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width