oracle shared server配置

来源:互联网 时间:1970-01-01


1、独占服务器(dedicatedserver) 
每一个session单独分配一个serverprocess,直到用户断开连接,才释放该进程所占用的资源。而大多数时候session都是idle的,所以存在占用资源的情况。
 
在有些情况下,必须使用独占服务器模式:(本地连接)
 

SQL> select username,SERVER,PROGRAM,OSUSER,MACHINEfrom v$session where username is not null; USERNAME SERVER PROGRAM OSUSER MACHINE------------------------------ --------------------------------------------------------- ------------------------------------------------------------SYS [email protected] (TNS V1-V3) oracle ora11g

 


2、shared server特点
 
共享服务器响应用户请求的步骤如下:
 
1) 客户传送一个请求到调度程序。
 
2) 调度程序将请求放在SGA中的请求队列中(share pool 或 large pool)。
 
3) 其中的一个共享服务器进程响应并处理这个请求。
 
4) 共享服务器进程把处理完的请求回复放到SGA中的响应队列中。
 
5) 调度器从响应队列中取出已经完成的请求。
 
6) 调度器把完成的请求回复给客户
 
关于SGA的请求和相应队列以及Dispatcher,具有如下的一些特点:
 
1)在SGA中只有一个请求队列。
 
2)共享服务器监控请求队列的使用情况。
 
3)请求队列遵循先进先出(FIFO)的原则。
 
4)共享服务器将已经完成的请求放在提交该请求的dispatcher所对应的response队列中。
 
5)每一个dispatcher在SGA中都由一个对应的response队列。
 
6)Dispatcher需要负责将完成的请求回复给相应的用户进程。
 
7)在同一个session中,用户进程始终连接的是同一个Dspatcher。
 
3、配置shared server
 
