[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 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、监控共享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)) 00000000720BA7A0 WAIT YES 132806 0
D001(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=49380)) 00000000720C2D60 WAIT YES 56575 0
D002(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=21687)) 00000000720C3E18 WAIT YES 56572 0
D003(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g)(PORT=53218)) 00000000720C4ED0 WAIT YES 56569 0
D004 (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 0
D001 0
D002 0
D003 0
D004 .003085705
关注客户请求在请求队列中等待了多长时间，请求队列中等待的时间越长，则说明客户等待的时间也越长。