Oracle Shared Server Configuration

Oracle Shared Server Configuration

1. Exclusive server (dedicatedserver)

Each session is assigned a serverprocess independently. Resources occupied by the process are released only when the user disconnects. In most cases, sessions are idle, so resources are occupied.

In some cases, the exclusive server mode must be used: (local connection)

SQL> select username, SERVER, PROGRAM, OSUSER, MACHINEfrom v $ session where username is not null;

USERNAME SERVER PROGRAM OSUSER MACHINE
---------------------------------------------------------------------------------------------------------------------------------------------------
SYS DEDICATEDsqlplus @ ora11g (TNS V1-V3) Oracle ora11g

2. shared server features

The steps for the shared server to respond to user requests are as follows:

1) The customer sends a request to the scheduler.

2) The scheduler places the request in the Request queue (share pool or large pool) in the SGA ).

3) One of the Shared Server Processes responds to and processes the request.

4) The Shared Server process places the processed request replies to the response queue in the SGA.

5) The scheduler extracts completed requests from the response queue.

6) The scheduler returns the completed request to the customer.

SGA requests, corresponding queues, and Dispatcher have the following features:

1) there is only one request queue in SGA.

2) The Shared Server monitors the usage of the Request queue.

3) The request queue follows the FIFO principle.

4) The Shared Server places the completed requests in the response queue corresponding to the dispatcher that submits the request.

5) Each dispatcher is assigned a corresponding response queue in SGA.

6) The Dispatcher is responsible for replying the completed request to the corresponding user process.

7) in the same session, the user process is always connected to the same Dspatcher.

3. Configure shared server

1) Configure dispatchs

SQL> alter system setdispatchers = "(PROTOCOL = tcp) (DISPATCHERS = 5 )";

System altered.

SQL> show parameters dispa

NAME TYPE VALUE
-----------------------------------------------------------------------------
Dispatchers string (PROTOCOL = tcp) (DISPATCHERS = 5)
Max_dispatchers integer

Oracle 21441 1 0? 00:00:00 ora_pmon_prod
Oracle 21443 1 0? 00:00:00 ora_psp0_prod
Oracle 21445 1? 00:00:14 ora_vktm_prod
Oracle 21449 1 0? 00:00:00 ora_gen0_prod
Oracle 21451 1 0? 00:00:00 ora_diag_prod
Oracle 21453 1 0? 00:00:00 ora_dbrm_prod
Oracle 21455 1 0? 00:00:00 ora_dia0_prod
Oracle 21457 1 0? 00:00:00 ora_mman_prod
Oracle 21459 1 0? 00:00:00 ora_dbw0_prod
Oracle 21461 1 0? 00:00:00 ora_dbw1_prod
Oracle 21463 1 0? 00:00:00 ora_lgwr_prod
Oracle 21465 1 0? 00:00:00 ora_ckpt_prod
Oracle 21467 1 0? 00:00:00 ora_smon_prod
Oracle 21469 1 0? 00:00:00 ora_reco_prod
Oracle 21471 1 0? 00:00:01 ora_mmon_prod
Oracle 21473 1 0? 00:00:00 ora_mmnl_prod
Oracle 21475 1 0? 00:00:00 ora_d000_prod
Oracle 21477 1 0? 00:00:00 ora_s000_prod
Oracle 21485 1 0? 00:00:00 ora_qmnc_prod
Oracle 21514 1 0? 00:00:00 ora_q000_prod
Oracle 21516 1 0? 00:00:00 ora_q001_prod
Oracle 21862 1 0? 00:00:00 ora_smco_prod
Oracle 21881 1 0? 00:00:00 ora_w000_prod
Oracle 21972 1 0? 00:00:00 ora_d001_prod -- scheduling process enabled
Oracle 21974 1 0? 00:00:00 ora_d002_prod
Oracle 21976 1 0? 00:00:00 ora_d003_prod
Oracle 21978 1 0? 00:00:00 ora_d004_prod

2) configure the sharedserver
SQL> alter system set shared_servers = 10;

System altered.

