Oracle query optimizer chooses bad plan at times with same query

I have my jetty application connects to Oracle database to persist application metadata.
I have below Sql query fired by application :

select * from my_table
 where id = 'some_id' --> not unique, i can have repeated Ids
 and created > sysdate - 160/24 
 and started <= to_timestamp('07-06-2023 06:12:45', 'dd-mm-yyyy hh24:mi:ss') 
 and status in (3, 13, 14);

All table column are indexed except started column.

I observed that some time this query is taking very long to respond around 5 minutes. Generally it get served with in few seconds.
After further deep dive, we found that optimizer chooses bad plan when it is taking longer. That time it is filtering on the basis of id column . It works well when it scan via status.
I tried partitioning this table every 7 days as volume is quite high (30-40 million rows per day) in this table. It helped but still this issue did not go permanently.
How can i make sure that query optimizer always uses good plan ?

Without changing your indexing, you can get stability by hinting your query. If the index on status is called idx_status, for example, then:

select /*+ INDEX(my_table idx_status) */ * from my_table
 where id = 'some_id' --> not unique, i can have repeated Ids
 and created > sysdate - 160/24 
 and started <= to_timestamp('07-06-2023 06:12:45', 'dd-mm-yyyy hh24:mi:ss') 
 and status in (3, 13, 14);

Though, if you have a significant number of records with those statuses that are older than a week, you will want to weed those out at the index level as well. And, if your id field reduces your results in any significant way, that too should be indexed. If this is the case, then you need what we call a concatenated or composite or multi-column index. From left to right, start with columns you do an equality predicate on, then columns you do IN on, then lastly columns you do a range/ inequality on. So the index would be:

CREATE OR REPLACE INDEX idx_composite ON my_table(id,status,created)

That will probably entice the optimizer so much that no hint is needed to get it to be used. To make sure though, you should drop the existing index on ID and let this new composite one take its place. Most likely this will give you the best performance.

You can use SQL plan baseline. Like this:

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '&sql_id',
    plan_hash_value => '&plan_hash_value',
    sql_handle => '&handle');
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/