Question on Tuning MTS(shared server) for 10g

Currently, I am running oracle10.2.0.3 on Solaris running MTS.

My problem is : system-administrator complaint the appserver sometimes get time out or slowness to connect
to oracle.

And then I look at V$queue:

PADDR TYPE QUEUED WAIT TOTALQ
---------------- ---------- ---------- ---------- ----------

00               COMMON              95      415309627 3735360550
0000000506538FD8 DISPATCHER          1   57343354 3935401708
0000000505530260 DISPATCHER          0   56537195 3880991039
00000005065397C0 DISPATCHER          0   57775475 4023677518
0000000505530A48 DISPATCHER          27   57890221 3998494824
0000000506539FA8 DISPATCHER          0   57908730 4055140635
0000000505531230 DISPATCHER          0          0          0
0000000505536140 DISPATCHER          0     300234   19868411
0000000506541640 DISPATCHER          0     296291   20327421

Originally, when my load (OS load ) is around 11 or 12, then I saw in the "QUEUED" column ,
the value is 95 , and then for other dispatcher, in the QUEUED column, I saw like 17 or 27.
To me, look like the dispatcher is not fast enough to service all the request, so some processes get
lined up in the "QUEUE".

So yesterday, I change max_dispatcher from 7 to 9 and dispatcher from 5 to 7

Today, even in high load (like 11 or 12) situation, I do queue on V$queue

PADDR TYPE QUEUED WAIT TOTALQ
---------------- ---------- ---------- ---------- ----------

00               COMMON              0  415391400 3735501536
0000000506538FD8 DISPATCHER          0   57347496 3935612160
0000000505530260 DISPATCHER          0   56540454 3881129879
00000005065397C0 DISPATCHER          0   57779800 4023890040
0000000505530A48 DISPATCHER          0   57893294 3998634646
0000000506539FA8 DISPATCHER          0   57912251 4055317875
0000000505531230 DISPATCHER          0          0          0
0000000505536140 DISPATCHER          0     304775   20117346
0000000506541640 DISPATCHER          0     299570   20493873

I don't see much processes lining up in the queue from querying the V $queue table
and look at the QUEUED column,
it is mostly either 0 or 1

So definitely, now, my system(oracle server) seem to have enough dispatcher now.

But system admin still complaint the appserver talk to oracle is slow.

I have shared_server 33, right now,
I am wondering how to "monitor" tune (or increase ) to have more shared_server.

Some people told me to look at my "large_pool_size".

If anyone have MTS experience and comment, please comment on this.
Really appreciate, this MTS thing is pretty confusing to me, and not much article to read, since most company are not running MTS.

Dominica Received on Thu Jul 16 2009 - 13:47:07 CDT