Oracle PL/SQL Programming, 5th Edition

Setting Up Multithreaded Mode

Oracle Database 10g introduced a way for multiple sessions to share a single external procedure process. Although this feature takes a bit of effort to set up, it could pay off when you have many users running external procedures. Here are the minimum steps required for your DBA to turn on multithreaded mode:

1. Shut down the external procedure listener. If you have configured a separate listener for it as recommended above, this step is simply:

   OS> lsnrctl stop extproc_listener

2. Edit listener.ora: first, change your extprocKey (which by default would be EXTPROC0 or EXTPROC1) to PNPKEY; second, to eliminate the possibility of any dedicated listeners, delete the entire SID_LIST_EXTPROC_LISTENER section.

3. Edit tnsnames.ora, changing your extprocKey to be PNPKEY.

4. Restart the external procedure listener; for example:

   OS> lsnrctl start extproc_listener

5. At the operating system command prompt, be sure you have set a value for the AGTCTL_ADMIN environment variable. The value should consist of a fully qualified directory path; this tells agtctl where to store its settings. (If you don’t set AGTCTL_ADMIN, but do have TNS_ADMIN set, the latter will be used instead.)

6. If you need to send any environment variables to the agent such as EXTPROC_DLLS or LD_LIBRARY_PATH, set these in the current operating system session. Here are some examples (if using the bash shell or equivalent):

   OS> export EXTPROC_DLLS=ANY
   OS> export LD_LIBRARY_PATH=/lib:/usr/local/lib/sparcv9

7. Assuming that you ...