first_rows makes query slower
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: first_rows makes query slower

Hybrid View

  1. #1
    Join Date
    Sep 2002
    Posts
    5

    first_rows makes query slower

    Hi,

    Since 2 days my query runs much slower then before. It takes 1,4sec to run.
    There are about 280000 records in the table with constantly inserts on it.

    select /*+ FIRST_ROWS */ id, entrydate, message, status
    from work_table
    where status = 0
    and apid = 4
    order by id;

    There are 2 indexes on the table:
    work_table_pk(unique): id
    work_table_idx: (status, apid)

    Execution plan is as follows:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: HINT: FIRST_ROWS
    1 TABLE ACCESS (BY INDEX ROWID) OF 'WORK_TABLE'
    284030 INDEX (FULL SCAN) OF 'WORK_TABLE_PK' (UNIQUE)


    Now when I leave out the first_rows hint, my query runs normal (0,2s).

    The Execution plan without the first rows is:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    0 SORT (ORDER BY)
    0 TABLE ACCESS (BY INDEX ROWID) OF 'WORK_TABLE'
    0 INDEX (RANGE SCAN) OF 'WORK_TABLE_IDX' (NON-UNIQUE)


    I already rebuild the indexes and analysed table/indexes, no change.
    Why suddenly the performance degraded and how can I solve it without changing the first_row hint ??

    I had the same problem 4 months ago. I don't know how I solved it, but after many analyses and rebuilding, it suddenly was gone.

    Hope somebody can help.
    Thanks.
    Kris

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Do you have histograms on status and apid? I wonder if that might help things
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2002
    Posts
    5
    Thanks, with histogram on status column it runs normal again.
    It takes the execution plan like when I leave out first_rows hint.
    But with a histogram on apid, it runs slow again.

    There are constantly (almost every second) inserts into this table, with an update later of status and apid. Every night I delete old records.
    Should I analyse this table and the indexes frequently ? In the past when I did this, performance was worse then before the analyse.

    Thanks for the help.
    Kris

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Kris, the timings you give I assume to be the TOTAL time for the query. Is that right? How do the times to return the first row differ? (I suspect that you can't tell, if they are of the order of 0.1 second).

    My suspicion is that the CBO is doing (or trying to do) exactly as you asked with the hint: returning the first row(s) as fast as possible, at the expense of increasing the total time for the query.

    If I've guessed right, then I don't understand why you would specify a first rows hint when the total query time is apparently more important to you.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    funny how the CBO knows better than you, eh?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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