对shared server的性能调优

在shared server可以手工配置delicated

# tnsnames.ora Network Configuration File:

# C:\oracle\product\10.1.0\db_1\network\admin\tnsnames.ora

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = MJW01)(PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = orcl)

(SRVR = DEDICATED) # Request a dedicated connection for DBA

)

)

对shared server的性能调优

1.定义large pool大小

large pool最小300K，最大2G，默认的oracle在shared server中每个session配置了250K，如果你没有配置large pool,user global area 就指定在了shared pool中，所以在shared server需要配置large pool，这样就不会影响到了shared pool的性能，

动态修改large pool大小

alter system set large pool=100m；

可以利用查询v$sgastat得到large pool有多少在应用，如

SQL> select * from v$sgastat where pool = 'large pool';

POOL NAME BYTES

----------- -------------------------- ---------

large pool free memory 251640

large pool session heap 48360

需要足够大的large pool来处理，一般一个连接connection需要1-3m，利用

select sum(value) "Max MTS Memory Allocated"from v$sesstat ss, v$statname st

where name = 'session uga memory max'and ss.statistic# =st.statistic#;

Max MTS Memory Allocated

------------------------

244416

可以得到shared server session用到的内存总大小

如果你有200个并发的connection，所以要240*200=50M，这个得到比较好的起始点，如果large pool 大小不合适，client就会报错。

2参看是否有足够的dispatchers

利用：

Select name, (busy / (busy + idle))*100

"Dispatcher % busy Rate"

From V$DISPATCHER

Protocol Dispatcher % Busy Rate

------------ ---------------------------

D000 .00070079

D001 .0059

如果busy超过了50%就要考虑加大dispatcher数目。

如：ALTER SYSTEM SET DISPATCHERS=”(PRO=TCP)(DIS=4)”;

3计算用户等待dispatchers的时间

利用v$queue和v$dispatcher视图

如：

SELECT decode(sum(totalq),0,’No Responses’,

Sum(wait)/sum(totalq)) “Average Wait time”

FROM V$QUEUE q, V$DISPATCHER d

WHERE q.type = ‘DISPATCHER’

AND q.paddr = d.paddr;

Average Wait Time

------------------

.0413

现在等待的时候0.04秒，观察长时间，如果这个数值一直在增长，则可以考虑增加dispatchers数目。

4决定是否有足够的shared servers

Select decode(totalq,0,’No Requests’) “Wait Time”,

Wait/totalq || ‘ hundredths of seconds’

“ Average Wait time per request”

from V$QUEUE

where type = ‘COMMON’

Wait Time Average Wait time per request