Maxgauge와 같은 툴의 가장 큰 장점은 Oracle Hang 상황에서도 액티브 세션의 리스트를 얻을 수 있다는 것입니다. 대기 이벤트와 SQL 정보가 포함된 액티브 세션 목록이야말로 모든 성능 트러블슈팅의 시작입니다.

Maxgauge와 같은 툴이 Oracle Hang 상황에서도 데이터를 수집할 수 있는 것은 DMA(Direct Memory Access)를 사용하기 때문입니다. 이 방법은 Oracle의 COE(Center Of Expertise) 팀이 극한의 상황, 즉 SQL로 필요한 정보를 수집하지 못할 때 사용하던 방법입니다. 이것이 Maxgauge와 같은 툴 덕분에 보편화된 것입니다.

만일 Maxgauge와 같은 툴이 없다면 어떻게 해야 할까요? Oracle이 제공하는 ASHDUMP 기능을 Preliminary Connection과 함께 사용할 수 있습니다.

• Preliminary Connection이란 SQL*Net 방식이 아닌 Direct Memory Access 방식으로 Connection을 맺는 것을 말합니다.
• ASHDUMP는 액티브 세션 목록의 메모리 버전인 ASH(Active Session History)를 텍스트 파일에 기록하는 기능입니다.

따라서 위 두 기능을 같이 사용하면 마치 DMA 방식으로 액티브 세션의 목록을 얻는 것과 동일한 효과가 있습니다.

아래에 간단한 예제가 있습니다.

우선 Preliminary Connection을 맺습니다.

일반적인 쿼리는 동작하지 않습니다. Preliminary Connection 상태에서 ASHDUMP를 수행합니다. 레벨(10)은 10분을 의미합니다. 즉 지난 10분간의 ASH를 의미합니다. Dump 파일의 내용은 다음과 같습니다. 11g부터는 액티브 세션 목록과 함께 해당 목록을 테이블로 로딩하는 스크립트까지 함께 제공합니다. Hang의 악몽이 지나간 후(대부분 리스타트) 사후 분석을 위한 것입니다. 사후 분석 용도로 사용되면 대단히 유용할 것입니다. Preliminary Connection에서는 전통적인 트러블슈팅 데이터인 Hang Analyze나 System State Dump도 사용 가능합니다. 단, Preliminary Connection와 oradebug는 비공식적인 지원 기능이기 때문에 철저한 테스트 후 사용해야 되며, 가능한 오라클 지원 엔지니어의 승인하에 사용해야 합니다.

더 자세한 정보들은 아래 문서를 참조하세요.

• Preliminary Connection과 Direct Access
• ML 노트: 243132.1

출처 : http://ukja.tistory.com/310

오라클이 버전업이 되면, 오라클의 내부 동작 방식을 제어하는 파라미터에 대한 변경사항이 발생하게 된다. 즉, 파라미터의 기본설정값이 변경되거나, 새로운 기능 추가로 인해 필요한 파라미터가 추가되거나, 더 이상 불필요한 파라미터가 사라지게 된다. 따라서 오라클 업그레이드시에는 이러한 파라미터의 변경사항들을 체크해보는 것이 필요하다. 본 문서는 오라클 9i, 10g, 11g에 대해서 각각의 버전간의 파라미터 값의 변경사항/추가/삭제 사항들을 정리하는것을 목적으로 한다.

오라클 9i, 10g, 11g 간의 파라미터 기본설정 값의 변경 내역

아래의 파일에 각 버전간에 기본설정 값이 변경된 내역을 정리하였다.

오라클 9i, 10g, 11g에서 추가되거나/사라진 파라미터 내역

오라클이 버전업되면 추가되어지는 파라미터가 수백개가 넘는다. 따라서 본 문서에 그 내용을 담는 대신 첨부되어진 파일을 참조하기 바란다.

Oracle 버전별 Parameter 비교.xls

불량SQL을 찾을때와 락문제를 해결할때 도움이 될 것입니다.

 

유용하게 사용되길 바라며...
 

 

SELECT * FROM dba_External_Locations
SELECT * FROM nls_database_parameters

--1. Buffer Cache Hit Ratio

SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;

--2. Library Cache Hit Ratio

SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;

--3. Data Dictionary Cache Hit Ratio

SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;

 

-- 테이블 스페이스 사용량

SELECT a.tablespace_name,
             a.total "Total(Mb)",
             a.total - b.free "Used(Mb)",
             nvl(b.free,0) "Free(Mb)",
             round((a.total - nvl(b.free,0))*100/total,0)  "Used(%)"
from    (   select     tablespace_name,
                            round((sum(bytes)/1024/1024),0) as total
               from       dba_data_files
               group by tablespace_name) a,
         (     select     tablespace_name,
                             round((sum(bytes)/1024/1024),0) as free
               from        dba_free_space
               group by  tablespace_name) b
where      a.tablespace_name = b.tablespace_name(+)
order by   a.tablespace_name;

 
--오라클서버의 메모리

select * from v$sgastat

select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool

 

--cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
--and a.spid = '675958'
order by c.PIECE

 
 
--cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '171'
order by c.PIECE

 

--프로세스 아이디를 이용하여 쿼리문 알아내기

