Oracle Shared Server (Erstwhile Multi-Threaded Server) and ORA-4031 Error on Large Pool

Environemnt: Oracle 9.2, Windows 2000 Server.
Basic MTS Configuration:- Include the following 2 parameters in the init.ora:-
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=db_server_ip_address))(DISPATCHERS=2)"
SHARED_SERVERS = 20

Note:
DISPATCHERS (Required to set): Oracle recommends a ratio of 1 DISPATCHER for every 1000 connections,
here I have allocated 2 DISPATCHERS to be on the safer side and it can handle up to 2000 connections.
SHARED_SERVERS (Optional): Database server uses 1 shared server for every 20 connections, so 20 will support 200 sessions, which is fair enough for this database requirement.

---

Troubleshooting: ORA-4031 Errors

Received the below ORA-4031 Error at the client side:-

ORA-4031: unable to allocate %s bytes of
shared memory ("%s","%s","%s","%s")

Noticed following error in the Alert.log of a dedicated server (non
multi-threaded server), where a job runs and used a database link, which is
connect to a multi-threaded server:-

ORA-04031: unable to allocate 64 bytes of
shared memory ("large pool","unknown object","session heap","trigger condition
node")

No errors noted in the Alert.log of MTS server, database functioning
normal, accepting user connections, but following were noted in the trace files
(SID_s001_2076.trc) of user_dump_dest:-

Dump file f:\oracle\admin\<sid>\udump\<sid>_s000_2056.trc
Thu Nov 09 19:35:43 2006
ORACLE V9.2.0.6.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: <sid>

Redo thread mounted by this instance: 1

Oracle process number: 10

Windows thread id: 2056, image: ORACLE.EXE

*** 2006-11-09 19:35:43.048
*** SESSION ID:(16.4) 2006-11-09 19:35:43.001
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora parameter _4031_dump_bitvec = 0
======================================
Allocation Request Summary Information
======================================
Current information setting: 00654fff
Dump Interval=300 seconds SGA Heap Dump Interval=3600 seconds
Last Dump Time=11/09/2006 19:35:41
Allocation request for: sort key
Heap: 58B34B04, size: 8192
******************************************************
HEAP DUMP heap name="large pool" desc=02170098
extent sz=0xfc4 alt=100 het=32767 rec=9 flg=-126 opc=0
parent=00000000 owner=00000000 nex=00000000 xsz=0x0
******************************************************
HEAP DUMP heap name="sort subheap" desc=58B34B04
extent sz=0x2024 alt=32767 het=32767 rec=0 flg=2 opc=2
parent=55F9F3AC owner=00000000 nex=00000000 xsz=0x1
Subheap has 1871424 bytes of memory allocated
====================
Process State Object
====================
----------------------------------------
SO: 5A59A1A4, type: 2, owner: 00000000, flag: INIT/-/-/0x00
(process) Oracle pid=10, calls cur/top: 5A705014/5A705014, flag: (40) MS SERVER
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 139
last post received-location: kmcmbf: not KMCVCFTOS
last process to post me: 5a59f358 1 128
last post sent: 0 0 146
last post sent-location: kmcpdp
last process posted by me: 5a59f358 1 128
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 5A5F8F10
O/S info: user: SYSTEM, term: <server_host_name>, ospid: 2056
OSD pid info: Windows thread id: 2056, image: ORACLE.EXE
=========================
User Session State Object
=========================
----------------------------------------
SO: 5A6068B4, type: 4, owner: 5BAE7D58, flag: INIT/-/-/0x00
(session) trans: 00000000, creator: 5BAE7D58, flag: (1000e5) USR/- BSY/-/-/-/-/-
DID: 0001-000B-00000008, short-term DID: 0000-0000-00000000
txn branch: 00000000
oct: 3, prv: 0, sql: 67908120, psql: 67908120, user: 50/RTT
O/S info: user: <OS User>, term: <Client_Hostname>, ospid: 2892:2560, machine:
<domain>\<hostname>
program: <program_name>
application name: <app name>, hash value=0
last wait for 'SQL*Net message to client' blocking sess=0x0 seq=24275 wait_time=6
driver id=4d545300, #bytes=1, =0
temporary object counter: 0
...No current library cache object being loaded
===========================
Current Instatiation Object
===========================
-------------------------------------
INSTANTIATION OBJECT: object=576F3410
type="cursor"[2] lock=659E4EF0 handle=67908120 body=00000000 level=0
flags=FST[60] executions=0
cursor name:
SQL Query...............
child pin: 5bd992b0, child lock: 5bdd363c, parent lock: 659e4ef0
xscflg: 80110476, parent handle: 67908120, xscfl2: 7a00009
Dumping Literal Information
Bind Pos: 8, Bind Len: 21, Bind Val:
nxt: 4.0x00000660 nxt: 3.0x00000470 nxt: 2.0x000007d0 nxt: 1.0x00000304
Cursor frame allocation dump:
frm: -------- Comment -------- Size Seg Off
bhp size: 172/560
whp size: 1887880/1895664
Dump of CURRENT WORK HEAP:

---

Since database is functioning normal and accepting user connections, its not
so critical to find an immediate solution, but the above heap in the trace file
causes some concern. Based on the Oracle's METALINK document

ORA-4031 from LARGE POOL when running with SHARED SERVER Configuration in 9i,
(ID: 223153.1), increased LARGE_POOL_SIZE from 70M to 300M, decreased
SHARED_POOL_SIZE & SHARED_POOL_RESERVED_SIZE from 200M & 20M to 150M & 15M
respectively. After the changes, init.ora will look like below:-

SGA_MAX_SIZE=1400M
PGA_AGGREGATE_TARGET=600M
DB_16K_CACHE_SIZE=400M
DB_CACHE_SIZE=400M
LARGE_POOL_SIZE=300M
SHARED_POOL_SIZE=150M
SHARED_POOL_RESERVED_SIZE=15M
JAVA_POOL_SIZE=40M
HASH_AREA_SIZE=4M
SORT_AREA_RETAINED_SIZE=2M
SORT_AREA_SIZE=2M
DB_BLOCK_SIZE=8192
DB_FILE_MULTIBLOCK_READ_COUNT=16
DISPATCHERS='(ADDRESS=(PROTOCOL=TCP)(HOST=db_server_ip_address))(DISPATCHERS=2)'
LOG_BUFFER=1M
OPEN_CURSORS=300
QUERY_REWRITE_ENABLED='FALSE'
SESSION_CACHED_CURSORS=50
SESSIONS=340
PROCESSES=400
SHARED_SERVERS=20

About LARGE_POOL: It is a part of SGA, doesn't use LRU list, not a part of SHARED_POOL. Used by
Oracle Shared Server (MTS) for UGA and RMAN if used. If LARGE_POOL is not set, then MTS will use memory from SHARED_POOL.

Use V$SGASTAT, V$SGA_DYNAMIC_COMPONENTS and V$SGA to find info about SGA and run the below query to check the summary of SGA:-

SELECT * FROM v$sgastat
WHERE pool ='large pool';
SELECT NVL(POOL,NAME) POOL_NAME,
ROUND(SUM(BYTES)/1024/1024,2) MBYTES
FROM V$SGASTAT GROUP BY NVL(POOL,NAME) ORDER BY 2 DESC;

COLUMN component FORMAT A15
SELECT component,
current_size/1024/1024 CURRENT_SIZE_MB,
granule_size/1024/1024 GRANULE_SIZE_MB
FROM v$sga_dynamic_components;

SELECT name,
ROUND((value)/1024/1024,2) MBYTES
FROM v$sga ORDER BY 2 DESC;

---

Note: Requires logon to access Oracle Metalink documents.Reference: Configuration
1)
Oracle9i Database Administrator's Guide - Configuring Oracle for the Shared Server - Chapter 5

2)
Net Services Administrator's Guide - Chapter 14 - Configuring Shared Server

3) Oracle Metalink Subject:
Multi-Threaded Server (MTS) Diagnostics, Doc ID: 1005259.6

Reference: Troubleshooting and Tuning

4) Oracle9i Database Performance Tuning Guide -
Chapter 14 - Memory Configuration and Use - Tuning the Large Pool and Shared
Pool for the Shared Server Architecture

5) Oracle9i Database Performance Tuning Guide -
Chapter 14 - Memory Configuration and Use -
Configuring and Using the Shared Pool and Large Pool

6) Oracle Metalink Subject:

ORA-4031 from LARGE POOL when running with SHARED SERVER Configuration in 9i,
Doc ID: 223153.1

7) Oracle Metalink Subject:
Diagnosing and Resolving Error ORA-04031, Doc ID:146599.1

8) Oracle Metalink Subject:
Fundamentals of the Large Pool, Doc ID:62140.1

9) Oracle Metalink Subject:
ORA-4031 AND ORA-600 [1113] USING MTS AND LARGE_POOL_SIZE, Doc ID:1058003.6