8. Oracle Shared Server 튜닝

- 목표
  > Oracle Shared Server 환경에서의 유저 관리 문제 식별
  > 성능 최적화를 위한 Oracle Shared Server 환경 구성
  > Oracle Shared Server 프로세스를 사용하여 성능 문제 진단 및 해결
 
- 디스패처 모니터 
  > 서버가 디스패처를 자동으로 시작하거나 정지하지 않기 때문에 디스패처 사용률을 모니터해야 한다.
  > v$shared_server_monitor
    >> 연결 및 세션 제한과 세션의 현재 사용현황을 확인할 수 있다. 
    >> sessions 가 디스패처에 대한 실제 제한보다 적게 설정되어 있으면 sessions 파라미터 값이 기본적으로 maximum_connections 의 값으로 설정된다.
  > v$dispatcher
    >> 디스패처 경합 식별
       select network Protocol, status,
              sum(owned) Client,
              sum(busy) * 100 / (sum(busy) + sum(idle)) Busy_Rate
       from v$dispatcher
       group by network, status;
  > 디스패처의 사용률이 50%를 넘으면 디스패처 수의 증가 고려
    유저는 해당 세션이 끝날 때까지 동일한 디스패처에 바인드 되므로 새로 연결할 경우에만 새 디스패처를 사용할 수 있다.
    ALTER SYSTEM SET dispatchers = 'protocol, number';
  > 유저 세션 대기 여부 조회, 평균 대기 시간은 1/100 초 단위로 나타난다.
    select decode(sum(totalq), 0, 'No Responses', sum(wait) / sum(totalq)) avg_wait_time
    from v$queue q, v$dispatcher d
    where q.type = 'DISPATCHER'
      and q.paddr = d.paddr;  
  > v$dispatcher_rate
      
- Shared Server 모니터
  > max_shared_servers 값이 현재 사용 가능한 서버 수보다 높으면 기존의 Shared Server 가 사용되고 있을 때 PMON 백그라운드 프로세스가 Oracle Shared Server 프로세스를 동적으로 시작한다.
    그리고, Shared Server 가 Idle 상태에 있을 때 Shared Server 수가 shared_servers 에 도달할 때까지 PMON 이 Shared Server 를 제거한다.
    다음 명령을 사용하여 Shared Server 를 추가하거나 제거한다.
    ALTER SYSTEM SET SHARED_SERVERS = number;
  > v$shared_server
    select name, requests,
           busy * 100 / (busy + idle), status
    from v$shared_server
    where status != 'QUIT';
  > v$queue
    select decode(totalq, 0, 'No Requests', wait/totalq || 'hundredths of seconds')
    from v$queue
    where type = 'COMMON';    
   
- 문제 해결
  > Shared Server 를 사용하면 startup 및 shutdown 과 같은 권한이 필요한 작업을 수행할 수 없다. 이 경우 Dedicated Server 를 사용해야 한다.
  > 배치 작업 등 사용률이 많은 작업은 Dedicated Server 를 사용해야 한다.   
 
- 관련 Dictionary
  > v$circuit : 데이터베이스에 대한 유저 연결 정보
  > v$dispatcher : 디스패처 프로세스에 대한 정보
  > v$dispatcher_rate : 디스패처 프로세스에 대한 비율 통계
  > v$queue : 다중 스레드 메시지 큐에 대한 정보
  > v$shared_server_monitor : Shared Server 연결을 튜닝하는데 유용한 정보
  > v$shared_server : Shared Server 프로세스에 대한 정보
  > select d.network, d.name disp, s.username oracle_user, s.sid, s.serial#,
           p.username os_user, p.terminal, s.program
    from v$dispatcher d, v$circuit c, v$session s, v$process p
    where d.paddr = c.dispatcher(+)
      and c.saddr = s.saddr(+)
      and s.paddr = p.addr(+)
    order by d.network, d.name, s.username;
    

ORACLE - Shared Server와 Dedicated Server의 접속 방식

---

 

 

