on prod system we have query that use FTS instead of IRS

SQL_ID 960mmmrtwxbpf, child number 2
-------------------------------------
SELECT "SV_CPP$SEARCH_BY_CONTACT"."ID",
"SV_CPP$SEARCH_BY_CONTACT"."ID_OBJ", "SV_CPP$SEARCH_BY_CONTACT"."VAL"
FROM "SV_CPP$SEARCH_BY_CONTACT" WHERE "SV_CPP$SEARCH_BY_CONTACT"."VAL"
= :arg0

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1577350318

---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 186K(100)| |
|* 1 | TABLE ACCESS FULL| CTT | 264K| 10M| 186K (1)| 00:37:15 |
---------------------------------------------------------------------------


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

1 - filter(("VRF_ON" IS NOT NULL AND SYS_OP_C2C("VAL")=:ARG0 AND
INTERNAL_FUNCTION("CTT_TYP")))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "ID"[NUMBER,22], "VAL"[VARCHAR2,4000], "R_OBJ"[NUMBER,22]

this is because developers put in bind variable nvarchar2 ( you may find bind type in v$sql_bind_capture ) instead of varchar2, they say that they can’t change code

solved by

create index i_ctt_nvar_var on ctt (SYS_OP_C2C(VAL)) online;