Shared Server Notes -------------------------------------------------------------------------------- * Change from dedicated to shared server config 9i Required: alter system set dispatchers = '(protocol=TCP)(dispatchers=5)'; alter system set dispatchers = 'TCP,5'; --Oracle7 syntax, not recommended but still works Optional but recommended: alter system set shared_servers = 5; For initial setup, you must edit pfile (and recreate spfile as needed). Please note that documentation says "You can modify the settings for DISPATCHERS and SHARED_SERVERS dynamically". This is very misleading! The initialization parameter dispatchers (let me use lower-case for it) can NOT be ADDed dynamically. Or you would get ORA-105 ("dispatching mechanism not configured for network protocol %s"). Attributes of this initialization parameter, such as DISPATCHERS (the count of them, as 5 in the example above; let me use upper-case for it), HOST, or TCP, CAN be changed dynamically i.e. by alter system. That's probably what documentation means. Note that the alter system command must not have "(address=..." part. This works: alter system set dispatchers = '(host=myhostname)(protocol=tcp)(dispatchers=5)' This throws ORA-105: alter system set dispatchers = '(address=(host=myhostname)(protocol=tcp))(dispatchers=5)'; After a client connection through Oracle Net (SQL*Net): v$circuit should show one row for this connection select * from v$dispatcher; lsnrctl service should show some Dxxx handlers with 1 connection, 1 active. v$session.server should be 'SHARED' or 'NONE' depending on whether the session is active (current session selecting from v$session is of course active). 10g Only need to set shared_servers: alter system set shared_servers = 5; To shutdown: alter system set dispatchers = ''; (not working in 9i) alter system set shared_servers = 0; -------------------------------------------------------------------------------- * lsnrctl service still only shows dedicated services Waited 1 minute or alter system register? If still only dedicated, are there two versions of Oracle installed? If so, start the higher version listener. -------------------------------------------------------------------------------- * v$session_wait.p1 If event is 'SQL*Net message from client' (or 'null even' in earlier versions of 9i), driver ID p1 may be 1297371904 and p1raw 4D545300. That stands for 'M', 'T', 'S'. -------------------------------------------------------------------------------- * shared_servers param If you alter system set shared_servers = 0, you still have 1 row in v$shared_server. If you set it to larger than max_shared_servers, you get ORA-00111: not all servers started because number of servers is limited to , and v$shared_server has entries. Once the number goes up, even if you set shared_servers = 0, the number doens't immediately come down (probably 30 seconds as default set by _pmon_load_constants). alter system register helps (v$shared_server.status changes to 'QUIT' and then 'TERMINATED'). -------------------------------------------------------------------------------- * Shutdown dispatcher alter system shutdown immediate 'd000' works. Interestingly, if the dispatcher is connecting your current session, you succeed in shutting it down but will get ORA-3113. Would it be nice if you were warned as in the case of alter system kill session '' (where you get ORA-00027: cannot kill current session)? But it may be technically difficult to implement, because when you run alter system kill, you're on the shared server, and Oracle may not know which dispatcher you're come back to once that active work of killing session is done. -------------------------------------------------------------------------------- * Force session to migrate to a new shared server There's no documented way and may be useful only for testing purposes. You can try event 10258 (force shared servers to be chosen round-robin). Bug 4926388 has it set at level 1.[note] Unfortunately, this event has to be set in init.ora (or spfile) and bouncing the instance is required. Alternatively, even more undocumented, you can use an OS tool to "lock" the current shared server and then submit a SQL (or even DESC SomeTable) in the session, which waits for the shared server to respond in vain and connects to a new shared server. The way to lock a shared server can be using a debugger to attach to it (don't forget to release when you're done), or kill -STOP (don't forget to kill -CONT ). [note] Different levels of event 10258 seem to mean very difference things. Level 1 is as the description claims (force round-robin). Level 2 may be half- duplex violation check (see Bug:3671563). Many people use level 4 to stop pmon auto-registration with listener. -------------------------------------------------------------------------------- * What is v$reqdist? Reference manual says "This view lists statistics for the histogram of shared server dispatcher request times, divided into 12 buckets, or ranges of time". What is "shared server dispatcher request"? In fact, this view breaks down into a 12-bucket histogram the service times of shared servers (not dispatchers). Reference says "maximum time for each bucket is (4 * 2^N)/100 seconds". So for the 12 buckets, Bucket service time number in seconds ------ ------------ 0 <= .04 1 <= .08 (I've never seen non-0 count for this bucket!) 2 <= .16 3 <= .32 4 <= .64 5 <= 1.28 6 <= 2.56 7 <= 5.12 8 <= 10.24 9 <= 20.48 10 <= 40.96 11 > 40.96 (not <= 81.92!) If your shared server finishes a client request within .04 seconds, your bucket number 0 should have count incremented by 1. If you run dbms_lock.sleep(15), bucket 9 should have 1 more count: SQL> select * from v$reqdist where count > 0; BUCKET COUNT ---------- ---------- 0 25 9 1 If you have abnormally high numbers in high number buckets, the clients may as well connect in dedicated mode. -------------------------------------------------------------------------------- * What is DIRECT_HANDOFF_TTC_? http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96531/ch3_9ir1.htm#72856 --- Begin Quote --- Oracle Net Connect Establishment (Direct Handoff) With Oracle9i, if the Oracle Net protocol and operating system allow it, the Oracle Net Listener hands off a connection request directly to a local dispatcher for all presentation protocols. This process is done without redirection of the connection request from listener back to the client. The client is connected directly to the dispatcher. The listener can resume listening for other incoming network sessions. This new feature improves overall latency by eliminating the need for a network round-trip for the "redirect." In addition, it improves connection establishment performance in Wide Area Network (WAN) environments in which the cost of redirecting the client connection could be significant." --- End Quote --- Assume the listener listens on port 1521 and a dispatcher on 40000. If this parameter is not set, direct handoff takes place. The listener passes or "hands-off" the client's network connection to the dispatcher. This handoff means the final network connection is made from the client to the dispatcher magically on *port 1521*! (Why the dispatcher and listener both can use the same port is a separate question. It's because UNIX can pass file descriptors between processes. See Richard Stevens's books for details. On BSD UNIX, it uses sendmsg() call while on Solaris, it uses a UNIX domain socket. That's why on Solaris, after the connection is made, you'll see a UNIX domain socket created in `pfiles ' output, in addition to the new file descriptor passed in by the listener.) When this parameter is set to off (perhaps to avoid bugs with Oracle support's advice), the listener tells the client to make another connection, this time to port 40000, where a dispatcher listens. Once it's done, the server side of this connection stays at port 40000. This second connection may not work if for instance there's a firewall that blocks high number ports such as 40000. In that case, the client will get a mysterious error such as ORA-12545:Connect failed because target host or object does not exist (seen on Windows) or ORA-12535: TNS:operation timed out (seen on Linux, after timeout) This parameter can be changed by lsnrctl reload. -------------------------------------------------------------------------------- * Difference between alter system kill session and disconnect session SQL References says "Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Server)." Under the IMMEDIATE bullet, "If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions, then this clause has the same effect as described for KILL SESSION IMMEDIATE." Two points here: Disconnection session kills the circuit, not the shared server or dispatcher server process; disconnect session immediate (without post_transaction) is the same as kill session. However, if you test this with shared server config, the above is not quite true. Session 1 (make sure server='SHARED'): select sid, serial#, server from v$session where sid in (select sid from v$mystat); @sillySQL.sql where sillySQL.sql has these lines: set term off serveroutput on select count(*) from dba_tables, dba_tables; exec dbms_output.put_line('You were too slow in session 2!'); Session 2: alter system disconnect session ', ' immediate; --alter system kill session ', '; --alter system kill session ', ' immediate; You'll find that you can send the disconnect session command in session 2 and get prompt back immediately, but session 1 keeps going. If you send kill session command, it hangs for a second or two and session 1 is really killed. The silly SQL has count(*) so that there's no need for session 1 to send intermediate results to the client. If there was, the disconnect session command would slip in and disconnect or kill it. A big surprise is when you kill session with immediate option. It seems to be equivalent to disconnect session immediate, or in other words, NOT immediately killing (disconnecting) the session. -------------------------------------------------------------------------------- * DCD complications See ./DCD.txt