MTS prolblem

From: murthy <muthy69_at_yahoo.com>
Date: 8 Jul 2002 05:41:01 -0700
Message-ID: <300217f3.0207080441.6cf46da@posting.google.com>

Hi,
I'am running MTS on my server.

I wrote this in a file called ss.sql

col status form a13
col messages head "Mesages|Processed"
col Bytes head "Bytes|Processed"
col Breaks form 999
col circuit head "Current|Circuit"
col requests head "Requests|Processed"
col Idle head "Idle|Secs"
col Busy form 9999999 head "Busy|Secs"
col load form 99.99 head "Ave|Load"
select a.name,circuit,a.status,a.messages,a.bytes,a.breaks,a.requests,

       trunc(a.idle/100) Idle,trunc(a.busy/100) Busy, 
	 trunc((a.Busy/(a.Busy+a.Idle))*100,2) Load

from v$shared_server a
order by 1
/

I wrote this in a file called ct.sql

col sid form 999
col username form a10
col d head "Dispatcher|Name"
col ss head "Shared|Server"
col Bytes head "Bytes|Processed"
col messages head "Messages|Processed"
col status form a9
col queue head "Current|Queue"
select a.sid,a.username,

	 c.name d,d.name ss,
	 b.circuit,b.status,b.queue,b.messages,b.bytes 

from v$session a,v$circuit b,v$dispatcher c,v$shared_server d where a.saddr=b.saddr
and b.dispatcher=c.paddr
and b.server=d.paddr(+)
/

I opened an SQL*PLUS session and did the following.

sys_at_dba>@ss

     Current                   Mesages      Bytes          Requests
NAME Circuit  STATUS         Processed  Processed BREAKS  Processed

Idle     Busy    Ave
Secs     Secs   Load

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

S000 803DD8BC WAIT(RECEIVE)      34520   11310723     19      16153   
  15914     8757  35.49
S001 803DD9C0 WAIT(RECEIVE)        407     107916      3        205   
   2244     5755  71.94
S002 803DDFD8 EXEC               13839    7023485     18       6633   
  24577        8    .03
S003 00       WAIT(COMMON)           4       1197      0          2   
  24533        0    .00
S004 00       WAIT(COMMON)           0          0      0          0   
  24533        0    .00

5 rows selected.

SQL>@ct

                Disp Shar Current            Current           
Messages
 SID USERNAME   Name Serv Circuit  STATUS    Queue            

Processed

Bytes
Processed

---- ---------- ---- ---- -------- --------- ----------------
---------- ----------
   7 ARBOR      D001      803DDDD0 NORMAL    NONE                   
220      62803
   7 ARBOR      D001 S000 803DD8BC NORMAL    SERVER                 
172      42296
   8 ARBOR      D001      803DDAC4 NORMAL    NONE                  
9589    1871856
   9 ARBOR      D000      803DDBC8 NORMAL    NONE                    
48       7421
   9 ARBOR      D000 S001 803DD9C0 NORMAL    SERVER                  
58      15219
  10 SYS        D001 S002 803DDFD8 NORMAL    SERVER                 
101      20460
  11 ARBOR      D001      803DDCCC NORMAL    NONE                   
374      91164
  12 ARBOR      D000      803DDED4 NORMAL    NONE                  
1936     799016
  13 ARBOR      D000      803DE0DC NORMAL    NONE                    
44       9965

9 rows selected.

I appreciate if anyone could clarify my doubts.

1. I saw in Metalink that due to a bug, Shared Servers may sometimes get coverted to Dedeicated servers if you use Database links in your SQL. But i'am not using DB links. Also i started my listener way before the database instances. WHy are the status of the Shared Servers WAIT(RECEIVE) ?

2)Can a session have 2 circuits attached to it at the same time ? The output of @ct shows that SID's 7 and 9 have 2 circuits attached to them.

3) The Avg Load of the Shared Server's S000 and S001 is increasing over time
Why is this?

The first 2 rows output of @ss after a couple of minutes is given below

     Current                   Mesages      Bytes          Requests
NAME Circuit  STATUS         Processed  Processed BREAKS  Processed 
      
Idle     Busy    Ave
Secs     Secs   Load

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

S000 803DD8BC WAIT(RECEIVE)      34520   11310723     19      16153   
  15914     9245  36.74
S001 803DD9C0 WAIT(RECEIVE)        407     107916      3        205   
   2244     6244  73.55

Comparing this output with the output of @ss given at the top, you can see that even though the messages processed and bytes processed stay the same, The busy secs is increasing and hence the Avg load.

Why is this ?

Thanks is advance for any responce Received on Mon Jul 08 2002 - 07:41:01 CDT