SQL tuning - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: SQL tuning

  1. #11
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Lee, can you *please* tell me the record size, or at least the fields and types?

    Also, how are you measuring the time? Is the index faster simply because you ran it second and the data was cached? You need to use a tool that can measure to less than a second, make sure nobody else is running anything, and run each test multiple times. That is the only way to accurately measure time in queries. Once you do that, what are the times?

    As far as whether it *should* use the index or not - the optimizer will use the plan that it *believe's* is the most efficient. That does not mean *always* doing an indexed Nested Loop by a long shot. When > 5% of the table data is retrieved, as a rule of thumb, the table scan is faster. There are, as I alluded in an earlier response, many other reasons why a table scan might be faster as well. There are many more than what I listed as well.

    Finally, quick lesson on LRs. While I *fully* advocate the use of LRs for measuring the improvement in performance, they are not equivalent between table scan plans and indexed plans - the table scan plans may have fewer LRs, but they should generally be weighted heavier because the LR/PR ratio is much higher for table scans and they therefore also usually take up more cache. Regardless, however, what does 2000 have to do with anything?

    - Chris

  2. #12
    Join Date
    May 2001
    Location
    Dominican Republic
    Posts
    3

    our two cents.....

    Try this out....

    Original sql scrip.
    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

    Suggested
    SELECT a.dt_tm, b.data_blob

    FROM audit_event a, t_data_blob b

    WHERE a.serv_typ_id = 5399
    AND a.dt_tm > sysdate - 1
    AND b.evnt_id = a.evnt_id

    ORDER BY a.dt_tm


  3. #13
    Join Date
    Feb 2000
    Posts
    142
    2000 is the limit which should be the buffer_gets in v$sqlarea a decided by our Lead DBA.

    In audit_evnt table-

    evnt_id - number(10) not null
    serv_typ_id - number(10)
    dt_tm - date

    Do u need the other columns too? Let em know.


    In audit_data_blob table-
    evnt_id - number(10) not null


    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