Server connection types in Oracle 10g

Learning objective

After completing this topic, you should be able to distinguish between Oracle server connection types.

1. Establishing a connection

When Oracle Net name resolution has been completed, the user generates a user process which passes a request for a connection to the Oracle Net listener.

The CONNECT packet is received by the listener, which then checks that the requested Oracle Net Services name is valid.

The listener transmits an error code to the user process if an invalid name is requested.

If the CONNECT packet does not request a service name, the listener acknowledges the request but does nothing else.

In order to process user requests, you have to specify the type of connection that the server must use.

The server processes are

• shared server
• dedicated server

shared server

The shared server connection is the default connection type for Oracle Net. It allows a small number of server processes to serve a large number of users.

When a shared server configuration is used, the various user processes connect to a dispatcher. The dispatcher then directs the incoming network user requests to a single queue. One of the idle shared server processes from a shared pool takes up one of the requests from the queue.

dedicated server

In a dedicated server configuration there is one server process for every user process.

This configuration takes up a lot of system resources, including memory and CPU cycles.

Question

Suppose the listener receives the CONNECT packet and the service name is not requested.

What does the listener do next?

Options:

0. It acknowledges the connect request and does nothing else
0. It hands all work to the server process
0. It spawns a new process to deal with the connection
0. It transmits an error code to the user process

Answer

When the listener receives the CONNECT packet and the service name is not requested, it acknowledges the connect request and does nothing else.

Option 1 is correct. When the CONNECT packet is received by the listener, it checks that the requested Oracle Net service name is valid. If it is invalid it transmits an error message.

Option 2 is incorrect. The server process does not pick up the connection request if a service name is not specified. Instead the listener acknowledges the request and remains idle.

Option 3 is incorrect. A new process cannot be generated until a valid service name is provided in the request. If no service name is supplied, the listener remains idle.

Option 4 is incorrect. An error code is only returned to the user process if an invalid service name is sent to the listener.

To specify a connection type, you first open the Oracle Net Manager using the netmgr command.

In the Oracle Net Manager, you must specify the Service name by selecting the appropriate node.

In this case it is orcl1.

You now want to establish a shared server connection.

You click the Connection Type drop-down list and select Shared Server.

The connection type is now set at Shared Server.

Question

So far you have been using a dedicated server to complete administrative tasks. Now, however, you want to allow for a number of users to create processing connections with the server.

You are required to use Oracle Net Manager to specify the Shared Server connection type. Which of these options allows you to complete this task?

Options:

0. You click the orcl3 Service Name node in the Navigation pane. You then click the Connection Type drop-down list and select Shared Server.
0. You click the orcl3 Connection Type folder in the Navigation pane. You then click the Service Name drop-down list and select Shared Server.

Answer

You click the orcl3 Service Name folder in the Navigation pane. You then click the Connection Type drop-down list and select Shared Server.

2. The dedicated server process

When the listener verifies that the service name is valid, it creates a new process to deal with the connection.

This is known as a "server process" or "shadow process". When created, the listener passes on the information for the user process, including the address information, to the server process.

The listener no longer deals with the process from this point. Now the server process transmits a RESEND packet back to the user process.

When the user process receives the RESEND packet, it sends the CONNECT packet again, this time to the server process.

The server process verifies the user through an authentication method, often a password, and, when verified, creates a user session.

In the session the dedicated server process acts as the user agent on the server.

The dedicated server process is responsible for

• parsing SQL
• checking the database buffer cache
• reading datablocks
• managing sort activity
• returning results

parsing SQL

The server process parses and runs all SQL statements that are passed through the application.

checking the database buffer cache

The server process checks for datablocks, which are needed to perform SQL statements, in the database buffer cache.

reading datablocks

If the necessary datablocks from the data files on disk are not already present in the database buffer cache section of the System Global Area (SGA), the server process reads them into it.

managing sort activity

The server process is partially made up of a Program Global Area (PGA), which contains a memory area called the Sort Area. It is here that the server process manages sort activity.

returning results

The server process provides results for the user process in a form that allows the application to process the information.

Question

Which of these is the dedicated server process responsible for?

Options:

0. Maintaining a dispatcher response queue
0. Parsing SQL
0. Reading datablocks
0. Returning results

Answer

The dedicated server process is responsible for parsing SQL, reading datablocks, and returning results.

Option 1 is incorrect. A dispatcher response queue is used in a shared server architecture. Another responsibily of the dedicated server process is to check the database buffer cache.

Option 2 is correct. The server process parses and runs all SQL statements that are passed through the application.

Option 3 is correct. If the necessary datablocks from the data files on disk are not already present in the database buffer cache section of the System Global Area (SGA), the server process reads them into it.