• 이전에 봤던 그림이랑 다르네요. ㅡ.,ㅡ 물론, 데이터베이스 버퍼 캐시 등도 있지만,
• 설명을 하기 편하게 하기 위해, 생략 하였습니다.

• 사용자 프로세스가 Dedicated Server를 통해 접속

 

• 반면 Shared Server를 통하면 여러개의 백그라운드 프로세스를 통하여 접속 및 처리가 가능하게 됩니다.
• DO0 , DO1...은 디스패쳐입니다.
• Conn hr/hr 이 L에 돌아갔다가 오는데, 이것은 리다이렉트 방식입니다.

• 이렇게 프로세스는 적지만 다량의 작업이 가능하기 때문에, 서버에 부하가 적습니다.
• 하지만, 반환하는 시간이 Dedicated Server 보다는 조금 더 오래 걸릴 수 있습니다.
• Shared Server를 사용한다고 해서, Dedicated Server를 사용하지 못하는 것은 아닙니다.
  • Shared Server를 사용하면, 서버 프로세스들간의 Road Balance가 되어서 idle(논다)프로세스가 줄어들어서 작업에 효율을 높일 수 있습니다.

PPT 참고 설명

---

• Dedicated Server

  

  • 클라이언트가 명령문을 요청해서 처리하는 동안에만 동작하는 프로세스 입니다.

 

• Shared Server

  
  
  

  

  • 미리 정의된 프로세스들이 사용자의 접속이 없어도 이렇게 실행되어 있습니다.
  • 사용자는 Dispatcher 들을 통해서 간접적으로 접속하여 반환하게 됩니다.
    

---

# 기타 설명

• Dedicated Server에서는 공유할 필요가 없기 때문에, 각각의 명령문을 PGA에 저장되어 사용됩니다.
• 반면, Shared Server Processor 들도 PGA가 있긴 하지만, 각각의 명령문을 공유해야 하기 때문에 Stack정보를 제외하고 나머지는 SGA에 들어가면서 UGA로 명칭만 변경되어 공유가 됩니다.
• 이 UGA 는 기본적으로 Shared Pool에 들어가게 되고, 접속자가 늘어날 경우에는 이 UGA공간도 늘어납니다.
• Shared Server 구조에서는 Large Pool 사이즈를 지정하여, UGA가 들어갈 수 있도록 사용해줘야 합니다.(기본 0)
  • Shared Server 구조는 다중 시스템(프로세스가 여러개)일 때 효율적입니다.
• Shared Server를 구성할 때에는 Spfile Shared_Servers = 를 몇개이다 라고 지정해야 하며,
• Dispatchers = 역시 속성등도 구성을 해줘야 합니다.
  • 그 중에, Connection 개수를 지정해 주는데, 예로 Conn = 30 이면, 최대 90명이 접속가능 합니다.
  • 또한 Pool = on 속성을 사용할 수 있으며, 이것을 사용하면 세션 기능도 사용가능하여 Connection을 더 증가시킬 수 있습니다.
    • 예 : Dispatchers = (conn = 30)
                                (Pool = on)
                                (sess = 50)
    • 풀링기능을 활성화 시킴으로써 더 많은 사용자들을 수용할 수 있습니다.
  • Connection Manager 라는 미들티어를 구성할 수 있는데, 이것은 별도의 기기에 구성하여 서버로 사용해야 합니다.
  • 사용자는 이 미들티어를 통해서 접속하며, 미들티어는 사용자의 다중접속을 서버에게는 단일 접속으로 처리하도록 해줍니다.

    

  • ▲오라클 Connection Manager

---

 Oracle Shared Server의 이점

• Instance에 대한 Process 수를 줄인다.
• 허용되는 User수를 증가시킨다.
• 로드 밸런싱을 수행한다.
  • 서버프로세스의 로드밸런스
• 휴지 Server Process의 수를 줄인다.
• 메모리 사용량과 시스템 오버헤드를 줄인다.
  • 주로 메모리에 대한 오버헤드는 많이 줄어들지만, CPU에 대한 오버헤드는 증가합니다.

 

 Oracle Shared Server와 함께 Dedicated Server 사용

---

 

