Why Not to Run SQL script using Concurrent Programs

Let's create a simple script as given below

BEGIN
EXECUTE IMMEDIATE 'SELECT :bind_1 FROM DUAL' USING &1;
END;

Now create concurrent program to execute that script.
Run concurrent program during such a time when no users are in your test or development system. Use parameter value as 23
Now run following query

SELECT first_load_time
, sql_text
FROM v$sql a
ORDER BY a.first_load_time DESC

first_load_time will give you idea of your sql execution time and sql_text will give you sql being executed. You should see these three sqls

SELECT :bind_1 FROM DUAL

BEGIN EXECUTE IMMEDIATE 'SELECT :bind_1 FROM DUAL' USING 23; END; 

BEGIN FND_GLOBAL.INITIALIZE(:session_id,14304,21623,660,0,0,99630707,99630707,20029,50186,41022224,41022224); END;

From where the third sql come? I guess concurrent manager runs this pl/sql block for each sql script being executed by concurrent program.

The second sql is your script. It is not suppose to use literal but parameters passed to this script made the script with literal. Suppose this script is run n times per day, what will you have in your shared pool? Do you want that? Answer should be EMPHATIC NO. What is the lesson learned? DO NOT USE SQL SCRIPT IN A CONCURRENT PROGRAM. Even if sql script does not have any parameter, unnecessary call to FND_GLOBAL.initialize is disappointing.

The first sql will be shared by all other executions of the concurrent program. because it is using bind variable.