Option 4 is correct. The server process provides results for the user process in form that allows the application to process the information.

The server process keeps a certain amount of memory back for specific purposes like hash joins and bitmaps.

However, the initialization parameter settings control how much memory the dedicated server process can use.

It can be controlled automatically using the PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY.

When using a dedicated server process, there is one server process for every user process.

As a result, system resources such as memory and CPU cycles are being consumed with each server process. This can negatively affect the system's scalability if the system is heavily loaded.

If the dedicated system architecture is having a negative impact on resource demands, you can add more memory and additional CPU resources.

Alternatively, you can use the Oracle Shared Server architecture.

Question

Which of these are characteristics of the dedicated server process?

Options:

0. A system's scalability can be negatively affected
0. Each server process takes up memory and CPU cycles
0. For every user process there is one server process
0. It takes user processes from a common queue

Answer

With a dedicated server process, a system's scalability can be negatively affected. Also, each server process takes up memory and CPU cycles, and for every user process there is one server process.

Option 1 is correct. As there is one server process for every user process, the system may be limited in terms of scalability if it is heavily loaded. You can provide additional memory or CPU cycles to get around this.

Option 2 is correct. You should use a dedicated server process when the number of user processes is relatively small. Otherwise the system's scalability may be negatively affected.

Option 3 is correct. As a server process takes up memory and CPU cycles, you have to be sure that the system doesn't get so heavily loaded it affects the system's scalability.

Option 4 is incorrect. User processes are taken from a common queue only when a shared server architecture is used. You would use a shared server architecture to improve the system's scalability.

3. The shared server process

In the Shared Server architecture, each service has one or more dispatcher processes.

In this architecture, the listener doesn't start a dedicated server process when it receives a connection request.

Instead, it uses a list of dispatchers for each service name with the relevant connection load for each dispatcher.

Note

The connection load is the number of concurrent connections.

Connection requests are routed to the lightest loaded dispatcher that is servicing a given service name.

Users are connected to the same dispatcher for the whole session. A dispatcher, unlike dedicated server processes, can handle hundreds of user sessions.

However, it doesn't handle user requests. Instead, it passes them to a common queue in the shared pool section of the SGA.

The shared server process then takes the requests from the queue and completes the full process of the request.

In a single user session, requests may be processed by different shared server processes. As a result, those memory structures normally stored in the PGA must reside in a shared memory location.

These memory areas are stored in the large pool area of the SGA in a Shared Server architecture.

Question

Which of these are characteristics of a shared server process?

Options:

0. A listener maintains a list of dispatchers
0. Each service has at least one dispatcher process
0. The listener doesn't start a dedicated server process when a request arrives
0. There is one server process for every user process

Answer

With a shared server process, instead of the listener starting a dedicated server process when a request arrives, it uses a list of dispatchers to process the request. In turn, each service has at least one dispatcher process.

Option 1 is correct. Connection requests are routed to the lightest loaded dispatcher that is servicing a given service name.

Option 2 is correct. The dispatcher doesn't process user requests. Instead it passes them to a common queue in the shared pool section of the SGA.

Option 3 is correct. Instead, the listener uses a list of dispatchers for each service name with the relevant connection load for each dispatcher.

Option 4 is incorrect. There is only one server process for every user process in a dedicated server architecture. A shared server architecture allows a user request to be processed by multiple shared servers.

Although the Oracle Shared Server architecture uses memory and processing resources efficiently, it is not appropriate for all connections.

Shared servers do not perform well with operations that deal with large sets of data, such as batch processing or warehouse queries.

This is because of the nature of common request queues and the fact that many users can share a dispatcher response queue.

Also, the Shared Server architecture does not deal well with the large datasets involved when completing a backup and recovery session using Oracle Recovery Manager.

Certain administrative tasks shouldn't be completed using shared server connections.

These tasks, commonly performed by the DBA, include creating tablespaces and datafiles, analyzing statistics, index and table maintenance, and startup and shutdown.

Question

Which types of database work are not suitable for shared servers?

Options:

0. Backup and recovery sessions
0. Creating tablespaces and datafiles
0. Startup and shutdown of the instance
0. Multiple user sessions

Answer

Backup and recovery sessions, creating tablespaces and datafiles and startup, and shutdown of the instance are not suitable for shared servers.

Option 1 is correct. Backup and recovery session using the Oracle Recovery Manager are not suitable for shared server connections because of the large datasets they use.

Option 2 is correct. Other administrative tasks that are not suitable across shared server connections are analyzing statistics and maintaining index and tables.