절차상의 차이점은 있지만, 둘다 리스너를 통해서 가야합니다.

또한 항상 디스패쳐당 접속수가 동일하게 유지하도록 해야 합니다.

이렇게 디스패쳐에 연결되기 위해서는 Remote와 Server의 접속방식이 동일해야 합니다.

SYSDBA가 접속할 때는 LOCAL 이던 무엇이던간에 무조건 전용서버가 할당됩니다.

• 사용자는 Request Queue를 통해 간접적으로 전하고, 반환을 받습니다.
• Response Queue는 Dispatcher당 하나씩 가지고 있습니다.

• Shared Pool 및 다른 메모리구조 : Larger Pool 입니다. Large Pool은 이러한 문제로 인해 반드시 설정해야 합니다.

---

Oracle Shared Server의 구성

• 필수 초기화 Parameter
  • DISPATCHERS
  • SHARED_SERVERS
    • 똑똑합니다. 동적으로 MAX 값까지 필요하면 늘여줍니다. 하지만, 줄이는건 동적이지 못합니다.
      
• 선택적 초기화 Parameter
  • MAX_DISPATCHERS
  • MAX_SHARED_SERVERS
    • 이 MAX 값은 Default값이 있기 때문에 선택적 값입니다.
  • CIRCUITS
    • Shared Server를 통한 경로. Response, Request를 통한....
    • 이것은 값을 튜닝해서 서버의 성능을 높이거나 하진 않지만, 값을 높여 놓으면 나중에 오라클이 인지를 하게 되고, 나중에 오라클이 Queue 사이즈를 크게 조정하거나 합니다.
    • 단, 큐를 키우게 되면 SGA의 다른 요소가 영향을 받습니다.
  • SHARED_SERVER_SESSIONS
    • Shared Server Sessions 을 모두 제한하는 파라미터 입니다.
    • 만약, 6개의 세션이 등록되었다면, 6개의 세션이 실행하고 있다는 뜻이 됩니다.
    • V$Session을 조회하면, 혼자 작업 중인데도 많은 세션이 동작중임을 볼 수 있습니다.
    • 1명이 쓰는데도 많이 접속한 것을 볼 수 있지요.
    • Shared_Server_Sessions의 값은 항상 낮게 설정해야 합니다. 그래야만, 나중에 Shared Server가 꽉 차더라도 공간을 보장해줘서 후에, Dedicated Server를 예약할 수 있도록 합니다.
      
      
      

• 지정된 프로토콜에 대해 처음에 시작된 Dispatcher 수를 지정합니다.
• \는 줄바꿈표시 ㅡ.,ㅡ
• DISPATCHERS 매개변수
  데이터베이스 관리자는 DISPATCHERS 매개변수를 사용하여 각 디스패처에 대해 다양
  한 속성을 설정합니다.
  Oracle9i는 Oracle Net 서비스에서 사용되는 구문과 유사한 이름-값 구문을 지원하여 기
  존 속성과 추가 속성의 사양을 위치 독립적이며 대소문자를 구분하지 않는 방식으로 설
  정합니다.
  예:

Oracle Database Configuration Assistant를 사용하여 이 매개변수를 구성할 수 있습니다.

Dispatchers의 파라미터

 

• PROTOCOL
• ADDRESS
• DESCRIPTION
• DISPATCHERS
• SESSIONS
• LISTENER
• CONNECTIONS

---

• 동시에 실행될 수 잇는 최대 디스패쳐 프로세스 수 지정합니다.
• 처음 시작 시보다 더 많은 디스패쳐를 지정이 가능합니다.

---

 

• 인스턴스가 시작될 때 생성할 서버 프로세스의 수를 지정합니다.
• 이것은 Alter  명령어를 이용하여 늘리거나 줄일 수 있습니다.

---

 

• 시작 가능한 최대 Shared Server수를 지정합니다.
• 직접 조정하지 않아도, 나중에 오라클서버가 자동으로 늘렺부니다.
• Request Queue의 길이에 따라 공유서버의 구성이 달라집니다.

---

 

