Configure Mts Oracle
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Configure Mts Oracle as PDF for free.
More details
- Words: 3,087
- Pages: 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Configuring Shared Server (MTS) A true story: the first time I taught the Oracle Networking course, my class spent nearly 4 hours trying to get Multi-Threaded Server configuration working. And not a single student (still less the Instructor, yours truly) could get the damn thing to work. Then one of the students hit the space bar when editing his tnsnames.ora, and Lo! Multi-Threaded Server configuration suddenly sprang into life!! Fortunately, that was back in the bad old days of Oracle 8.1.5, and things have got steadily easier since then, with much less sensitivity to the odd space here and there. However, I mention this story to advise you that if Multi-Threaded Server (or Shared Server as it is now known in 9i) refuses to work, stick with it… check your syntax extremely carefully. Given enough fiddling (and patience) it can be made to work. For the record, I run my test-bed 9i database at home in dedicated server mode. Just prior to starting this paper, I switched to Shared Server configuration. It was working within three minutes.
Configuring the init.ora Shared Server can be made to work with just two additions to your init.ora. First, DISPATCHERS needs to be set to the number of dispatchers you want spawned when the Instance starts up, and the networking protocol you want them to work on. Second, SHARED_SERVERS needs to be set to the number of Server Processes you want spawned and ready in the pool of shared Server Processes when the Instance starts up. And that’s all there is to it! With some slight provisos, naturally!! First, those parameter names are the 9i versions. In 8i or before, they are called MTS_DISPATCHERS and MTS_SERVERS respectively. To confuse the issue even more, if in 9i you do a show parameter dispatcher, you’ll see listed both the new parameters and the old ones: dispatchers mts_dispatchers
string string
(PROTOCOL=TCP)(DISPATCHERS=2) (PROTOCOL=TCP)(DISPATCHERS=2)
Likewise, both the new and the old SERVER parameters are available in 9i. For the hell of it, I’ve tested 9i using the old syntax, and it works perfectly well –but you are strongly advised to stick to the new parameter names, because the old ones are only there for backwards compatibility, and there’s no telling when exactly in the future they will cease to function.
Copyright © Howard Rogers 2002
17/03/2002
Page 1 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
To stress the point, Oracle will give you the following stern-looking warning when you start up after adding these parameters into your init.ora: SQL> startup force pfile=d:\oracle\admin\db9\pfile\init.ora ORA-32006: MTS_DISPATCHERS initialization parameter has been deprecated ORA-32006: MTS_SERVERS initialization parameter has been deprecated ORACLE instance started. Total System Global Area 93089624 bytes Fixed Size 282456 bytes Variable Size 58720256 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened.
As you can see, the database does proceed to start up perfectly normally, the key point being that the parameters are “deprecated” not actually “obsolete”. The other thing I should mention at this point is: what are reasonable values for the number of Dispatchers and shared Server Processes? Good question, though outside the scope of this paper: you might care to read my paper “How do I know the right number of Dispatchers and Servers to start?” for a detailed answer. Finally, I’ll mention some additional init.ora parameters that can be set at this point, though they are optional. MAX_DISPATCHERS sets a hard limit to the number of Dispatchers than can run concurrently. If not set, this defaults to 5. If you subsequently discover that the number of Users connecting to your database is causing contention for the existing number of Dispatchers, you can start additional ones, up to this hard limit. Allowing the default of 5 to be set is therefore restricting your tuning options, so you might want to think about explicitly setting a rather higher limit to give yourself some future flexibility. (in 8i and before, this parameter was called MTS_MAX_DISPATCHERS). MAX_SHARED_SERVERS does a similar sort of job for the number of shared Server Processes that are allowed to be running concurrently. Oracle will automatically detect contention for the existing pool of Shared Server processes, and will spawn additional ones as it sees fit (and kill of unnecessary ones, too, if the load on the system decreases). But it can only spawn extra processes up to this hard limit. Again, if you choose not to set this parameter explicitly, there is a default setting –either 20, or twice whatever SHARED_SERVERS was set to, whichever is the higher. Those seem quite reasonable defaults to me, so there is perhaps less need to set this one yourself… though, of course, it’s there for future tuning use as necessary. In 8i and before, this parameter was called MTS_MAX_SERVERS. There are other init.ora parameters that can be set, but these are the ones most closely related to getting Shared Server working in the first place. I’ll discuss the others at the end of this paper. Copyright © Howard Rogers 2002
17/03/2002
Page 2 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Configuring the tnsnames.ora You shouldn’t actually need to do anything to your tnsnames.ora. By default, if there are Dispatchers running when a User requests a connection to an Instance, they are connected to a Dispatcher by the Listener –and hence are immediately making use of a Shared Server connection. On the other hand, it’s usually good advice to make your connection choices explicit, rather than rely on slightly mysterious Oracle defaults. And if there seems to be some difficulty getting Shared Server configuration to work, a bit of light editing of the tnsnames.ora can’t do any harm! Probably, your tnsnames.ora looks a bit like this: DB9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) ) )
What you can do is to edit it so that it looks like this: DB9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) (SERVER=SHARED) ) )
The one addition is the line I’ve highlighted in bold. It explicitly demands that a Shared Server connection be made. There is a drawback to explicitly setting this parameter: if, for some reason, the Instance is started without any Dispatchers, then a User request to connect will simply fail: his tnsnames.ora is demanding a connection to a Dispatcher, and there aren’t any. Had that line been left out, as in the original version of the file, a User request to connect would have caused the Listener to spawn a dedicated Server Process for him in the absence of any Dispatchers. Should you explicitly demand shared connections, then? It’s up to you: I always do, on the grounds that I’d rather I couldn’t connect at all than make the wrong sort of connection, but your mileage might well vary. Copyright © Howard Rogers 2002
17/03/2002
Page 3 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
On the other hand, there is another edit to the tnsnames.ora that I always make, and it results in a tnsnames that looks like this: DB9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) (SERVER=SHARED) ) ) DB9DBA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) (SERVER=DEDICATED) ) )
In short, I copy the entire original contents, and paste them underneath. I then modify the service alias (the “DB9 = ” bit), and make it clear that the new alias is for DBA use. Hence the new alias is, in this case, “DB9DBA”. Finally, I make an explicit request that the new alias connects using a dedicated Server Process. This is actually quite an important thing to do. The benefits of Shared Server configuration only start to really make themselves felt when Users are performing short, sharp transactions …and a dedicated Server Process would then have to sit around idle waiting for them to submit their next one. In Shared Server configuration, that User idle time is used by the Server Process to service other User’s requests. If, however, your Users are in the habit of firing off an enormous transaction, what that means in Shared Server configuration is that they’ve effectively tied up a Server Process in exclusive mode –and thus other Users have less processes around to service their requests. Performance degrades all round as a result. Now, your typical DBA is likely to connect to the database to perform administrative tasks which, by their nature, are lengthy (the rebuild of an index, for example, or the collection of statistics on a table). You need a dedicated Server Process to perform such tasks if you are not to effectively shrink the pool of processes available to everyone else. So this additional service alias is a useful way of not clogging up a Shared Server configuration. You should consider allowing any User that needs to perform a long-running transactions (greater than around 10 seconds or so) to connect to a primarily Shared Server database in dedicated mode. And this is the way to do it.
Copyright © Howard Rogers 2002
17/03/2002
Page 4 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
How do you know it’s working? Apart from the edits to the init.ora and the tnsnames.ora I’ve just mentioned, there’s really nothing else to configure. Obviously, any edit to the init.ora requires that you restart your Instance to pick up the new settings. (Incidentally, a word of warning in 9i: it’s no good editing your init.ora and then issuing a bare “startup” command if you happen to have previously created an spfile. The spfile takes precedence over an init.ora, so the net effect would be… nothing. You may need, therefore, to explicitly specify that the startup sequence should use the init.ora, and then create a new spfile from the init.ora once the database comes back up, so that future startups use Shared Server configuration). But, given the Instance bounce, how do you then know whether Shared Server is working properly? There a couple of things to check. First, you need to make sure that the Dispatchers have been spawned and have registered themselves with the Listener. From the command line, you can run the lsnrctl tool to check this: C:\Documents and Settings\Administrator>lsnrctl services LSNRCTL for 32-bit Windows: Version 9.0.1.1.1 - Production on 17-MAR-2002 12:57:44 Copyright (c) 1991, 2001, Oracle Corporation.
All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mozart)(PORT=1521))) Services Summary... Service "db9.aldeburgh.local" has 3 instance(s). Instance "db9", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Instance "db9", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:6 refused:0 state:ready LOCAL SERVER Instance "db9", status READY, has 3 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 state:ready LOCAL SERVER "D001" established:2 refused:0 current:1 max:1002 state:ready DISPATCHER <machine: MOZART, pid: 1564> (ADDRESS=(PROTOCOL=tcp)(HOST=mozart.aldeburgh.local)(PORT=4199)) "D000" established:2 refused:0 current:0 max:1002 state:ready DISPATCHER <machine: MOZART, pid: 1920> (ADDRESS=(PROTOCOL=tcp)(HOST=mozart.aldeburgh.local)(PORT=4197)) The command completed successfully
I’ve again highlighted the important lines in bold. All Dispatchers have a process name of “Dxxx”, where “xxx” is just a monotonically incrementing sequence number starting at zero. So those two highlighted lines show me that two Dispatchers have been started –and since this is the Listener Control utility we’re running, it’s obvious that they must have registered themselves with the Listener. Copyright © Howard Rogers 2002
17/03/2002
Page 5 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
However, just having Dispatchers at the ready isn’t enough: remember that it’s perfectly possible for a Shared Server Instance to allow dedicated connections. So… what I then do is go to a client machine, and issue the standard connection commands. Once the User has connected, if you then query V$CIRCUIT and get a row returned, that’s a guarantee that a Shared Server connection has been made. Dedicated Server connections don’t cause rows to be added to V$CIRCUIT. Using the tnsnames.ora I showed you earlier, it’s easy enough to prove this point: SQL> connect system/manager@db9 Connected. SQL> select count(*) from v$circuit; COUNT(*) ---------1 SQL> connect system/manager@db9dba Connected. SQL> select count(*) from v$circuit; COUNT(*) ---------0
You’ll recall that the alias “db9” demanded a shared connection, but the alias “db9dba” demanded a dedicated connection. When the shared connection is made, V$CIRCUIT shows some positive row count. Dedicated connections just leave V$CIRCUIT at zero, however. There’s another test I sometimes do, just to remind myself what Shared Server configuration is really all about: count the number of processes as, on client machines, I make more and more connections. Shared Server configuration should show no increase in the number of processes as additional Users connect (which is the whole point, of course). Dedicated connections, however, cause a new dedicated Server Process to be spawned, so the process count should increase by one as each new connection is made. SQL> connect system/manager@db9 Connected. SQL> select count(*) from v$process union select count(*) from v$session; COUNT(*) ---------7 13 SQL> connect system/manager@db9dba Connected. SQL> select count(*) from v$process union select count(*) from v$session; COUNT(*) ---------7 14 Copyright © Howard Rogers 2002
17/03/2002
Page 6 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Here we connect first in what we hope is Shared Server mode. We have a count of 7 sessions, requiring 13 processes. Then we connect in what is supposed to be dedicated mode. The same 7 sessions now require 14 processes –the additional process being, of course, the Server Process that the dedicated connection request caused to be spawned.
Other Init.ora Parameters I mentioned earlier that there are other parameters that may need to be altered in the init.ora. None of them are actually required to get Shared Server configuration working, but they may be needed to get Shared Server configuration working well. LARGE_POOL_SIZE:
This one is actually very important. If you don’t set it, then the memory that, in Dedicated configuration, would have been part of the PGA is now stolen from the SGA, and in particular the Library Cache of the Shared Pool. That will probably cause fragmentation of the Shared Pool, and excessive ageing out of parsed SQL statements (requiring excessive amounts of reparsing). If a Large Pool is configured, however, the relevant bits of the PGA take their memory requirements from that pool, leaving the Shared Pool largely untouched. I say “largely” untouched, because even with a Large Pool, each User connection requires about 10K from the Shared Pool. You probably also want to boost your existing SHARED_POOL_SIZE by (10K*the expected number of concurrent Users) as a result.
CIRCUITS:
As we’ve seen, each Shared Server connection increments the row count of V$CIRCUIT by one. This parameter sets a hard limit on the number of concurrent circuits (that is, concurrent Shared Server connections) that are permitted on the Instance. If you don’t set this, then the value of the SESSIONS parameter is assumed to be the maximum number of circuits that are permitted. (Called MTS_CIRCUITS in 8i and earlier).
SESSIONS:
The maximum number of concurrent sessions, whether dedicated or shared that are permitted on the Instance. The default appears to be 170.
SHARED_SERVER_SESSIONS: The maximum number of concurrent Shared Server connections
that are permitted on the Instance. Dedicated connections don’t count towards this total. If this parameter is not set, it defaults to SESSIONS-5, which means that Oracle is assuming that 5 dedicated sessions will be needed on a primarily Shared Server Instance, and thus reserves those expected sessions. (Called MTS_SESSIONS in 8i and earlier). Copyright © Howard Rogers 2002
17/03/2002
Page 7 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Advanced init.ora parameters I’m not going to go into detail here, but there are things you can do to some of the init.ora parameters I’ve already mentioned which get us into the realm of advanced Shared Server configurations. The main culprit is the DISPATCHERS init.ora parameter. That can take a number of additional parameters, above and beyond the two we mentioned earlier. So far, we’ve got this: DISPATCHERS=”(PROTOCOL=TCP)(DISPATCHERS=2)”
…which means ‘create me 2 dispatchers, both working on the TCP/IP protocol. There’s nothing to stop you doing this: DISPATCHERS=”(PROTOCOL=TCP)(DISPATCHERS=2)” DISPATCHERS=”(PROTOCOL=IPC)(DISPATCHERS=1)” DISPATCHERS=”(PROTOCOL=TCPS)(DISPATCHERS=3)”
… which means you’ll have 6 Dispatchers, 2 working on TCP/IP, 3 working on secure TCP/IP and 1 on the IPC protocol. To have multi-protocol Dispatchers, however, requires that you configure your Listener to listen on the same protocols. You can force the Dispatchers to work with non-default Listeners, too. That would look like this: DISPATCHERS=”(PROTOCOL=TCP)(DISPATCHERS=2)(LISTENER=XYZ)”
That “XYZ” is actually a service name alias, meaning that there needs to be a server-side tnsnames.ora file to resolve that into a location (host and port number) where a nondefault Listener can be found. The 2 Dispatchers thus configured will register themselves with this Listener, rather than the default one found on the Server. You’ll need to configure like this if you’ve chosen not to have your Listener listening on the standard 1521 port, for example. Two other variations to the DISPATCHERS parameter spring to mind: Multiplexing and Connection Pooling. To configure those, you’d end up with this: DISPATCHERS=”(PROTOCOL=TCP)(DISPATCHERS=2)(MULTIPLEX=ON)(POOL=ON)”
Session Multiplexing is a feature of Connection Manager, whereby many Users make separate connections to a Connection Manager server, and it then establishes a single connection to the back-end database. That spares your back-end server from having to support thousands of individual direct connections, and compromising performance as a result.
Copyright © Howard Rogers 2002
17/03/2002
Page 8 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Connection Pooling is a way of sort-of disconnecting an idle User, thus making his connection circuit available for someone else, and then automatically re-connecting him when he starts performing real work again (this is very commonly used when you’re dealing with web connections to the database). In this way, a fixed number of available circuits can actually be made to service the requests from many more real Users than you might have expected. All of these features are fiendishly clever, but this is not the place to start discussing the intricacies of their configuration. At this stage, I simply wanted to point out that, if you need to, there’s far more to Shared Server configurations than just getting it working. Still: if you managed to get it working at all, congratulate yourself!
Copyright © Howard Rogers 2002
17/03/2002
Page 9 of 9
Networking Tips
Configuring Shared Server (MTS) A true story: the first time I taught the Oracle Networking course, my class spent nearly 4 hours trying to get Multi-Threaded Server configuration working. And not a single student (still less the Instructor, yours truly) could get the damn thing to work. Then one of the students hit the space bar when editing his tnsnames.ora, and Lo! Multi-Threaded Server configuration suddenly sprang into life!! Fortunately, that was back in the bad old days of Oracle 8.1.5, and things have got steadily easier since then, with much less sensitivity to the odd space here and there. However, I mention this story to advise you that if Multi-Threaded Server (or Shared Server as it is now known in 9i) refuses to work, stick with it… check your syntax extremely carefully. Given enough fiddling (and patience) it can be made to work. For the record, I run my test-bed 9i database at home in dedicated server mode. Just prior to starting this paper, I switched to Shared Server configuration. It was working within three minutes.
Configuring the init.ora Shared Server can be made to work with just two additions to your init.ora. First, DISPATCHERS needs to be set to the number of dispatchers you want spawned when the Instance starts up, and the networking protocol you want them to work on. Second, SHARED_SERVERS needs to be set to the number of Server Processes you want spawned and ready in the pool of shared Server Processes when the Instance starts up. And that’s all there is to it! With some slight provisos, naturally!! First, those parameter names are the 9i versions. In 8i or before, they are called MTS_DISPATCHERS and MTS_SERVERS respectively. To confuse the issue even more, if in 9i you do a show parameter dispatcher, you’ll see listed both the new parameters and the old ones: dispatchers mts_dispatchers
string string
(PROTOCOL=TCP)(DISPATCHERS=2) (PROTOCOL=TCP)(DISPATCHERS=2)
Likewise, both the new and the old SERVER parameters are available in 9i. For the hell of it, I’ve tested 9i using the old syntax, and it works perfectly well –but you are strongly advised to stick to the new parameter names, because the old ones are only there for backwards compatibility, and there’s no telling when exactly in the future they will cease to function.
Copyright © Howard Rogers 2002
17/03/2002
Page 1 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
To stress the point, Oracle will give you the following stern-looking warning when you start up after adding these parameters into your init.ora: SQL> startup force pfile=d:\oracle\admin\db9\pfile\init.ora ORA-32006: MTS_DISPATCHERS initialization parameter has been deprecated ORA-32006: MTS_SERVERS initialization parameter has been deprecated ORACLE instance started. Total System Global Area 93089624 bytes Fixed Size 282456 bytes Variable Size 58720256 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened.
As you can see, the database does proceed to start up perfectly normally, the key point being that the parameters are “deprecated” not actually “obsolete”. The other thing I should mention at this point is: what are reasonable values for the number of Dispatchers and shared Server Processes? Good question, though outside the scope of this paper: you might care to read my paper “How do I know the right number of Dispatchers and Servers to start?” for a detailed answer. Finally, I’ll mention some additional init.ora parameters that can be set at this point, though they are optional. MAX_DISPATCHERS sets a hard limit to the number of Dispatchers than can run concurrently. If not set, this defaults to 5. If you subsequently discover that the number of Users connecting to your database is causing contention for the existing number of Dispatchers, you can start additional ones, up to this hard limit. Allowing the default of 5 to be set is therefore restricting your tuning options, so you might want to think about explicitly setting a rather higher limit to give yourself some future flexibility. (in 8i and before, this parameter was called MTS_MAX_DISPATCHERS). MAX_SHARED_SERVERS does a similar sort of job for the number of shared Server Processes that are allowed to be running concurrently. Oracle will automatically detect contention for the existing pool of Shared Server processes, and will spawn additional ones as it sees fit (and kill of unnecessary ones, too, if the load on the system decreases). But it can only spawn extra processes up to this hard limit. Again, if you choose not to set this parameter explicitly, there is a default setting –either 20, or twice whatever SHARED_SERVERS was set to, whichever is the higher. Those seem quite reasonable defaults to me, so there is perhaps less need to set this one yourself… though, of course, it’s there for future tuning use as necessary. In 8i and before, this parameter was called MTS_MAX_SERVERS. There are other init.ora parameters that can be set, but these are the ones most closely related to getting Shared Server working in the first place. I’ll discuss the others at the end of this paper. Copyright © Howard Rogers 2002
17/03/2002
Page 2 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Configuring the tnsnames.ora You shouldn’t actually need to do anything to your tnsnames.ora. By default, if there are Dispatchers running when a User requests a connection to an Instance, they are connected to a Dispatcher by the Listener –and hence are immediately making use of a Shared Server connection. On the other hand, it’s usually good advice to make your connection choices explicit, rather than rely on slightly mysterious Oracle defaults. And if there seems to be some difficulty getting Shared Server configuration to work, a bit of light editing of the tnsnames.ora can’t do any harm! Probably, your tnsnames.ora looks a bit like this: DB9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) ) )
What you can do is to edit it so that it looks like this: DB9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) (SERVER=SHARED) ) )
The one addition is the line I’ve highlighted in bold. It explicitly demands that a Shared Server connection be made. There is a drawback to explicitly setting this parameter: if, for some reason, the Instance is started without any Dispatchers, then a User request to connect will simply fail: his tnsnames.ora is demanding a connection to a Dispatcher, and there aren’t any. Had that line been left out, as in the original version of the file, a User request to connect would have caused the Listener to spawn a dedicated Server Process for him in the absence of any Dispatchers. Should you explicitly demand shared connections, then? It’s up to you: I always do, on the grounds that I’d rather I couldn’t connect at all than make the wrong sort of connection, but your mileage might well vary. Copyright © Howard Rogers 2002
17/03/2002
Page 3 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
On the other hand, there is another edit to the tnsnames.ora that I always make, and it results in a tnsnames that looks like this: DB9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) (SERVER=SHARED) ) ) DB9DBA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) (SERVER=DEDICATED) ) )
In short, I copy the entire original contents, and paste them underneath. I then modify the service alias (the “DB9 = ” bit), and make it clear that the new alias is for DBA use. Hence the new alias is, in this case, “DB9DBA”. Finally, I make an explicit request that the new alias connects using a dedicated Server Process. This is actually quite an important thing to do. The benefits of Shared Server configuration only start to really make themselves felt when Users are performing short, sharp transactions …and a dedicated Server Process would then have to sit around idle waiting for them to submit their next one. In Shared Server configuration, that User idle time is used by the Server Process to service other User’s requests. If, however, your Users are in the habit of firing off an enormous transaction, what that means in Shared Server configuration is that they’ve effectively tied up a Server Process in exclusive mode –and thus other Users have less processes around to service their requests. Performance degrades all round as a result. Now, your typical DBA is likely to connect to the database to perform administrative tasks which, by their nature, are lengthy (the rebuild of an index, for example, or the collection of statistics on a table). You need a dedicated Server Process to perform such tasks if you are not to effectively shrink the pool of processes available to everyone else. So this additional service alias is a useful way of not clogging up a Shared Server configuration. You should consider allowing any User that needs to perform a long-running transactions (greater than around 10 seconds or so) to connect to a primarily Shared Server database in dedicated mode. And this is the way to do it.
Copyright © Howard Rogers 2002
17/03/2002
Page 4 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
How do you know it’s working? Apart from the edits to the init.ora and the tnsnames.ora I’ve just mentioned, there’s really nothing else to configure. Obviously, any edit to the init.ora requires that you restart your Instance to pick up the new settings. (Incidentally, a word of warning in 9i: it’s no good editing your init.ora and then issuing a bare “startup” command if you happen to have previously created an spfile. The spfile takes precedence over an init.ora, so the net effect would be… nothing. You may need, therefore, to explicitly specify that the startup sequence should use the init.ora, and then create a new spfile from the init.ora once the database comes back up, so that future startups use Shared Server configuration). But, given the Instance bounce, how do you then know whether Shared Server is working properly? There a couple of things to check. First, you need to make sure that the Dispatchers have been spawned and have registered themselves with the Listener. From the command line, you can run the lsnrctl tool to check this: C:\Documents and Settings\Administrator>lsnrctl services LSNRCTL for 32-bit Windows: Version 9.0.1.1.1 - Production on 17-MAR-2002 12:57:44 Copyright (c) 1991, 2001, Oracle Corporation.
All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mozart)(PORT=1521))) Services Summary... Service "db9.aldeburgh.local" has 3 instance(s). Instance "db9", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Instance "db9", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:6 refused:0 state:ready LOCAL SERVER Instance "db9", status READY, has 3 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 state:ready LOCAL SERVER "D001" established:2 refused:0 current:1 max:1002 state:ready DISPATCHER <machine: MOZART, pid: 1564> (ADDRESS=(PROTOCOL=tcp)(HOST=mozart.aldeburgh.local)(PORT=4199)) "D000" established:2 refused:0 current:0 max:1002 state:ready DISPATCHER <machine: MOZART, pid: 1920> (ADDRESS=(PROTOCOL=tcp)(HOST=mozart.aldeburgh.local)(PORT=4197)) The command completed successfully
I’ve again highlighted the important lines in bold. All Dispatchers have a process name of “Dxxx”, where “xxx” is just a monotonically incrementing sequence number starting at zero. So those two highlighted lines show me that two Dispatchers have been started –and since this is the Listener Control utility we’re running, it’s obvious that they must have registered themselves with the Listener. Copyright © Howard Rogers 2002
17/03/2002
Page 5 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
However, just having Dispatchers at the ready isn’t enough: remember that it’s perfectly possible for a Shared Server Instance to allow dedicated connections. So… what I then do is go to a client machine, and issue the standard connection commands. Once the User has connected, if you then query V$CIRCUIT and get a row returned, that’s a guarantee that a Shared Server connection has been made. Dedicated Server connections don’t cause rows to be added to V$CIRCUIT. Using the tnsnames.ora I showed you earlier, it’s easy enough to prove this point: SQL> connect system/manager@db9 Connected. SQL> select count(*) from v$circuit; COUNT(*) ---------1 SQL> connect system/manager@db9dba Connected. SQL> select count(*) from v$circuit; COUNT(*) ---------0
You’ll recall that the alias “db9” demanded a shared connection, but the alias “db9dba” demanded a dedicated connection. When the shared connection is made, V$CIRCUIT shows some positive row count. Dedicated connections just leave V$CIRCUIT at zero, however. There’s another test I sometimes do, just to remind myself what Shared Server configuration is really all about: count the number of processes as, on client machines, I make more and more connections. Shared Server configuration should show no increase in the number of processes as additional Users connect (which is the whole point, of course). Dedicated connections, however, cause a new dedicated Server Process to be spawned, so the process count should increase by one as each new connection is made. SQL> connect system/manager@db9 Connected. SQL> select count(*) from v$process union select count(*) from v$session; COUNT(*) ---------7 13 SQL> connect system/manager@db9dba Connected. SQL> select count(*) from v$process union select count(*) from v$session; COUNT(*) ---------7 14 Copyright © Howard Rogers 2002
17/03/2002
Page 6 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Here we connect first in what we hope is Shared Server mode. We have a count of 7 sessions, requiring 13 processes. Then we connect in what is supposed to be dedicated mode. The same 7 sessions now require 14 processes –the additional process being, of course, the Server Process that the dedicated connection request caused to be spawned.
Other Init.ora Parameters I mentioned earlier that there are other parameters that may need to be altered in the init.ora. None of them are actually required to get Shared Server configuration working, but they may be needed to get Shared Server configuration working well. LARGE_POOL_SIZE:
This one is actually very important. If you don’t set it, then the memory that, in Dedicated configuration, would have been part of the PGA is now stolen from the SGA, and in particular the Library Cache of the Shared Pool. That will probably cause fragmentation of the Shared Pool, and excessive ageing out of parsed SQL statements (requiring excessive amounts of reparsing). If a Large Pool is configured, however, the relevant bits of the PGA take their memory requirements from that pool, leaving the Shared Pool largely untouched. I say “largely” untouched, because even with a Large Pool, each User connection requires about 10K from the Shared Pool. You probably also want to boost your existing SHARED_POOL_SIZE by (10K*the expected number of concurrent Users) as a result.
CIRCUITS:
As we’ve seen, each Shared Server connection increments the row count of V$CIRCUIT by one. This parameter sets a hard limit on the number of concurrent circuits (that is, concurrent Shared Server connections) that are permitted on the Instance. If you don’t set this, then the value of the SESSIONS parameter is assumed to be the maximum number of circuits that are permitted. (Called MTS_CIRCUITS in 8i and earlier).
SESSIONS:
The maximum number of concurrent sessions, whether dedicated or shared that are permitted on the Instance. The default appears to be 170.
SHARED_SERVER_SESSIONS: The maximum number of concurrent Shared Server connections
that are permitted on the Instance. Dedicated connections don’t count towards this total. If this parameter is not set, it defaults to SESSIONS-5, which means that Oracle is assuming that 5 dedicated sessions will be needed on a primarily Shared Server Instance, and thus reserves those expected sessions. (Called MTS_SESSIONS in 8i and earlier). Copyright © Howard Rogers 2002
17/03/2002
Page 7 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Advanced init.ora parameters I’m not going to go into detail here, but there are things you can do to some of the init.ora parameters I’ve already mentioned which get us into the realm of advanced Shared Server configurations. The main culprit is the DISPATCHERS init.ora parameter. That can take a number of additional parameters, above and beyond the two we mentioned earlier. So far, we’ve got this: DISPATCHERS=”(PROTOCOL=TCP)(DISPATCHERS=2)”
…which means ‘create me 2 dispatchers, both working on the TCP/IP protocol. There’s nothing to stop you doing this: DISPATCHERS=”(PROTOCOL=TCP)(DISPATCHERS=2)” DISPATCHERS=”(PROTOCOL=IPC)(DISPATCHERS=1)” DISPATCHERS=”(PROTOCOL=TCPS)(DISPATCHERS=3)”
… which means you’ll have 6 Dispatchers, 2 working on TCP/IP, 3 working on secure TCP/IP and 1 on the IPC protocol. To have multi-protocol Dispatchers, however, requires that you configure your Listener to listen on the same protocols. You can force the Dispatchers to work with non-default Listeners, too. That would look like this: DISPATCHERS=”(PROTOCOL=TCP)(DISPATCHERS=2)(LISTENER=XYZ)”
That “XYZ” is actually a service name alias, meaning that there needs to be a server-side tnsnames.ora file to resolve that into a location (host and port number) where a nondefault Listener can be found. The 2 Dispatchers thus configured will register themselves with this Listener, rather than the default one found on the Server. You’ll need to configure like this if you’ve chosen not to have your Listener listening on the standard 1521 port, for example. Two other variations to the DISPATCHERS parameter spring to mind: Multiplexing and Connection Pooling. To configure those, you’d end up with this: DISPATCHERS=”(PROTOCOL=TCP)(DISPATCHERS=2)(MULTIPLEX=ON)(POOL=ON)”
Session Multiplexing is a feature of Connection Manager, whereby many Users make separate connections to a Connection Manager server, and it then establishes a single connection to the back-end database. That spares your back-end server from having to support thousands of individual direct connections, and compromising performance as a result.
Copyright © Howard Rogers 2002
17/03/2002
Page 8 of 9
Configuring Shared Server (or Multi-Threaded Server)
Networking Tips
Connection Pooling is a way of sort-of disconnecting an idle User, thus making his connection circuit available for someone else, and then automatically re-connecting him when he starts performing real work again (this is very commonly used when you’re dealing with web connections to the database). In this way, a fixed number of available circuits can actually be made to service the requests from many more real Users than you might have expected. All of these features are fiendishly clever, but this is not the place to start discussing the intricacies of their configuration. At this stage, I simply wanted to point out that, if you need to, there’s far more to Shared Server configurations than just getting it working. Still: if you managed to get it working at all, congratulate yourself!
Copyright © Howard Rogers 2002
17/03/2002
Page 9 of 9
Related Documents
Configure Mts Oracle
May 2020 0
What Is Mts Oracle
May 2020 4
Configure
May 2020 16
Mts
November 2019 40
Mts
May 2020 28