Option 3 is correct. Administrative tasks commonly performed by the DBA should not be performed across shared server connections.

Option 4 is incorrect. The shared server architecture is more suitable than the dedicated server architecture for supporting multiple user sessions.

4. Processing a request

There are several steps involved in processing a database request through a shared server architecture:

1. the request is forwarded to the dispatcher
2. the dispatcher puts the request into a common queue in the SGA
3. the next available shared server takes up the request and processes it
4. each dispatcher has its own response queue, so the shared server places the response on the calling dispatcher's response queue
5. the response is taken from the queue by the dispatcher
6. the response is passed to the user by the dispatcher

When the user request is completed, the shared server process is no longer needed, and it becomes available to service another user request in the queue.

The request queue has several characteristics:

• all dispatchers share the request queue
• the shared server checks the request queue for new requests
• requests in the queue can't be prioritized, as they are processed on a first-in, first-out basis

Question

Rank the steps involved in the database request process in the order they occur.

Options

Option	Description
A	The dispatcher puts the request into a common queue in the SGA
B	The next available shared server takes up the request
C	The request is forwarded to the dispatcher
D	The shared server places the response on the calling dispatcher's response queue

Answer

Correct ranking

Option	Description
C	The request is forwarded to the dispatcher After this, the dispatcher puts the request into the queue in the SGA.
A	The dispatcher puts the request into a common queue in the SGA Before this, the user process forwards the request to the dispatcher and after it, the next available server takes up the request.
B	The next available shared server takes up the request Before this, the dispatcher puts the request into a common request queue and after this the shared server places the response on the calling dispatcher's response queue.
D	The shared server places the response on the calling dispatcher's response queue After this, the dispatcher retrieves the response from its response queue and returns it to the user.

The SGA and the PGA have different contents depending on whether the dedicated server or the shared servers are used.

For example, in a shared server the user session data is in the SGA, while in a dedicated server it is in the PGA.

The contents of the SGA and the PGA are

• shared pool and other memory structures
• cursor state
• user-session data
• stack space

shared pool and other memory structures

In both the shared and dedicated servers, the text and parsed forms of all SQL statements are stored in the SGA.

cursor state

Runtime memory values for the SQL statements are contained in the cursor state, which is stored in the PGA in a dedicated server and the SGA in a shared server.

user-session data

Security information and resource usage are included in user-session data. It appears in the PGA in the dedicated server and the SGA in the shared server.

stack space

The stack space contains local variables for the process and is stored in the PGA in both dedicated servers and shared servers.

Question

What do the SGA and PGA contain?

Options:

0. Cursor state
0. Control files
0. Stack space
0. User-session data

Answer

The SGA and PGA contain a cursor state, stock space, and user-session data.

Option 1 is correct. Runtime memory values for the SQL statements are contained in the cursor state, which is stored in the PGA in a dedicated server and in the SGA in a shared server.

Option 2 is incorrect. Control files are stored on disks, in a simple binary file format. They describe the structure of the database, and are used by the server when opening the database.

Option 3 is correct. The stack space contains local variables for the process and is stored in the PGA in both dedicated servers and shared servers.

Option 4 is correct. Security information and resource usage are included in user-session data. It appears in the PGA in the dedicated server and the SGA in the shared server.

Question

Now match the contents to the correct server process.

Options:

0. User-session data is held in the SGA
0. Cursor state is held in the PGA
0. Cursor state is held in the SGA
0. User-session data is held in the PGA

Targets:

0. Shared servers
0. Dedicated servers

Answer

In the shared server, the user-session data and cursor state are held in the SGA. In the dedicated server, the user-session data and cursor state are held in the PGA.

Summary

When Oracle Net name resolution has been completed, a user process passes a request for a connection to the Oracle Net Listener. The listener checks that the service name is valid. If it is not, the listener transmits an error code to the user process. If the user process does not request a service name, the listener acknowledges the request but does nothing else.

After the listener validates the service name, it creates a server process. There are two server processes: dedicated server and shared server. The dedicated server process is responsible for parsing SQL, reading datablocks, managing sort activity, checking the database buffer cache, and returning results. As there is one dedicated server process for every user process, the pressure on memory and CPU cycles can be detrimental to system scalability.

In the shared server architecture, the listener uses a list of dispatchers for each service name with the relevant connection load. Users are connected to the same dispatcher for the whole session and a dispatcher, unlike dedicated server processes, can handle hundreds of user sessions.

A database request is first forwarded to the dispatcher, then the dispatcher puts the request into a common queue. The next available shared server takes up the request. When processed, the shared server places the response on the calling dispatcher's response queue. The dispatcher takes the response from the queue and passes it back to the user.