• 수신 및 송신 네트워크 세션에 사용할 수 있는 가상 Circuit의 총 수를 지정합니다.
• 전체 SGA 크기에 영향을 줍니다.

---

• 허용되는 Oracle Shared ServerUser 세션의 총 수를 지정합니다.
• 이 파라미터를 설정하면 전용서버에 대한 사용자 세션을 예약할 수 있습니다.
• LARGE_POOL_SIZE : UGA를 사용하기 위해 라지풀을 조정한다.

---

▶ 설정 확인

• 위의 명령을 실행하여 인스턴스가 시작될 때 디스패쳐가 리스너에 등록되었는지 확인합니다.
• 단일 연결을 설정하여 공유 서버를 통해 연결한 다음 V$Circuit 뷰를 질의하여 Shared Server 연결 당 하나의 항목만 표시되는지 확인합니다.

▶ 동적 뷰

• V$CIRCUITS
• V$SHARED_SERVER
• V$DISPATCHER
• V$SHARED_SERVER_MONITOR
• V$QUEUE
• V$SESSION
  

Shared Server (MTS) Diagnostics [ID 1005259.6]
		수정 날짜 29-SEP-2011     유형 BULLETIN     상태 PUBLISHED

In this Document
  Purpose
  Scope and Application
  Shared Server (MTS) Diagnostics
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 10.2.0.1 - Release: 8.1.5 to 10.2
Oracle Net Services - Version: 8.1.5.0.0 to 10.2.0.1.0   [Release: 8.1.5 to 10.2]
Information in this document applies to any platform.

Purpose

Introduction

This document contains information about the Shared Server configuration of the Oracle Database software and some of the SQL scripts necessary to gather diagnostic information with guidelines for interpreting results. Prior to Oracle 9.0, Shared Server was called Multi-Threaded Server; the name was changed to reflect the extensive enhancements made.

Scope and Application

Skill level rating for this Article: Intermediate

Shared Server (MTS) Diagnostics

Architecture of the Shared Server Database Configuration

The best position to be in will always be based on understanding. The components of the Shared Server database configuration consist of the Dispatchers and the Shared Servers. These components run as separate processes in the operating system (or threads in some operating systems). They interact with each other through the use of a Common Queue (CQ - also known as the Virtual Queue, of which there could be multiple CQs) and individual Dispatcher Queues. Both queues reside in the Shared Global Area (SGA) and are sized automatically by the database itself. Another component of Shared Server is not a process but an abstraction of the user session(more of an owned pointer), called a Virtual Circuit (VC). The communication between the Dispatchers and Shared Servers is primarily done by passing ownership of a Virtual Circuit from one to another.

Fig. 1: Diagram of Shared Server Architecture

 

The stages that Shared Server goes through are quite simple. After the client sends the connection request to the Listener, it will either redirect or hand off (called warm hand-off) the connection to the Dispatcher (the Dispatcher does not necessarily need to be on the same host as the Listener). Once the client has connected to a Dispatcher it stays connected to that Dispatcher. Before the client completes the database log in, the Dispatcher associates a Virtual Circuit (VC) for that database session. There exists exactly one row in the VC view (V$CIRCUIT) for each client connection. This view also shows the current status of the client's VC. Once the VC has been associated with the database session, the client will complete the database logon by passing the username and password to the Dispatcher. This request, as part of the VC for that new session, will be placed in the Common Queue where the first available Shared Server will complete the logon. Once each phase of the logon has completed, the Shared Server will pass the VC back to the Dispatcher, which then passes the response back to the Client (this actually takes several round trips to the client, in just the same manner as if it was a Dedicated connection).

