11.2.0.4
Have a dotnet program calling a procedure passing in 1 variable thats ran in a very simple 1 table cursor.
select * from table where id = P_ID -- passed in ID.
Theres 1 index on the table. only 3000 rows in the table
Its intermittently running slow that timeouts the app when it does. 0.5 seconds (which is fine) out as far as 120+ seconds. DB not under load at the time. I cant reproduce working on the SQL on its own, either index use or full table scan returns in sub second. the plan in the cache and AWR uses the index but I tested with a FULL hint, no difference in times.
Search cache and AWR for the SQL_ID. Same plan and hash value for all runs despite times. So I checked the child cursors to see was something going on there, ran version_rpt against the SQL_ID and got the following
Versions Summary
----------------
LANGUAGE_MISMATCH :6
BIND_EQUIV_FAILURE :5
LOAD_OPTIMIZER_STATS :2
Total Versions:6
~
I can look them up individually but can someone say why this might be happening, the lang mismatch probably the clients that are running the app but cant see how that would account for the vastly varying times Im seeing though on such a small table.