How careless programing can subvert the benefit
of the PL/SQL cursor cache

Consider this trivial program, Open_Close_5_Same. This was generated thus...

begin Generate_Open_Many_Cursors (
  p_number=>5, p_close=>true, p_same_sql=>true ); end;

Compile and run it, and observe it from another session as above. You'll see...

BEGIN Open_Close_5_Same; END;
SELECT 'same' from dual
SELECT 'same' from dual
SELECT 'same' from dual

SELECT 'same' from dual
SELECT 'same' from dual

...because, from the viewpoint of the PL/SQL run-time system the cursors, having different names, are different.

Though the SQL statement will be represented only once in v$sqlarea, and the cost of parsing it and calculating the execution plan will be saved for each successive different cursor with the same SQL, we're nevertheless still wasting some resources by each time we open a new cursor, by looking up its SQL in v$sqlarea, and setting up the define-binds.

This can also occur when using an implicit cursor. (An implicit cursor has no identifier, and the programmer does not use the open, fetch and close statements with it. Implicit cursors occur for all cases of static SQL DML, for the SELECT...INTO construct, and for a cursor FOR loop with a SELECT statement.)

Create this procedure...

procedure P is
  v_last_name employees.last_name%type;

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

...re-connect as programmer and execute it. Observe open_cursor_sql_text as above. You'll see...

SELECT last_name from employees where employee_id = 100
SELECT last_name from employees where employee_id = 100
begin p; end;

...which shows that the implicit cursor created for the first SQL statement is not reused for the second, even though the text is identical.