Once the logon has completed, the client starts a normal conversation with the database. When the client makes a request to the database, it is the Dispatcher that picks up this request from the operating system's network protocol. The Dispatcher then determines which client session the request came from (remember that a Dispatcher can be configured for Connection Pooling and Multiplexing: see the Net Administration Guide for more information on those configurations), tags that sessions' VC that there is a new message (there is also a pointer to that session buffer in the VC) and places the VC in the Common Queue. The CQ is serviced by the Shared Servers on a first-in-first-out basis. The next available Shared Server pulls the VC from the CQ and processes the request. Part of the VC structure is the identity of the Dispatcher that created it (and which client is connected to it). When the Shared Server is finished processing the request, it writes the output to the session buffer, changes the VC's ownership back to the Dispatcher that created it, places the VC into that Dispatcher's queue, and posts the Dispatcher it has something in its queue. The Dispatcher then checks its queue and sends what is in the session buffer through the operating system network protocol back to the Client.

In the case where there is a request for a Database Link, it is the Shared Server process that will, from the link definition, create an outbound VC and place it into the least loaded Dispatcher's queue (not necessarily the same Dispatcher the Client is connected to). This Dispatcher then logs into the remote database and passes the query to it for processing. Once the remote database responds, the Dispatcher then places the VC back into that Shared Server's ownership.

The Dispatchers are not limited to just the Oracle Net protocol. They also are able to understand FTP, HTTP(S), WebDAV, IIOP, SMTP, and TCP protocols.

 

The main views containing Shared Server information include:

V$CIRCUIT
V$DISPATCHER
V$DISPATCHER_CONFIG
V$DISPATCHER_RATE
V$QUEUE
V$SESSION
V$SHARED_SERVER
V$SHARED_SERVER_MONITOR

Dispatchers: The Number Of Dispatchers

 

The number of Dispatchers present in a Shared Server database configuration may vary from zero up to the system INIT.ORA parameter of MAX_DISPATCHERS. The initial number of Dispatchers created at instance startup is the value defined by the DISPATCHERS parameter in the system INIT.ORA and can exceed the MAX_DISPATCHERS value (as of version 10). Both parameters can be altered at runtime by ALTER SYSTEM commands (version 9.0 onwards, consult the SQL Reference manual for the particular version being run).

It is the PMON database background process that starts or stops any background processes, such as Dispatchers and Shared Servers. Dispatchers are not dynamically started or stopped, but must be manually maintained. Apart from restarting the database in order for the database to take on a new value set in the INIT.ORA, the following command may also be issued while the database is running:


Where 'string' is a valid setting for the DISPATCHERS parameter. For example:


PMON creates or destroys Dispatchers and then informs the Listener with their current state. PMON also updates the Listener about every ten seconds (depending on system load and other factors) with the current number of sessions and database load. To have PMON update the Listener with a change outside its normal cycle (as long as the LOCAL_LISTENER and REMOTE_LISTENER parameters are properly set in the system INIT.ORA) use this command:

One can then query then Listener for the new services that have been registered:

From this output we can see that there are four dispatchers, two using TCP and two using TCPS. The TCP ports that are assigned to the Dispatchers were allocated by the operating system. To specify what port to use, each Dispatcher has to be configured individually specifying the port number in the ADDRESS parameter section (see the Oracle Net Administration Guide on how to assign ports to Dispatchers).

Dispatchers can also be configured for a particular service. By default, each Dispatcher will service all SERVICE_NAMES and the DB_NAME. Dispatchers can be set up to only service a specific Service.

 The command added one Dispatcher, D003, to service the SERVICE_NAME of V10R2_DISP (specified by the SERVICE clause). To get this Dispatcher to service V10R2_DISP the INDEX clause was used (INDEX can also be used to "separate" Dispatchers from other TCP Dispatchers). This new Dispatcher gets a new INDEX:

 If the desire is to add more Dispatchers for a particular protocol, either issue the ALTER SYSTEM command specifying the full DESCRIPTION of the Dispatcher, or the Dispatcher's INDEX. For example, below are three Dispatchers configured for TCP and one configured for TCPS and there is a need to add one more TCP Dispatcher:

Dispatchers: Shutting Down Dispatchers

If the need is to shut down or reduce the number of Dispatchers by using the ALTER SYSTEM statement, the database can decide, or a specific Dispatchers can be chosen. To identify the name of the specific Dispatcher process to shut down, use the V$DISPATCHER view.

