Ok, principalmente os detalhes de como é montado,como é usado, esse
tal pool de conexões - repito, a(s) notas metalink só serão de
utilidade SE vc tem o pool no banco, SE vc tem o pool controlado pela
tua aplicação/linguagem, ou por um programa de terceiros, o metalink
será de pouco ou nenhum uso. detalhe, o PLANO DE EXECUÇÃO, como eu
disse ,é IMPRESCINDÍVEL, tirado em sessões que emulem o mais
perfeitamente possível o seu uso. Se for CBO, e se vc ver que o plano
muda entre os dois testes (direto e mts/shared) , também não seria mal
um trace 10053.
Quanto à nota : sim , eu tinha consultado a minha pastinha de textos
arquivados, realmente não está mais online, vc pode substituir pelas
notas : Note:62143.1 Subject: Understanding and Tuning the Shared
Pool, Note:16656.1 Subject: Multi-Threaded Server Configuration and
Operation, Note:1005246.6 Subject: Using Oracle7 Multi-Threaded Server
(MTS), Note:1005259.6 Subject: Multi-Threaded Server (MTS)
Diagnostics, Note:126078.1 Subject: Net8 overview and explanation, e
Note:223299.1 Subject: Top Oracle 9i init.ora Parameters Affecting
Performance, esses todos eu consultei no metalink, sei q estão
online... Inclusive, esta última tem um aviso que pode ter MUITO aver :
"If PGA_AGGREGATE_TARGET is set in the init.ora and Dedicated Server
connection is used,
then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and
CREATE_bitmap_AREA_SIZE are ignored.
If Shared server connection is used then PGA_AGGREGATE_TARGET will be
ignored and the *_AREA_SIZE
parameter will take precedence. "
==> isso me veio à mente porque no último e-mail vc afirma que setou
PGA_AGGREGATE_TARGET, ora, SE vc está fazendo poolong via banco, deve
estar usando MTS/Shared Server, e portanto de acordo com a nota
PGA_AGGREGATE_TARGET será ** IGNORADO **.
De qquer maneira, segue a nota arquivada que eu citei :
Doc ID: Note:47684.1
Subject: Frequently Asked Questions on Multi-Threaded Server (MTS)
Type: FAQ
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 28-JAN-1998
Last Revision Date: 31-JAN-2002
ORACLE MTS/Networking
------------------------------
network.MTS
----------------
FREQUENTLY ASKED QUESTIONS
--------------------------
22-AUG-1999
CONTENTS
--------
1. What is Multi-Threaded Server (MTS)?
2. What users (O/S IDs) are working through which MTS dispatchers?
3. Is MTS supported on Windows NT?
4. How to trace MTS dispatchers and shared servers?
5. How to setup IPC for MTS?
6. How to estimate Shared Pool utilization?
7. Is MTS supported on Netware?
8. What diagnostics can be used for MTS?
9. How to calculate Shared Pool size when using MTS?
10. How to make a dedicated connection when MTS is configured on the
server?
11. Can local connections be made via MTS?
12. How to determine the maximum number of connections a dispatcher
can handle?
13. How to configure MTS?
14. How to turn off MTS without stopping the database?
15. How to start more dispatchers without stopping the database?
QUESTIONS & ANSWERS
1. What is MTS?
Answer
------
MTS allows many user processes to share very few server processes.
Without MTS, each
user process requires its own dedicated server process; a new server
process is created
for each client requesting a connection. A dedicated server process
remains associated
to the user process for the remainder of the connection. With MTS,
many user processes
connect to a dispatcher process. The dispatcher routes client requests
to the next
available shared server process. The advantage of MTS is that system
overhead is
reduced, so the number of users that can be supported is increased.
References
----------
<PRE:1013209.6> <PRE:1005246.6>
2. What users (O/S IDs) are working through which MTS dispatchers?
Answer
------
===========
Disclaimer:
===========
This script is provided for educational purposes only. It is NOT
supported by Oracle
World Wide Technical Support. The script has been tested and appears
to work as intended.
However, you should always test any script before relying on it.
PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the
way text editors,
email packages and operating systems handle text formatting (spaces,
tabs and carriage
returns), this script may not be in an executable state when you first
receive it.
=========
Abstract:
=========
The following script will report what users (O/S IDs) are working
through which MTS dispatchers.
=============
Requirements:
=============
Access privileges to the dynamic performance tables.
=======
Script:
=======
----------- cut --------------------- cut -------------- cut
--------------
SET ECHO off
REM NAME: tfs
REM USAGE:"@path/..."
REM -------------------------------------------------------------------
REM REQUIREMENTS:
REM Access to dynamic performance tables
REM -------------------------------------------------------------------
REM PURPOSE:
REM The purpose of this script is to report the dispatcher, session and
REM process mapping using MTS.
REM -------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM -------------------------------------------------------------------
REM Main text of script follows:
column network format a7
column disp format a4
column oracle_user format a11
column sid format 999
column serial# format 9999999
column os_user format a10
column terminal format a8
column program format a20
break on network on disp
select d.network network, d.name disp
, s.username oracle_user, s.sid sid, s.serial# serial#
, p.username os_user, p.terminal terminal, s.program program
from v$dispatcher d, v$circuit c, v$session s, v$process p
where d.paddr = c.dispatcher (+)
and c.saddr = s.saddr (+)
and s.paddr = p.addr (+)
order by d.network, d.name, s.username
----------- cut --------------------- cut -------------- cut
--------------
References
----------
<PRE:1019594.6>
3. Is MTS supported on Windows NT?
Answer
------
With Oracle 7 and SQL*Net v1/v2, MTS was not supported on Windows NT.
With Oracle
8.0.3 and Net 8.0.3, MTS is supported on Windows NT v4.0 using Winsock v2.
4. How to trace MTS dispatchers and shared servers?
Answer
------
Add the following parameters to the INIT<sid>.ORA file for each sid
that is running MTS and you want to create a Trace.
For dispatchers:
event="10248 trace name context forever, level X"
For shared servers:
event="10249 trace name context forever, level X"
X can be a value from 1 to 10, 10 being the most detailed trace. After
you add these
parameters to the INIT<sid>.ORA file, stop the instance and restart it
for the changes to take effect.
5. How to setup IPC for MTS?
Answer
------
IPC or local connections can be made using MTS. The only requirement
is that the
version of Oracle on the server platform in use supports MTS and that
the server
supports IPC. You also have to configure the LISTENER.ORA and
TNSNAMES.ORA files to use IPC.
LISTENER.ORA:
=============
LISTENER=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=IPC)
(KEY=<sid name>)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY=<alias in tnsnames.ora for the sid>)
)
)
CONNECT_TIMEOUT_LISTENER=10
STARTUP_WAIT_TIME_LISTENER=0
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=<sid name>)
(ORACLE_HOME=<home directory path for Oracle>)
)
)
Other addresses can be added to the address list if other protocols
are being used.
If the sid name and alias in tnsnames.ora are the same only one
address entry is required for IPC.
TNSNAMES.ORA:
=============
<alias>=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=IPC)
(KEY=<sid name>)
)
(CONNECT_DATA=
(SID=<sid name>)
)
)
INIT.ORA entries for MTS:
=========================
MTS_DISPATCHERS="IPC,2"
MTS_SERVERS=1
MTS_MAX_DISPATCHERS=6
MTS_MAX_SERVERS=3
MTS_SERVICE=<sid name>
MTS_LISTENER_ADDRESS="(ADDRESS=(PROTOCOL=IPC)(KEY=<sid name>))"
To make the multi-threaded IPC connection issue a connection command.
Example (using SQLPLUS):
sqlplus <username>/<password>@<alias from tnsnames.ora>
References
----------
<PRE:1005246.6>
6. How to estimate Shared Pool utilization?
Answer
------
===========
Disclaimer:
===========
This script is provided for educational purposes only. It is NOT
supported by Oracle
World Wide Technical Support. The script has been tested and appears
to work as intended.
However, you should always test any script before relying on it.
PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way
text editors,
email packages and operating systems handle text formatting (spaces,
tabs and carriage
returns), this script may not be in an executable state when you first
receive it.
=========
Abstract:
=========
This script estimates shared pool utilization based on current
database usage. This
should be run during peak operation, after all stored objects (i.e.
packages, views) have been loaded.
=============
Requirements:
=============
SELECT privileges on V$ tables
=======
Script:
=======
----------- cut ---------------------- cut -------------- cut
--------------
SET ECHO off
REM NAME: TFSSPUTL.SQL
REM USAGE:"@path/tfssputl"
REM
------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$ tables
REM
------------------------------------------------------------------------
REM PURPOSE:
REM Estimates shared pool utilization based on current database
REM usage. This should be run during peak operation, after all
REM stored objects i.e. packages, views have been loaded.
REM
------------------------------------------------------------------------
REM EXAMPLE:
REM Obj mem: 2536573 bytes
REM Shared sql: 4101742 bytes
REM Cursors: 2125 bytes
REM Free memory: 968976 bytes (.92MB)
REM Shared pool utilization (total): 7968528 bytes (7.6MB)
REM Shared pool allocation (actual): 9000000 bytes (8.58MB)
REM Percentage Utilized: 89%
REM
REM
------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM
------------------------------------------------------------------------
REM Main text of script follows:
set echo off
spool tfssputl.lst
Rem If running MTS uncomment the mts calculation and output
Rem commands.
set serveroutput on;
declare
object_mem number;
shared_sql number;
cursor_mem number;
mts_mem number;
used_pool_size number;
free_mem number;
pool_size varchar2(512); -- same as V$PARAMETER.VALUE
begin
-- Stored objects (packages, views)
select sum(sharable_mem) into object_mem from v$db_object_cache;
-- Shared SQL -- need to have additional memory if dynamic SQL used
select sum(sharable_mem) into shared_sql from v$sqlarea;
-- User Cursor Usage -- run this during peak usage.
-- assumes 250 bytes per open cursor, for each concurrent user.
select sum(250*users_opening) into cursor_mem from v$sqlarea;
-- For a test system -- get usage for one user, multiply by # users
-- select (250 * value) bytes_per_user
-- from v$sesstat s, v$statname n
-- where s.statistic# = n.statistic#
-- and n.name = 'opened cursors current'
-- and s.sid = 25; -- where 25 is the sid of the process
-- MTS memory needed to hold session information for shared server users
-- This query computes a total for all currently logged on users (run
-- during peak period). Alternatively calculate for a single user and
-- multiply by # users.
select sum(value) into mts_mem from v$sesstat s, v$statname n
where s.statistic#=n.statistic#
and n.name='session uga memory max';
-- Free (unused) memory in the SGA: gives an indication of how much memory
-- is being wasted out of the total allocated.
select bytes into free_mem from v$sgastat
where name = 'free memory';
-- For non-MTS add up object, shared sql, cursors and 20% overhead.
used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem));
-- For MTS mts contribution needs to be included (comment out previous
line)
-- used_pool_size :=
round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));
select value into pool_size from v$parameter where
name='shared_pool_size';
-- Display results
dbms_output.put_line ('Object mem: '||to_char (object_mem) || ' bytes');
dbms_output.put_line ('Shared SQL: '||to_char (shared_sql) || ' bytes');
dbms_output.put_line ('Cursors: '||to_char (cursor_mem) || ' bytes');
-- dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes');
dbms_output.put_line ('Free memory: '||to_char (free_mem) || ' bytes ' ||
'('
|| to_char(round(free_mem/1024/1024,2)) || 'MB)');
dbms_output.put_line ('Shared pool utilization (total): '||
to_char(used_pool_size) || ' bytes ' || '(' ||
to_char(round(used_pool_size/1024/1024,2)) || 'MB)');
dbms_output.put_line ('Shared pool allocation (actual): '|| pool_size ||'
bytes ' || '(' || to_char(round(pool_size/1024/1024,2)) || 'MB)');
dbms_output.put_line ('Percentage Utilized: '||to_char
(round(used_pool_size/pool_size*100)) || '%');
end;
/
spool off
----------- cut ---------------------- cut -------------- cut
--------------
=========
Examples:
=========
Object mem: 2536573 bytes
Shared SQL: 4101742 bytes
Cursors: 2125 bytes
Free memory: 968976 bytes (.92MB)
Shared pool utilization (total): 7968528 bytes (7.6MB)
Shared pool allocation (actual): 9000000 bytes (8.58MB)
Percentage Utilized: 89%
References
----------
<PRE:1020183.6>
7. Is MTS supported on Netware?
Answer
------
NetWare has been designed without a need for MTS. The design of MTS
reduces process
loads for very large installations supporting hundreds of users.
Oracle on NetWare
is completely implemented as a multi-threaded application.
8. What diagnostics can be used for MTS?
Answer
------
The components of MTS consist of processes on the system,
communication software
and the shared global section (SGA). Below is a map of MTS which shows
how these
pieces fit together. Diagnostic information about all of the above
components are
available to privileged accounts in SQLDBA, Server Manager, and SQL*Plus.
SQL*Net
+--------+ V2 +------------+ SGA +---------+ SGA +--------+
| client |<-------->| dispatcher |<----->| circuit |<----->| shared |
+--------+ +------------+ +---------+ | server |
"JSMITH" "ORA_<sid>_D000" | +--------+
|
"ORA_<sid>_S000"
+---------+
session |
+---------+
For each SQL*Net V2 MTS client session, there exists exactly one row
in the circuit
view which describes the current status of the client session.
Some of the views containing MTS information are:
V$CIRCUIT
V$SHARED_SERVER
V$DISPATCHER
V$MTS
V$QUEUE
V$SESSION
These views are readable by SYS and SYSTEM by default. The DBA can
provide access
to these views to other users on the database by granting them the
MONITORER role.
References
----------
For detailed information on the diagnostic queries see <PRE:1005259.6>
9. How to calculate Shared Pool size when using MTS?
Answer
------
A)If you are using MTS, then you will need to allow enough memory for
all the shared
server users to put their session memory in the shared pool. This can
be measured for one user with the following query:
select value sess_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory'
and s.sid = 23; -- replace 23 with session id of user being
measured
A more conservative value to use is the maximum session memory that
was ever allocated by the user:
select value sess_max_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory max'
and s.sid = 23; -- replace 23 with session id of user being
measured
To select this value for all the currently logged on users the
following query can be used:
select sum(value) all_sess_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory max';
B) OVERHEAD
If the system is making heavy use of dynamic SQL this 20% may not be
enough, since
this will come out of the runtime heap in the shared pool. You will
need to add a
minimum of 20-30% overhead to the values calculated above to allow for
unexpected
and unmeasured usage of the shared pool other than object memory,
shared sql or cursor
memory. If the system is making heavy use of dynamic SQL 20% may not
be enough, since
this will come out of the runtime heap in the shared pool.
10. How to make a dedicated connection when MTS is configured on the
server?
Answer
------
Add the (SERVER=DEDICATED) parameter to the tnsnames.ora file:
<service name> =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = <community>)
(PROTOCOL = <protocol>)
(Host = <server>)
(Port = 1526)
)
)
(CONNECT_DATA =
(SID = <sid>)
(SERVER=DEDICATED) <------------
)
)
NOTE: If Oracle Names is configured for your network, no TNSNAMES.ORA
is created.
You therefore cannot create a service name for a dedicated connection.
The workaround
this limitation (if required for testing purposes, only) is to add a
USE_DEDICATED_SERVER=ON
parameter to the SQLNET.ORA file. This parameter makes all connections
from a client workstation DEDICATED.
11. Can local Unix connections be made via MTS?
Answer
------
When installing SQL*Net V2 or Net 8 on UNIX, you will automatically
install the two
available interprocess communication protocol adapters, IPC and BEQ.
The implementation
of these adapters will vary by port: check the port's Installation and
Configuration Guide for any port-specific details.
The IPC adapter, which is implemented on top of Unix Domain Sockets,
is used for local connections to the MTS.
The BEQ adapter, which is implemented on top of Unix PIPES, cannot be
configured
for MTS. It is used to bequeath, or spawn a process and connected to
them via Unix PIPES.
12. How to determine the maximum number of connections a dispatcher
can handle?
Answer
------
The maximum number of connections that a dispatcher can handle is port
specific.
One can see the specific number for your operating system by issuing
the command:
lsnrctl services
P713 has 2 service handlers
DEDICATED SERVER established:5 refused:0
DISPATCHER established:0 refused:0 current:0 max:60 state:ready
D000 (machine: technique, pid: 29096)
(ADDRESS=(PROTOCOL=ipc)(DEV=7)(KEY=#29096.1))
It is 60 in this case.
Any changes you make to the sessions in init.ora are reflected in the
maximum_connections
column of v$mts. It is determined as: min(os_specific_limit-3,
sessions init.ora parameter)
The 3 are reserved for listening port, incoming connection, and tracefile.
EXAMPLE
-------
If your operating system supports 64 connections per process, but you
set sessions
to 30 in the init.ora file, then v$mts will show 30. If you raise the
sessions to
100, then v$mts will show 61. The maximum number of sessions depends
on both the
port and on the value of sessions as set in the init.ora file.
13. How to configure MTS?
Answer
------
1) Prior to implementing MTS, ensure that SQL*Net V2/Net 8 is
installed on both
the client and the server; confirm that the client can connect to the
server. Check
to see that the listener.ora file contains a profile for IPC. An
example of the listener.ora follows.
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL=IPC)
(KEY= <SID>) <- Name of SID
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = <Server>) <- Server name
(PORT = 1521)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
LOG_DIRECTORY_LISTENER = <Oracle_Home>/network/log> <- Vaild
directory
LOG_FILE_LISTENER = <listener> <- Valid
file name
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = <SID>) <- Name of SID
(ORACLE_HOME = <Oracle_Home>) <- Path to
Oracle_home
)
)
TRACE_LEVEL_LISTENER = OFF
2) The following lines should be in the init<sid>.ora file:
mts_dispatchers="<protocol>,<initial_number_of_dispatchers>"
mts_max_dispatchers=<maximum number of dispatchers>
mts_servers=<minimum_number_of_servers>
mts_service=<sid>
mts_listener_address="(ADDRESS=(PROTOCOL=<protocol>) (HOST=<host_name>)
(PORT=<port_number>))"
In the following example we are configuring MTS to support:
a) TCP/IP and IPC connections.
b) The listener will be listening on port 1521.
c) The database SID is V716.
d) The server is server1
e) The minimum number of dispatchers/servers is one (1).
f) The maximum number of dispatchers/servers is ten (10).
mts_dispatchers="tcp,1"
mts_dispatchers="ipc,1"
mts_max_dispatchers=10
mts_servers=1
mts_max_servers=10
mts_service=V716
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(host=server1)(port=1521))"
mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=V716))"
3) To start the system use the following procedure:
a) Start the listener first (lsnrctl start).
b) Start the database.
References
----------
<PRE:1005246.6>
14. How to turn off MTS without stopping the database?
Answer
------
As the dba, use the ALTER SYSTEM command to set the number of
dispatchers to zero. Below is an example for TCP dispatchers:
ALTER SYSTEM SET MTS_DISPATCHERS='TCP,0'
Note: When specifying the protocol, it must be in the same case as
used in the MTS_DISPATCHERS
command in the init<sid>.ora. For example, if you used TCP in the
init<sid>.ora then use TCP in the alter system command.
15. How to start more dispatchers without stopping the database?
Answer
------
As the dba, use the ALTER SYSTEM command to set a new value for the
number of dispatchers
to have running. Below is an example for TCP:
ALTER SYSTEM SET MTS_DISPATCHERS="TCP,6"
Note: When specifying the protocol, it must be in the same case as
used in the MTS_DISPATCHERS
command in the init<sid>.ora. For example, if you used TCP in the
init<sid>.ora then use TCP in the alter system command.
In this example, there will be a total of 6 dispatchers running, not
an additional 6.
The total number of dispatchers you start cannot be greater than the
value of MTS_MAX_DISPATCHERS
as defined in the init<sid>.ora.
.
--------------------------------------------------------------------------------
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved.
Legal Notices and Terms of Use.
--- Em [email protected], Marcus Vinicius Miguel Pedro
<[EMAIL PROTECTED]> escreveu
>
> Chiappa, vou levantar as informações e colocarei todas aqui, mas o
documento que você informou, não encontrei no Metalink...
>
> MetaLink V2 - Error Message
> Note entry 47684.1.cannot be displayed. Possible reasons
are:
> The article is not classified as publicly accessible
("non-public")
> The article's content is being updated and it is temporarily
unavailable but will be made available again soon.
> The article has been deleted from this serve r.
> The article number does not exist (it was referenced incorrectly).
> If you still have questions about why you couldn't access this
article, please use the Feedback button at the bottom of the left-hand
menu bar.
>
--------------------------------------------------------------------------------------------------------------------------
Atenção! As mensagens deste grupo são de acesso público e de inteira
responsabilidade de seus remetentes.
Acesse: http://www.mail-archive.com/[email protected]/
--------------------------------------------------------------------------------------------------------------------------__________________________________________________________________
Moderador e Fundador: Dorian Anderson Soutto [EMAIL PROTECTED]
__________________________________________________________________
Links do Yahoo! Grupos
<*> Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
<*> Para sair deste grupo, envie um e-mail para:
[EMAIL PROTECTED]
<*> O uso que você faz do Yahoo! Grupos está sujeito aos:
http://br.yahoo.com/info/utos.html
