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.
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.