Each Dispatcher is uniquely identified by a name of the form Dnnn (where n is a number in the 0-9 range). In Unix, the Dispatcher process will have a name like ora_dnnn_sid. For Windows, the Dispatchers run as threads and are only visible using certain utilities, but the Oracle views will be the same. The Dispatchers are grouped by CONF_INDX based on the DESCRIPTION (or ADDRESS and PROTOCOL).

To shut down Dispatcher D002, issue the following statement:

 

 

The Dispatcher stops accepting new connections and will wait until all of the sessions it handles are disconnected before shutting down.

For a more "immediate" shutdown of the Dispatcher (it is in a bad state or it won't shutdown with the above command) issue this command:

 

 

The IMMEDIATE keyword stops the Dispatcher from accepting new connections and immediately terminates all existing connections through that Dispatcher. After all sessions are cleaned up, the Dispatcher process shuts down.

When a Dispatcher is shut down, other Dispatchers will not inherit the name, so it is possible to see some Dispatchers missing. When starting up a new Dispatcher, it will obtain the next open Dispatcher name. In the case where TCPS D002 Dispatcher was shut down, and a new Dispatcher for TCP was started, the new Dispatcher will be called D002. 

Dispatchers: Monitoring Performance

In general, Dispatchers will not be very busy because their tasks are relatively quick to complete. In the example below, Dispatchers are less than 1% busy.

 The OWNED column of V$DISPATCHER view shows the number of clients currently connected to each Dispatcher. In the above example, a D000 has 26 clients connected. D000 is in the process of sending a message to a client as is shown by the SEND status. D004 has OWNED = 0 because it has just been started using the following command:

 

 

Dispatchers: Performance

One perspective for interpreting Dispatcher performance is measuring the wait times in the various queues by querying the view V$QUEUE.

 

In the above example, the WAIT column is the total amount of time all requests have waited in the particular queue. The TOTALQ column is the total number of requests in a queue since the startup of the database. The AVG WAIT denotes the average wait (in seconds) per queued request.

The row with the TYPE of COMMON represents the Common Queue. The CQ holds all client requests to be processed by the Shared Servers. Please note that V$QUEUE view is not related to the Oracle Streams Advance Queuing feature. 

Shared Servers: Setting the Quantity of Shared Servers

The quantity of Shared Server processes will vary between INIT.ORA parameters SERVERS and MAX_SERVERS. Initially, the MAX_SERVERS value should be set to some estimated maximum number. For the initial setting, it can be set to the maximum number of expected sessions on the database (this is just a suggestion as it could also be set to far less). The effect of setting this parameter to a large value only affects the size of the Common Queue. This parameter can be changed dynamically by issuing the command:

 

 

Setting the initial value for SERVERS is also estimated. It can be set to some number under the setting of MAX_SHARED_SERVERS. But once the system is running under production load, the parameter SERVERS can be changed to accommodate the load. The Oracle Database Performance Tuning Guide explains how to monitor V$QUEUE to determine if SERVERS should be increased:  

 If the system is suffering from high SYS load due to having to create and destroy many shared servers, then SERVERS might be set to one plus the number in SERVERS_HIGHWATER that is found in the V$SHARED_SERVER_MONITOR view (the name of the view is V$MTS in 8.1 and before).

Setting this parameter based on the SERVERS_HIGHWATER value will reduce the expense of process creation and match the known maximum of Shared Servers. These are just suggestions as to a starting point to configuring Shared Server. The Shared Server views should be monitored to make sure the settings are appropriate for this instance. Monitoring the instance initially to make sure the settings are correctly configured will assure a well-tuned system.

Shared Server Performance

Shared Servers are created by PMON. Upon instance startup, PMON will create them according to the value of the SHARED_SERVERS parameter. If more SHARED_SERVERS are needed, PMON will create them up to MAX_SHARED_SERVERS to meet the need. PMON will terminate idle Shared Servers until the number goes back to SHARED_SERVERS. When measuring the performance of the Shared Servers, it is normal to see the lower numbered Shared Servers to be busier then the higher numbered ones.

 

In the above example, all the Shared Servers are between 1% and 99% busy. Shared Server S008 is very busy processing a single client request and Shared Server S000 has been busy handling numerous smaller requests. In general, the S000 Shared Server will always be the busiest and could easily be 100% busy all the time. This is by design.

The reason that S003-S005 and S007 are not listed is because the SHARED_SERVER parameter was set to 3 so PMON removed those Shared Servers because they went idle long enough to be removed. The idle interval cannot be set, nor does it need to be as it is more efficient to not have to create a Shared Server. S006 and S008 are not idle so they will exist as long as there is work for them to do.

In the case where there is a gap in the %TIME BUSY, such as is illustrated above where higher numbered Shared Servers S006 and S008 are nearly 100% used. This could be due to some sessions having so much work to do that a Shared Server has been dedicated to that particular session. It is sessions like this that should be found and forced to connect with a Dedicated server processes. Such heavy sessions have enough continuous workload that the service time the Dispatcher adds may slow them down.

The STATUS column of the V$SHARED_SERVER view provides useful information about WAIT status. In particular, the WAIT(ENQ) status tells the DBA that the user is waiting for a lock resource, and in rare cases, acts as an alert for a deadlock situation.

An overview of server creation and termination and high-water mark is available from the V$SHARED_SERVER_MONITOR view.  

 

The MAXIMUM_CONNECTIONS is the value of the maximum number of Virtual Circuits in use at one time.

The MAXIMUM_SESSIONS is the highest number of Shared Server sessions in use at one time since the instance started.

The SERVERS_STARTED and SERVERS_TERMINATED columns maintain a running total of Shared Server process creation and termination by PMON (but do not include the number set in the SHARED_SERVERS parameter).

The SERVERS_HIGHWATER value holds the high-water mark for the Shared Server count since the instance startup.

These statistics are useful indicators to check if SERVERS is set too low or too high. If the SERVERS_STARTED or SERVERS_TERMINATED are zero, this is an indication that too many Shared Servers may have been configured. Similarly, if the values of SERVERS_STARTED and SERVERS_TERMINATED grow quickly, the number for SHARED_SERVERS is likely to be too low and should be set to SERVERS_HIGHWATER + 1 (the "+ 1" is for good measure and has no intrinsic meaning).

 

Virtual Circuits and Sessions

The SERVER column in the V$SESSION view shows the type of Server that is currently servicing each session.

In the above example, DEDICATED means that client is connected with a dedicated server process. Shared Server connections appear as NONE or SHARED depending on whether a task is currently being serviced by a Shared Server or not. In this case only the OS User "bill" is being serviced by a Shared Server.

The V$CIRCUIT view provides more detailed information about usage of circuits by each session.

 The DISPATCHER column identifies the Oracle process ID for the Dispatcher Associated with the session. The SERVER column provides the Oracle process ID for the Shared Server currently servicing the client session, and zero if the session is not being serviced. For the QUEUE column, NONE represents the circuit is idle, SERVER means it is currently being serviced by a Shared Server, DISPATCHER means it is being serviced by a Dispatcher, and COMMON means it is on the Common Queue waiting to be picked up by a Shared Server.

The WAITER column is the Oracle process ID of the process that is currently waiting for data to appear in the Circuit. It will contain "00" when no database operation is in progress, otherwise it will list the Oracle process id for the Dispatcher or a Shared Server. Dispatchers are very quick to complete their work, so quick that it is rare to catch a glimpse of a Dispatcher in the WAITER queue.

References

http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14220%2Ftoc.htm&remark=portal+%28Books%29
http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14231%2Ftoc.htm&remark=portal+%28Books%29
http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14211%2Ftoc.htm&remark=portal+%28Books%29
http://www.oracle.com/pls/db102/to_toc?pathname=network.102%2Fb14212%2Ftoc.htm&remark=portal+%28Books%29
Previous versions of the above manuals

첨부 파일 Shared_Server.jpg (62.28 KB)

관련 자료 제품 Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Oracle Database Products > Oracle Database > Net Services > Oracle Net Services 키워드 DISPATCHER; DISPATCHERS; MTS; PERFORMANCE; SHARED SERVER