Re: Question on Tuning MTS(shared server) for 10g

From: joel garry <joel-garry_at_home.com>
Date: Thu, 16 Jul 2009 14:11:32 -0700 (PDT)
Message-ID: <5b3e76a2-4a7e-4468-be85-327a0c569ad8_at_q40g2000prh.googlegroups.com>

On Jul 16, 11:47 am, "domin..._at_gmail.com" <domin..._at_gmail.com> wrote:
> Hi All,
>
> 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.
>

My MTS experience after initial training ~14 years ago is in throwing it out, but I'll comment. You've got a metric that shows you've pretty much done as much as you can on the server side. Your admin needs to show more detail on the network side - how exactly is he showing slowness? Grabbing blindly into my hat and throwing out what I find, look at these:

Network response time when things are slow. Is the server doing some of its own housekeeping at times? Matching frame size - search the oracle docs, metalink, google about tcp, sdu, window size, nodelay and how to turn on network tracing if you are using sqlnet.
How does the app connect? JDBC, ODBC... How does the app deal with closing sessions? Are there sessions being left about? Are you seeing lots of fin_wait_2 on netstat -a (or whatever Solaris uses to see net stats) on the server side? How does the app deal with slow connection requests? Jonathan Lewis has a classic story on his site about a middle tier storming new connections when access to certain kernel tables slow things down. Does the app make a new connection with every access? And of course the basic tuning question, is the app doing more than it needs to?

Tuning is usually iterative, if you make the network faster, your queue might grow...

Check out Cary Millsap's book if you haven't already. Tuning where there isn't a bottleneck may not help. You ought to find out where the bottlenecks are first.

jg

--
_at_home.com is bogus.
So much for Caeser's wife...
http://www3.signonsandiego.com/stories/2009/jul/16/1b16twitter235313-hacker-exposes-crucial-twitter-d/?uniontrib

Received on Thu Jul 16 2009 - 16:11:32 CDT