CREATE TABLE MSG_USER
(
  MSG_USER_ID      NUMBER(12)               NOT NULL,
  MSG_INSTANCE_ID  NUMBER(12)               NOT NULL,
  CLNT_OID           VARCHAR2(16 BYTE)        NOT NULL,
  PROCESS_STEP_ID      NUMBER(12),
  USER_OID             VARCHAR2(16 BYTE)        NOT NULL,
  MSG_STATUS       VARCHAR2(3 BYTE)         NOT NULL,
  SEND_REMINDER_DATE   DATE,
  SEND_REMINDER_COUNT  NUMBER(2),
  HIDE_DETAILS_LINK    NUMBER(1),
  ARCHIVED             NUMBER(1)                NOT NULL,
  READ                 NUMBER(1)                NOT NULL,
  START_DATE           DATE                     NOT NULL,
  END_DATE             DATE,
  MSG_TYPE_NAME    VARCHAR2(80 BYTE)        NOT NULL,
  BASE_TYPE            CHAR(3 CHAR)             NOT NULL,
  GROUP_OID            NUMBER(12),
  DUE_DATE             DATE,
  REMINDER_TYPE        NUMBER(1)
);


CREATE UNIQUE INDEX PK_MSG_USER ON MSG_USER
(MSG_USER_ID);


CREATE INDEX MSG_USER_IDX01 ON MSG_USER
(USER_OID, MSG_STATUS, CLNT_OID);


CREATE INDEX MSG_USER_IDX02 ON MSG_USER
(SEND_REMINDER_DATE);


CREATE INDEX MSG_USER_IDX03 ON MSG_USER
(PROCESS_STEP_ID);


CREATE INDEX MSG_USER_IDX04 ON MSG_USER
(CLNT_OID);


CREATE INDEX MSG_USER_IDX05 ON MSG_USER
(MSG_TYPE_NAME);


CREATE INDEX MSG_USER_IDX06 ON MSG_USER
(MSG_INSTANCE_ID);


ALTER TABLE MSG_USER ADD (
  CONSTRAINT PK_MSG_USER
  PRIMARY KEY
  (MSG_USER_ID)
  USING INDEX PK_MSG_USER
  ENABLE VALIDATE);

CREATE TABLE MSG_INSTANCE
(
  MSG_INSTANCE_ID        NUMBER(12)         NOT NULL,
  CLNT_OID                 VARCHAR2(16 BYTE)  NOT NULL,
  SUBMITTER_USER_OID         VARCHAR2(16 BYTE),
  SUBMITTER_PROCESS_ID       VARCHAR2(255 BYTE),
  SUBMITTER_LASTNAME         VARCHAR2(64 BYTE),
  SUBMITTER_FIRSTNAME        VARCHAR2(64 BYTE),
  AFFECTED_USER_OID          VARCHAR2(16 BYTE),
  AFFECTED_LASTNAME          VARCHAR2(64 BYTE),
  AFFECTED_FIRSTNAME         VARCHAR2(64 BYTE),
  AFFECTED_USER_EMAIL        VARCHAR2(256 BYTE),
  NOTIFY_AFFECTED_USER       NUMBER(1)          NOT NULL,
  REMOTE_ID                  NUMBER(15),
  SRC_APP                    VARCHAR2(80 BYTE)  NOT NULL,
  SUBJECT                    VARCHAR2(80 BYTE),
  SUMMARY                    VARCHAR2(4000 BYTE),
  CREATED_DATE               DATE               NOT NULL,
  MODIFIED_DATE              DATE,
  PRIORITY                   CHAR(3 BYTE)       NOT NULL,
  ACTION_URL_EXTENSION       VARCHAR2(100 BYTE),
  SUBJECT_LANG_ID            NUMBER(12),
  SUMMARY_LANG_ID            NUMBER(12),
  SUBMITTER_PROCESS_LANG_ID  NUMBER(12)
);


CREATE UNIQUE INDEX PK_MSG_INSTANCE ON MSG_INSTANCE
(MSG_INSTANCE_ID);


CREATE INDEX MSG_INSTANCE_IDX01 ON MSG_INSTANCE
(CLNT_OID);


CREATE INDEX MSG_INSTANCE_IDX02 ON MSG_INSTANCE
(REMOTE_ID, CLNT_OID);


ALTER TABLE MSG_INSTANCE ADD (
  CONSTRAINT PK_MSG_INSTANCE
  PRIMARY KEY
  (MSG_INSTANCE_ID)
  USING INDEX PK_MSG_INSTANCE
  ENABLE VALIDATE);

GRANT DELETE, INSERT, UPDATE ON MSG_INSTANCE TO POD_MOVER;

GRANT SELECT ON MSG_INSTANCE TO PUBLIC;

Query:

variable SYS_B_0 varchar2(16);
variable SYS_B_1 NUMBER;
variable SYS_B_2 varchar2(16);
variable SYS_B_3 varchar2(16);

--exec :SYS_B_0:='G3T2T99999999E7F'; this client has more data And below is a prod client for prt03y
--
exec :SYS_B_0:='036X199999999BRB';
exec :SYS_B_1:=1;
exec :SYS_B_2:='NEW';
exec :SYS_B_3:='INP';

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (  SELECT *
                    FROM msg_instance mi
                   WHERE     mi.clnt_oid = :SYS_B_0
                         AND EXISTS
                                (SELECT :SYS_B_1
                                   FROM msg_user mu
                                  WHERE mu.msg_instance_id = mi.msg_instance_id
                                        AND mu.msg_status IN
                                                        (:SYS_B_2, :SYS_B_3))
                ORDER BY created_date DESC) A)
 WHERE rn >= 1 AND rn < 11;

Query plan:

--------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |       |       |  2296 (100)|          |
|*  1 |  FILTER                           |                    |       |       |            |          |
|*  2 |   VIEW                            |                    |   766 |  2002K|  2296   (1)| 00:00:28 |
|   3 |    COUNT                          |                    |       |       |            |          |
|   4 |     VIEW                          |                    |   766 |  1992K|  2296   (1)| 00:00:28 |
|   5 |      SORT ORDER BY                |                    |   766 |   227K|  2296   (1)| 00:00:28 |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

|   6 |       NESTED LOOPS SEMI           |                    |   766 |   227K|  2295   (1)| 00:00:28 |
|   7 |        TABLE ACCESS BY INDEX ROWID| MSG_INSTANCE       |   766 |   219K|   384   (0)| 00:00:05 |
|*  8 |         INDEX RANGE SCAN          | MSG_INSTANCE_IDX01 |   766 |       |     7   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS BY INDEX ROWID| MSG_USER           |  1533K|    16M|     3   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN          | MSG_USER_IDX06     |     2 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((:B1>:B0 AND :B0<:B1))
   2 - filter(("RN">=:B0 AND "RN"<:B1))
   8 - access("MI"."CLNT_OID"=:SYS_B_0)
   9 - filter(("MU"."MSG_STATUS"=:SYS_B_2 OR "MU"."MSG_STATUS"=:SYS_B_3))
  10 - access("MU"."MSG_INSTANCE_ID"="MI"."MSG_INSTANCE_ID")


37 rows selected.

select * from table(dbms_xplan.display_cursor());