Oracle 21441 1 0? 00:00:00 ora_pmon_prod
Oracle 21443 1 0? 00:00:00 ora_psp0_prod
Oracle 21445 1? 00:00:16 ora_vktm_prod
Oracle 21449 1 0? 00:00:00 ora_gen0_prod
Oracle 21451 1 0? 00:00:00 ora_diag_prod
Oracle 21453 1 0? 00:00:00 ora_dbrm_prod
Oracle 21455 1 0? 00:00:00 ora_dia0_prod
Oracle 21457 1 0? 00:00:00 ora_mman_prod
Oracle 21459 1 0? 00:00:00 ora_dbw0_prod
Oracle 21461 1 0? 00:00:00 ora_dbw1_prod
Oracle 21463 1 0? 00:00:00 ora_lgwr_prod
Oracle 21465 1 0? 00:00:00 ora_ckpt_prod
Oracle 21467 1 0? 00:00:00 ora_smon_prod
Oracle 21469 1 0? 00:00:00 ora_reco_prod
Oracle 21471 1 0? 00:00:01 ora_mmon_prod
Oracle 21473 1 0? 00:00:00 ora_mmnl_prod
Oracle 21475 1 0? 00:00:00 ora_d000_prod
Oracle 21477 1 0? 00:00:00 ora_s000_prod
Oracle 21485 1 0? 00:00:00 ora_qmnc_prod
Oracle 21514 1 0? 00:00:00 ora_q000_prod
Oracle 21516 1 0? 00:00:00 ora_q001_prod
Oracle 21862 1 0? 00:00:00 ora_smco_prod
Oracle 21881 1 0? 00:00:00 ora_w000_prod
Oracle 21972 1 0? 00:00:00 ora_d001_prod
Oracle 21974 1 0? 00:00:00 ora_d002_prod
Oracle 21976 1 0? 00:00:00 ora_d003_prod
Oracle 21978 1 0? 00:00:00 ora_d004_prod
Oracle 22098 1 0? 00:00:00 ora_s001_prod
Oracle 22100 1 0? 00:00:00 ora_s002_prod
Oracle 22102 1 0? 00:00:00 ora_s003_prod
Oracle 22104 1 0? 00:00:00 ora_s004_prod
Oracle 22106 1 0? 00:00:00 ora_s005_prod
Oracle 22108 1 0? 00:00:00 ora_s006_prod -- server process startup
Oracle 22110 1 0? 00:00:00 ora_s007_prod
Oracle 22112 1? 00:00:00 ora_s008_prod
Oracle 22114 1 0? 00:00:00 ora_s009_prod

3) Client Configuration

Start listener on Oracle server

Configure tnsnames. ora on the client

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = ora11g) (PORT = 1521 ))
(CONNECT_DATA =
(SEVER = SHARED)
(SERVICE_NAME = prod)
)
)

[Oracle @ ora11g admin] $ tnsping test

TNS Ping Utility for Linux: Version11.2.0.4.0-Production on 04-NOV-2015 15:42:39

Copyright (c) 1997,201 3, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ora11g) (PORT = 1521) (CONNECT_DATA = (SEVER = SHARED) (SERVICE_NAME = prod )))
OK (10 msec)

Test:

[Oracle @ ora11g admin] $ sqlplus system/oracle @ test

SQL * Plus: Release 11.2.0.4.0 Production onWed Nov 4 15:42:49 2015

Copyright (c) 1982,201 3, Oracle. All rights reserved.


Connected:
Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL>

SQL> select username, sid, server fromv $ session where username is not null;

USERNAME SID SERVER
-------------------------------------------------
SYS 9 DEDICATED
SYSTEM 12 NONE


SQL> selectusername, SERVER, PROGRAM, OSUSER, MACHINE from v $ session where username is notnull;

USERNAME SERVER PROGRAM OSUSER MACHINE
---------------------------------------------------------------------------------------------------------------------------------------------------
SYS DEDICATEDsqlplus @ ora11g (TNS V1-V3) oracle ora11g
System none sqlplus @ ora11g (TNS V1-V3) oracle ora11g

4. Monitoring Shared server

SQL> col network for a50
SQL> select name, NETWORK, PADDR, STATUS, ACCEPT, idle, busy from v $ dispatcher;

NAME NETWORK PADDR STATUS ACC IDLE BUSY
-------------------------------------------------------------------------------------------------------------
D000 (ADDRESS = (PROTOCOL = tcp) (HOST = ora11g) (PORT = 33225) 255.255.720ba7a0 wait yes 132806 0
D001 (ADDRESS = (PROTOCOL = tcp) (HOST = ora11g) (PORT = 49380) 201710000720c2d60 wait yes 56575 0
D002 (ADDRESS = (PROTOCOL = tcp) (HOST = ora11g) (PORT = 21687) 255.255.720c3e18 wait yes 56572 0
D003 (ADDRESS = (PROTOCOL = tcp) (HOST = ora11g) (PORT = 53218) 255.255.720c4ed0 wait yes 56569 0
D004 (ADDRESS = (PROTOCOL = tcp) (HOST = ora11g) (PORT = 51052) 255.255.720c5f88 wait yes 56563 2

1) Configure large pool in sga

--- Monitor the usage of dispatch. If the usage exceeds 50%, you need to add dispatch

SQL> selectname, (busy/(busy + idle) * 100 "busyrate" from v $ dispatcher;

NAME busy rate
--------------
D000 0
D001 0
D002 0
D003 0
D004. 003085705

Pay attention to the length of time the customer requests wait in the Request queue. The longer the waiting time in the Request queue, the longer the customer waits.

SQL> select decode (totalq, 0, 'norequests') "wait time ",
2 wait/totalq | 'hundredths ofseconds '"Average wait time per request"
3 from v $ queue
4 where type = 'common ';

Wait time Average wait time per request
------------------------------------------------------------------------
0 hundredths of seconds
0 hundredths of seconds