Oracle shared server Configuration

Oracle shared server Configuration
1. A serverprocess is allocated to each session of the dedicatedserver until the user is disconnected. 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 following steps for the shared server to respond to user requests: 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 replies the completed request to the customer's SGA request, corresponding queue, and Dispatcher, which has 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) and dispatchs

SQL> alter system setdispatchers = "(PROTOCOL = tcp) (DISPATCHERS = 5)"; System altered. SQL> show parameters dispa NAME TYPE VALUE types ------------------------------ dispatchers string (PROTOCOL = tcp) (DISPATCHERS = 5) max_dispatchers integer oracle 21441 1 0? 00:00:00 ora_pmon_prodoracle 21443 1 0? 00:00:00 ora_psp0_prodoracle 21445 1? 00:00:14 ora_vktm_prodoracle 21449 1 0? 00:00:00 ora_gen0_prodoracle 21451 1 0? 00:00:00 ora_diag_prodoracle 21453 1 0? 00:00:00 ora_dbrm_prodoracle 21455 1 0? 00:00:00 ora_dia0_prodoracle 21457 1 0? 00:00:00 ora_mman_prodoracle 21459 1 0? 00:00:00 ora_dbw0_prodoracle 21461 1 0? 00:00:00 ora_dbw1_prodoracle 21463 1 0? 00:00:00 ora_lgwr_prodoracle 21465 1 0? 00:00:00 ora_ckpt_prodoracle 21467 1 0? 00:00:00 ora_smon_prodoracle 21469 1 0? 00:00:00 ora_reco_prodoracle 21471 1 0? 00:00:01 ora_mmon_prodoracle 21473 1 0? 00:00:00 ora_mmnl_prodoracle 21475 1 0? 00:00:00 ora_d000_prodoracle 21477 1 0? 00:00:00 ora_s000_prodoracle 21485 1 0? 00:00:00 ora_qmnc_prodoracle 21514 1 0? 00:00:00 ora_q000_prodoracle 21516 1 0? 00:00:00 ora_q001_prodoracle 21862 1 0? 00:00:00 ora_smco_prodoracle 21881 1 0? 00:00:00 ora_w000_prodoracle 21972 1 0? 00:00:00 ora_d001_prod -- the scheduling process enables oracle 21974 1 0? 00:00:00 ora_d002_prodoracle 21976 1 0? 00:00:00 ora_d003_prodoracle 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_prodoracle 21443 1 0? 00:00:00 ora_psp0_prodoracle 21445 1? 00:00:16 ora_vktm_prodoracle 21449 1 0? 00:00:00 ora_gen0_prodoracle 21451 1 0? 00:00:00 ora_diag_prodoracle 21453 1 0? 00:00:00 ora_dbrm_prodoracle 21455 1 0? 00:00:00 ora_dia0_prodoracle 21457 1 0? 00:00:00 ora_mman_prodoracle 21459 1 0? 00:00:00 ora_dbw0_prodoracle 21461 1 0? 00:00:00 ora_dbw1_prodoracle 21463 1 0? 00:00:00 ora_lgwr_prodoracle 21465 1 0? 00:00:00 ora_ckpt_prodoracle 21467 1 0? 00:00:00 ora_smon_prodoracle 21469 1 0? 00:00:00 ora_reco_prodoracle 21471 1 0? 00:00:01 ora_mmon_prodoracle 21473 1 0? 00:00:00 ora_mmnl_prodoracle 21475 1 0? 00:00:00 ora_d000_prodoracle 21477 1 0? 00:00:00 ora_s000_prodoracle 21485 1 0? 00:00:00 ora_qmnc_prodoracle 21514 1 0? 00:00:00 ora_q000_prodoracle 21516 1 0? 00:00:00 ora_q001_prodoracle 21862 1 0? 00:00:00 ora_smco_prodoracle 21881 1 0? 00:00:00 ora_w000_prodoracle 21972 1 0? 00:00:00 ora_d001_prodoracle 21974 1 0? 00:00:00 ora_d002_prodoracle 21976 1 0? 00:00:00 ora_d003_prodoracle 21978 1 0? 00:00:00 ora_d004_prodoracle 22098 1 0? 00:00:00 ora_s001_prodoracle 22100 1 0? 00:00:00 ora_s002_prodoracle 22102 1 0? 00:00:00 ora_s003_prodoracle 22104 1 0? 00:00:00 ora_s004_prodoracle 22106 1 0? 00:00:00 ora_s005_prodoracle 22108 1 0? 00:00:00 ora_s006_prod -- the server process starts oracle 22110 1 0? 00:00:00 ora_s007_prodoracle 22112 1? 00:00:00 ora_s008_prodoracle 22114 1 0? 00:00:00 ora_s009_prod

 

3) The client configuration starts the listener client on the Oracle server to configure tnsnames. ora

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, 2013, Oracle.  All rights reserved.  Used parameter files:    Used TNSNAMES adapter to resolve the aliasAttempting 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, 2013, Oracle.  All rights reserved.    Connected to:Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit ProductionWith 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 DEDICATEDSYSTEM                                 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                        ora11gSYSTEM                         NONE      sqlplus@ora11g (TNS V1-V3)                       oracle                         ora11g

 

4. Monitoring Shared server

SQL> col network for a50SQL> 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))  00000000720BA7A0 WAIT             YES     132806         0D001(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=49380))  00000000720C2D60 WAIT             YES      56575          0D002(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=21687))  00000000720C3E18 WAIT             YES      56572         0D003(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=53218))  00000000720C4ED0 WAIT             YES      56569         0D004 (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=51052))  00000000720C5F88 WAIT             YES      56563          2

 

1) configure the large pool in sga to monitor the usage of dispatch. If the usage rate exceeds 50%, you need to add a dispatch.

SQL> selectname,(busy/(busy+idle))*100  "busyrate"  from  v$dispatcher;  NAME busy rate---- ----------D000          0D001          0D002          0D003          0D004 .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------------------------------------------------------------------------           0hundredths of seconds           0hundredths of seconds