performance - Why SQL query can take so long time to return results? -

i have sql query simple as:

select * recent_cases user_id=1000000 , case_id=10095; 

it takes 0.4 seconds execute in oracle. , when 20 requests in row, takes > 10s.

the table 'recent_cases' has 4 columns: id, user_id, case_id , visited_date. there 38 records in table.

also, there 3 indexes on table: on id column, on user_id column, , on (user_id, case_id) columns pair.

any ideas?

one theory -- table has large data segment , high water mark near end, statistics not prompting optimiser use index. therefore you're getting slow full table scan. alter table ... move , rebuild indexes fix such problem, or coalesce it.