How to diagnose and avoid subverting the benefit
of the PL/SQL cursor cache

Let your application run and observe open_cursor_sql_text as above. (In the real world you may need to restrict on v$open_cursor.sid to focus on a single session.) A SELECT COUNT()...GROUP BY query thus...

select count(sql_text) c, sql_text
  from open_cursor_sql_text
  group by sql_text

  order by c desc, sql_text

...helps, since we're looking for multiple occurrences of the same SQL statement.

If ever you find a SQL statement with two ore more occurences, then investigate!

The likely explanation is that you missed an opportunity to write some re-usable code. The solution will usually be to encapsulate a cursor for the offending SQL statement within a procedure or function, and then to replace your previous constructs that used this SQL statement with an invocation of the new procedure or function, for example...

procedure P is
  g_last_name employees.last_name%type;
  procedure Get_Last_Name is

  begin
    select last_name into g_last_name from employees where employee_id = 100;
  end Get_Last_Name;
begin
  Get_Last_Name;
  --
  -- time elapses, last_name may have changed for this employee
  --
  Get_Last_Name;

end P;

This re-write not only yields code with better readablility and maintaiability, but also better performance!

There may be some special cases where you have to decide that multiple cursors for the same statement are unavoidable (for example if the SQL statement invokes a function which in turn executes the same SQL statement), but in the majority of cases finding mutilple occurrences of the same SQL statement in v$open_cursor means you have an opportunity to improve your performance.