select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '1708032' --1912870/
order by c.PIECE

 

--세션 죽이기(SID,SERAIL#)

--ALTER SYSTEM KILL SESSION '8,4093'

--오라클 세션과 관련된 테이블*/

--select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'

 

--현재 커서 수 확인

SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = 'ilips'
GROUP BY sid
ORDER BY cursor DESC

SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC

select * from v$session_wait

select sid, serial#, username, taddr, used_ublk, used_urec
 from v$transaction t, v$session s
 where t.addr = s.taddr;

select *  from sys.v_$open_cursor

 

--V$LOCK 을 사용한 잠금 경합 모니터링

SELECT s.username, s.sid, s.serial#, s.logon_time,
    DECODE(l.type, 'TM', 'TABLE LOCK',
          'TX', 'ROW LOCK',
       NULL) "LOCK LEVEL",
    o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL

 

--락이 걸린 세션 자세히 알아보기

select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'

 

--락이 걸린 세션 간단히 알아보기

select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
       a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
  and b.id1 = c.object_id
  and b.type = 'TM';

select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'

 
--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디

select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다

ALTER SYSTEM KILL SESSION '11,39061'

 

 

alter session으로 죽지않는 프로세스 죽이기

1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
2.su -오라클계정
3.sqlplus '/as sysdba''
4.connect system/sys
5.ALTER SYSTEM KILL SESSION '137,1723'

 

 

-- 특정 테이블에 사용된 쿼리
SELECT * FROM V$SQLAREA
WHERE parsing_schema_name = 'ING'
AND lower(sql_text) LIKE '%order_result_report_tbl%'
ORDER BY last_active_time DESC

-- 현재 세션에서 사용되는 쿼리
SELECT S.SID
     , S.SERIAL#
     , S.STATUS
     , S.PROCESS
     , S.OSUSER
     , A.SQL_TEXT
     , P.PROGRAM
  FROM V$SESSION S
     , V$SQLAREA A
     , V$PROCESS P
WHERE S.SQL_HASH_VALUE =  A.HASH_VALUE
  AND S.SQL_ADDRESS    =  A.ADDRESS
  AND S.PADDR          =  P.ADDR
  AND S.SCHEMANAME     = 'ING'
  AND S.STATUS         = 'ACTIVE'

--
select
 sql_text,
 sharable_mem + persistent_mem + runtime_mem memory,
 sorts,
 executions,
 first_load_time,
 invalidations,
 parse_calls,
 disk_reads,
 buffer_gets,
 rows_processed,
 round(rows_processed/greatest(executions,1)) row_ratio,
 round(disk_reads/greatest(executions,1)) disk_ratio,
 round(buffer_gets/greatest(executions,1)) buffer_ratio
from v$sqlarea
where
 executions > 100
or disk_reads > 1000
or buffer_gets > 1000
or rows_processed > 1000
order by
 executions * 250 + disk_reads * 25 + buffer_gets desc

 
--
SELECT
    PARSING_SCHEMA_NAME,
    executions,
    SQL_TEXT,
   disk_reads,
   buffer_gets,
    elapsed_time / executions AS elapsed_time,
    buffer_gets / decode(executions,0,1,executions) / 500 AS runtime,
    bind_data,
    MODULE,
    sharable_mem,
    persistent_mem,
    runtime_mem,
   rows_processed,
   round(rows_processed/greatest(executions,1)) row_ratio,
   round(disk_reads/greatest(executions,1)) disk_ratio,
   round(buffer_gets/greatest(executions,1)) buffer_ratio,
    LAST_LOAD_TIME,
    LAST_ACTIVE_TIME
FROM
    V$SQLAREA
WHERE
    LAST_LOAD_TIME BETWEEN SYSDATE - 1 AND SYSDATE
    AND PARSING_SCHEMA_NAME IN('WORLDGATE')
    AND SQL_TEXT LIKE 'SELECT%'
ORDER BY
    buffer_gets / decode(executions,0,1,executions) / 500 DESC
   
   
   
-- 10초 이상
select translate(sql_text,'&',':') ,
' Expected Run Time = '||
buffer_gets / decode(executions,0,1,executions) / 500 runt

from v$sqlarea
where buffer_gets / decode(executions,0,1,executions) / 500 > 10
and upper(sql_text) not like '%BEGIN%'
and upper(sql_text) not like '%SQLAREA%'
and upper(sql_text) not like '%DBA_%'
and upper(sql_text) not like '%USER_%'
and upper(sql_text) not like '%ALL_%'
order by executions desc

   

평균 메모리 사용량이 많은 SQL (상위 N개)

SELECT BUFFER_GETS,DISK_READS,

       EXECUTIONS,BUFFER_PER_EXEC,SQL_TEXT

FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,

   BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,

   SQL_TEXT

       FROM   V$SQLAREA

WHERE LAST_ACTIVE_TIME > trunc(SYSDATE)-8/24
AND  ROWNUM <=  5
       ORDER BY BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )

 

 

총 메모리 사용량이 많은 SQL (상위 N개)

SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT

FROM (SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT

      FROM V$SQLAREA

      ORDER BY BUFFER_GETS DESC  )

WHERE ROWNUM <= 5

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