1)、配置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 15:22 ? 00:00:00 ora_pmon_prodoracle 21443 1 0 15:22 ? 00:00:00 ora_psp0_prodoracle 21445 1 1 15:22 ? 00:00:14 ora_vktm_prodoracle 21449 1 0 15:22 ? 00:00:00 ora_gen0_prodoracle 21451 1 0 15:22 ? 00:00:00 ora_diag_prodoracle 21453 1 0 15:22 ? 00:00:00 ora_dbrm_prodoracle 21455 1 0 15:22 ? 00:00:00 ora_dia0_prodoracle 21457 1 0 15:22 ? 00:00:00 ora_mman_prodoracle 21459 1 0 15:22 ? 00:00:00 ora_dbw0_prodoracle 21461 1 0 15:22 ? 00:00:00 ora_dbw1_prodoracle 21463 1 0 15:22 ? 00:00:00 ora_lgwr_prodoracle 21465 1 0 15:22 ? 00:00:00 ora_ckpt_prodoracle 21467 1 0 15:22 ? 00:00:00 ora_smon_prodoracle 21469 1 0 15:22 ? 00:00:00 ora_reco_prodoracle 21471 1 0 15:22 ? 00:00:01 ora_mmon_prodoracle 21473 1 0 15:22 ? 00:00:00 ora_mmnl_prodoracle 21475 1 0 15:22 ? 00:00:00 ora_d000_prodoracle 21477 1 0 15:22 ? 00:00:00 ora_s000_prodoracle 21485 1 0 15:23 ? 00:00:00 ora_qmnc_prodoracle 21514 1 0 15:23 ? 00:00:00 ora_q000_prodoracle 21516 1 0 15:23 ? 00:00:00 ora_q001_prodoracle 21862 1 0 15:33 ? 00:00:00 ora_smco_prodoracle 21881 1 0 15:33 ? 00:00:00 ora_w000_prodoracle 21972 1 0 15:35 ? 00:00:00 ora_d001_prod --调度进程启用oracle 21974 1 0 15:35 ? 00:00:00 ora_d002_prodoracle 21976 1 0 15:35 ? 00:00:00 ora_d003_prodoracle 21978 1 0 15:35 ? 00:00:00 ora_d004_prod

 


  
2)、配置sharedserver
SQL> alter system set shared_servers=10; System altered. oracle 21441 1 0 15:22 ? 00:00:00 ora_pmon_prodoracle 21443 1 0 15:22 ? 00:00:00 ora_psp0_prodoracle 21445 1 1 15:22 ? 00:00:16 ora_vktm_prodoracle 21449 1 0 15:22 ? 00:00:00 ora_gen0_prodoracle 21451 1 0 15:22 ? 00:00:00 ora_diag_prodoracle 21453 1 0 15:22 ? 00:00:00 ora_dbrm_prodoracle 21455 1 0 15:22 ? 00:00:00 ora_dia0_prodoracle 21457 1 0 15:22 ? 00:00:00 ora_mman_prodoracle 21459 1 0 15:22 ? 00:00:00 ora_dbw0_prodoracle 21461 1 0 15:22 ? 00:00:00 ora_dbw1_prodoracle 21463 1 0 15:22 ? 00:00:00 ora_lgwr_prodoracle 21465 1 0 15:22 ? 00:00:00 ora_ckpt_prodoracle 21467 1 0 15:22 ? 00:00:00 ora_smon_prodoracle 21469 1 0 15:22 ? 00:00:00 ora_reco_prodoracle 21471 1 0 15:22 ? 00:00:01 ora_mmon_prodoracle 21473 1 0 15:22 ? 00:00:00 ora_mmnl_prodoracle 21475 1 0 15:22 ? 00:00:00 ora_d000_prodoracle 21477 1 0 15:22 ? 00:00:00 ora_s000_prodoracle 21485 1 0 15:23 ? 00:00:00 ora_qmnc_prodoracle 21514 1 0 15:23 ? 00:00:00 ora_q000_prodoracle 21516 1 0 15:23 ? 00:00:00 ora_q001_prodoracle 21862 1 0 15:33 ? 00:00:00 ora_smco_prodoracle 21881 1 0 15:33 ? 00:00:00 ora_w000_prodoracle 21972 1 0 15:35 ? 00:00:00 ora_d001_prodoracle 21974 1 0 15:35 ? 00:00:00 ora_d002_prodoracle 21976 1 0 15:35 ? 00:00:00 ora_d003_prodoracle 21978 1 0 15:35 ? 00:00:00 ora_d004_prodoracle 22098 1 0 15:38 ? 00:00:00 ora_s001_prodoracle 22100 1 0 15:38 ? 00:00:00 ora_s002_prodoracle 22102 1 0 15:38 ? 00:00:00 ora_s003_prodoracle 22104 1 0 15:38 ? 00:00:00 ora_s004_prodoracle 22106 1 0 15:38 ? 00:00:00 ora_s005_prodoracle 22108 1 0 15:38 ? 00:00:00 ora_s006_prod --server进程启动oracle 22110 1 0 15:38 ? 00:00:00 ora_s007_prodoracle 22112 1 015:38 ? 00:00:00 ora_s008_prodoracle 22114 1 0 15:38 ? 00:00:00 ora_s009_prod

 


3)客户端配置
 
在Oracle server 启动listener
 
客户端配置tnsnames.ora
 
TEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=ora11g)(PORT = 1521)) (CONNECT_DATA = (SEVER = SHARED) (SERVICE_NAME = prod) ) ) [[email protected] 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)

 


测试:
 
[[email protected] admin]$ sqlplus system/[email protected] 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 [email protected] (TNS V1-V3) oracle ora11gSYSTEM NONE [email protected] (TNS V1-V3) oracle ora11g

 


 
4、监控共享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)在sga 中配置large pool
 
---监控dispatch的使用情况,如果使用率超过50%,需要增加dispatch
SQL> selectname,(busy/(busy+idle))*100 "busyrate" from v$dispatcher; NAME busy rate---- ----------D000 0D001 0D002 0D003 0D004 .003085705

 


 
关注客户请求在请求队列中等待了多长时间,请求队列中等待的时间越长,则说明客户等待的时间也越长。
 
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

 





相关阅读:
Top