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