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

Do you have histograms on status and apid? I wonder if that might help things

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

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.

funny how the CBO knows better than you, eh?