First of all, you are littering shared pool. Every time Oracle needs to parse a cursor it searches shared pool for an either exact match (CURSOR_SHARING=EXACT) or almost (up to some literals) exact match (CURSOR_SHARING=FORCE). By constructing SQL as:


in a loop, you are adding a 100 SQL statements (your loop has 100 iterations) to shared pool. If CURSOR_SHARING=EXACT, Nth iteration of the loop will look at N-1 SQL statements already it shared pool (I assume, for simplicity, shared pool is empty before script starts) and will not have a match (assuming all iterations return different c_doc values). Then Oracle will hard parse the statement and add it to shared pool. If CURSOR_SHARING=FORCE, SQL issued by iterations 2 to 100 will be matched to SQL issued by first iteration, but at a cost of much more complex matching rules comparing to exact matching. As a result, it will avoid hard parsing, which still takes more resources than forced matching, but still will cost you. To avoid forced matching, you should use bind variables:



and then specify USING i.c_doc in EXECUTE IMMEDIATE. This way, regardless of CURSOR_SHARING=EXACT or FORCE, Oracle will do soft parse for iterations 2 through 100 without waisting additional resources on forced matching and only one SQL statement will end up in shared pool . However, it still will not avoid parsing 100 times, but rather minimize parse time. To parse once, you should use DBMS_SQL, since it is the only way in PL/SQL I know where you can specify PARSE and EXECUTE in separate calls. PL/SQL statements OPEN, OPEN FOR, EXECUTE IMMEDIATE, FOR LOOP, etc. perform both PARSE and EXECUTE. Usind DBMS_SQL, you could place DBMS_SQL.PARSE before FOR LOOP and thus parsing one time only.

SY.