Parsing - Troubleshooting Oracle Performance - page 432

The second way is based on explicit cursors. In this case, some control over the cursors is possible. Nevertheless,
the open/parse/execute phases are merged in a single operation ( OPEN ). This means that only the fetch and close
phase can be controlled. The following PL/SQL block shows an example that implements test case 2:
DECLARE
CURSOR c (p_val NUMBER) IS SELECT pad FROM t WHERE val = p_val;
l_pad VARCHAR2(4000);
BEGIN
FOR i IN 1..10000
LOOP
OPEN c(i);
FETCH c INTO l_pad;
CLOSE c;
END LOOP;
END;
From a performance perspective, the two methods are similar. Although they both prevent bad code from being
written (test case 1), they don't allow very efficient code to be written (test case 3). This is because no full control over
the cursors is available.
To solve this problem, client-side statement caching is available. The maximum number of cached cursors is
determined by the session_cached_cursors initialization parameter. The default number of cached cursors is 20 in
version 10.2 and 50 from version 11.1 onward. Be aware that an initialization parameter, which is not directly related
to client-side statement caching, is “misused” in order to configure it! In fact, it's the same initialization parameter
used to control server-side statement caching.
Native Dynamic SQL: EXECUTE IMMEDIATE
From a cursor management perspective, native dynamic SQL based on EXECUTE IMMEDIATE is similar to static SQL
with implicit cursors. In other words, it's not possible to control the life cycle of a cursor. The following PL/SQL block
shows an example implementing test case 2:
DECLARE
l_pad VARCHAR2(4000);
BEGIN
FOR i IN 1..10000
LOOP
EXECUTE IMMEDIATE 'SELECT pad FROM t WHERE val = :1' INTO l_pad USING i;
END LOOP;
END;
Without control over the cursors, it's not possible to write code implementing test case 3. For this reason,
client-side cursor caching is used as with static SQL.