Parsing - Troubleshooting Oracle Performance - page 434

Since full control over the cursors is given, there is no problem in implementing test case 3. The following
PL/SQL block shows an example. Notice how the procedures that prepare ( open_cursor , parse , and define_column )
and close ( close_cursor ) the cursor are placed outside the loop to avoid unnecessary soft parses.
DECLARE
l_cursor INTEGER;
l_pad VARCHAR2(4000);
l_retval INTEGER;
BEGIN
l_cursor := dbms_sql. open_cursor ;
dbms_sql. parse (l_cursor, 'SELECT pad FROM t WHERE val = :1', 1);
dbms_sql. define_column (l_cursor, 1, l_pad, 10);
FOR i IN 1..10000
LOOP
dbms_sql. bind_variable (l_cursor, ':1', i);
l_retval := dbms_sql. execute (l_cursor);
IF dbms_sql. fetch_rows (l_cursor) > 0
THEN
NULL;
END IF;
END LOOP;
dbms_sql. close_cursor (l_cursor);
END;
The database engine isn't able to take advantage of client-side statement caching with the dbms_sql package. So,
in order to optimize an application that is suffering because of too many soft parses (as test case 2 is), you must modify
it to reuse the cursors (as test case 3 does). As a workaround, you could consider server-side statement caching.
OCI
OCI is a low-level application programming interface. Consequently, it provides full control over the life cycle of cursors.
For example, in the following code snippet, which implements test case 2, notice how every step is explicitly coded:
for (i=1 ; i<=10000 ; i++)
{
OCIStmtPrepare2 (svc, (OCIStmt **)&stm, err, sql, strlen(sql), NULL, 0, OCI_NTV_SYNTAX,
OCI_DEFAULT);
OCIDefineByPos (stm, &def, err, 1, val, sizeof(val), SQLT_STR, 0, 0, 0, OCI_DEFAULT);
OCIBindByPos (stm, &bnd, err, 1, &i, sizeof(i), SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT);
OCIStmtExecute (svc, stm, err, 0, 0, 0, 0, OCI_DEFAULT);
if (r = OCIStmtFetch2 (stm, err, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT) == OCI_SUCCESS)
{
// do something with data...
}
OCIStmtRelease (stm, err, NULL, 0, OCI_DEFAULT);
}
Since full control over the cursors is available, it's possible to implement test case 3 as well. The following code
snippet is an example. Notice how the functions that prepare ( OCIStmtPrepare2 and OCIDefineByPos ) and close
( OCIStmtRelease ) the cursor are placed outside the loop to avoid unnecessary soft parses.