Re: What is v$mts telling me?
Date: 24 Jul 1993 19:40:31 GMT
Message-ID: <22s37fINNdgc_at_gap.caltech.edu>
Since posting my original question I've learned a few points which I will share with you now.
Querying V$MTS
A simple query and its results are displayed below:
SELECT /* v$mts displays #'s for instance since last startup */
MAXIMUM_CONNECTIONS /* max # connections for each dispatcher */ ,SERVERS_STARTED /* # servers automatically spawned */ ,SERVERS_TERMINATED /* # servers killed after they were not needed*/ ,SERVERS_HIGHWATER /* max # of simultaneous servers running at any */ FROM V$MTS /* moment since last startup *//
Example results obtained after pushing the dbs through a flurry of activity:
MAXIMUM_CONNECTIONS SERVERS_STARTED SERVERS_TERMINATED SERVERS_HIGHWATER
------------------- --------------- ------------------ -----------------
29 12 9 5
A process listing was obtained from the machine during the flurry of activity to verify the above query:
% ps -aux | grep x7
o7 352 0.0 4.0 212 872 ? S 09:20 0:00 ora_pmon_x7 o7 353 0.0 4.0 208 856 ? S 09:20 0:01 ora_dbwr_x7 o7 354 0.0 3.7 208 792 ? S 09:20 0:00 ora_lgwr_x7 o7 355 0.0 0.0 332 0 ? IW 09:20 0:02 ora_smon_x7 o7 356 0.0 0.0 228 0 ? IW 09:20 0:00 ora_reco_x7 o7 357 0.0 0.6 280 132 ? S 09:20 1:03 ora_s000_x7 <- o7 358 0.0 0.6 276 132 ? S 09:20 0:52 ora_s001_x7 <- o7 455 10.3 24.1 220 5200 ? R 09:34 0:05 ora_s002_x7 <- o7 456 9.5 24.6 220 5292 ? R 09:34 0:04 ora_s003_x7 <- o7 457 9.5 23.7 220 5108 ? R 09:34 0:03 ora_s004_x7 <- o7 359 0.0 1.1 276 236 ? S 09:20 0:22 ora_d000_x7 o7 360 0.0 1.1 276 236 ? S 09:20 0:19 ora_d001_x7 o7 361 0.0 1.1 244 228 ? S 09:21 0:00 ora_d002_x7
The scripts used to generate the flurry of activity are displayed below:
#! /bin/csh -f
# mtstst.sh
# tests connections to mts
setenv ORACLE_HOME /q/o7
setenv ORACLE_SID x7
setenv TWO_TASK ava2_x7
set path = ($ORACLE_HOME/bin $path)
# run a set of scripts to make the dbs busy
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.1.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.2.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.3.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.4.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.5.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.6.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.7.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.8.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.9.out &
$ORACLE_HOME/bin/sqlplus / _at_mtstst.sql > mtstst.10.out &
# wait a minute for the dbs to get busy
sleep 66
# obtain a process listing
ps -aux |fgrep x7 > mtstst.ps.out
# query the v$mts table; quote the $ sign so the shell doesn't
# interpret it
$ORACLE_HOME/bin/sqlplus / > qrymts.lst << endofsql
select * from v\$mts
/
endofsql
# end of mtstst.sh
####################################################
rem
rem mtstst.sql
rem
SET ECHO ON
SELECT * FROM DICT ; SELECT * FROM V$MTS ; SELECT * FROM V$CIRCUIT ; SELECT * FROM V$DISPATCHER ; SELECT * FROM V$SESSION ; SELECT * FROM V$SHARED_SERVER ; SELECT * FROM DBA_TABLES ; SELECT * FROM DBA_INDEXES ; SELECT * FROM DBA_SYNONYMS ; SELECT * FROM CAT ;
EXIT
rem end of mtstst.sql
The discussion above points out the obvious observation: V$MTS contains some of the information a DBA needs to pick a suitable value for for MTS_SERVERS. If SERVERS_STARTED and SERVERS_TERMINATED are 0 and SERVERS_HIGHWATER = MTS_SERVERS, then the instance has not been very busy since it was last started; the DBA might consider lowering the value of MTS_SERVERS if the instance has been up a long time.
Daniel B. Bikle
dbikle_at_alumni.caltech.edu
415/854-9542
Received on Sat Jul 24 1993 - 21:40:31 CEST