Section 27.3.  Setting Up Multithreaded Mode

27.3. Setting Up Multithreaded Mode Oracle Database 10g Release 1 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: 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 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. Edit tnsnames.ora, changing your extprocKey to be PNPKEY. Restart the external procedure listener; for example: OS> lsnrctl start extproc_listener 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.) 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 Assuming that you are still using the external procedure's default listener "SID," that is, PLSExtProc, run the following: OS> agtctl startup extproc PLSExtProc To see if it's working, you can use the "lsnrctl services" command: OS> lsnrctl services extproc_listener ... Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=PNPKEY)) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status READY, has 1 handler(s) for this service... Handler(s): "ORACLE SERVER" established:0 refused:0 current:0 max:5 state:ready PLSExtProc (ADDRESS=(PROTOCOL=ipc)(KEY=#5746.1.4)) This output is what we hoped to see; the agent is listed in state "ready," and is not labeled as dedicated. This command also shows stats on the number of sessions; in the output above, everything is "0" except the maximum number of sessions, which defaults to 5. Internally, a multithreaded agent uses its own listener/dispatcher/worker bee arrangement, allowing each session request to get handed off to its own thread of execution. You can control the numbers of tasks using "agtctl set" commands. For example, to modify the maximum number of sessions, first shut down the agent: OS> agtctl shutdown PLSExtProc Then set max_sessions: OS> agtctl set max_sessions n PLSExtProc Where n is the maximum number of Oracle sessions that can connect to the agent simultaneously. Finally, restart the agent: OS> agtctl startup extproc PLSExtProc When tuning your setup, there are several parameter settings to be aware of: Parameter Description Default max_dispatchers Maximum number of dispatcher threads, which hand off requests to the task threads. 1 max_task_threads Maximum number of "worker bee" threads 2 max_sessions Maximum number of Oracle sessions that can be serviced by the multithreaded extproc process 5 listener_address Addresses with which the multithreaded process "registers" with an already-running listener. (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC) (KEY=PNPKEY)) (ADDRESS= (PROTOCOL=IPC) (KEY=listenerSID)) (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521))) By the way, while testing this feature, I discovered that whenever I bounced the listener, afterwards I needed to bounce the agent as well. Fortunately, the agtctl utility kindly remembers any parameter adjustments you have made from the default values. Some experimentation may be needed to optimize the agent-specific parameters against the number of agent processes. While I have not experimented enough with multithreaded agents to offer any rules of thumb, let's at least take a look at the changes required to use two multithreaded agents. Follow the steps given earlier, but this time, in Step 7, you will start two agents with unique names: OS> agtctl startup extproc PLSExtProc_001 ... OS> agtctl startup extproc PLSExtProc_002 You must also modify tnsnames.ora (Step 3 above) to be aware of these new agent names; because you probably want Oracle Net to "load balance" across the agents, edit the EXTPROC_CONNECTION_DATA section of tnsnames.ora to be: EXTPROC_CONNECTION_DATA = (DESCRIPTION_LIST = (LOAD_BALANCE = TRUE) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY)) (CONNECT_DATA = (SID = PLSExtProc_001)(PRESENTATION = RO)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc)(key = PNPKEY)) (CONNECT_DATA = (SID = PLSExtProc_002) (PRESENTATION = RO)) ) ) You need to add the DESCRIPTION_LIST parameter and include one description section for each of the agents. With this new configuration, each time Oracle Net receives a request from PL/SQL to connect to an external procedure, Oracle Net will randomly connect to one of the agents listed; if the connection fails (for example, because it already has the maximum number of sessions connected), Oracle Net will try the other agent, until it either makes a successful connection or fails to connect to any of them. Such a failure will result in ORA-28575: unable to open RPC connection to external procedure agent. You can read more about multithreaded mode and agtctl in Oracle's Application Development GuideFundamentals and the Heterogenous Connectivity Administrator's Guide. Oracle Net's load balancing features are described in the Net Services Administrator's Guide and the Net Services Reference. One final point: when using multithreaded agents, the C program that implements an external procedure must be thread-safe, and writing such a beasty is not necessarily trivial. See the notes at the end of this chapter for a few of the caveats.