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

Caching Objects in Shared Pool

Introduction:
This document is about KEEPing frequently used database objects like PROCEDURES, PACKAGES, SEQUENCES, TRIGGERS and CURSORS in the SHARED_POOL of SGA. Caching/KEEPing frequently
used objects in SHARED POOL will help improve the database performance.

Tested Environment: Oracle 9.2, Windows 2000 Server.

Connect to the database as sys:-
SQL> connect sys/**** as sysdba

Step#1: Create a table sp_obj_tab to hold the objects that need to be cached:-

SQL> CREATE TABLE sys.sp_obj_tab (
      objownr VARCHAR2(64),
      objname VARCHAR2(1000),
      objtype CHAR(1),
      listdte DATE,
      keepdte DATE,
      remarks CHAR(30));

Step#2: Take a look at script shared_pool_mgmt.SQL and run it, which will create a package called shared_pool_mgmt:-

SQL> @shared_pool_mgmt.SQL

Step#3: Create the below triggers which runs during database startup and shutdown:-

SQL> CREATE OR REPLACE TRIGGER db_shutdown_sp_obj_list
      BEFORE SHUTDOWN ON DATABASE
      BEGIN
      sys.SHARED_POOL_MGMT.list_obj_table;
      END;
      /

SQL> CREATE OR REPLACE TRIGGER db_startup_sp_obj_keep
      AFTER STARTUP ON DATABASE
      BEGIN
      sys.SHARED_POOL_MGMT.keep_obj_startup;
      END;
      /

Step#4: Schedule a dbms job that runs at regular intervals, the below once runs at 5 pm every day:-

SQL> VARIABLE JOBNO NUMBER;

SQL> BEGIN
      DBMS_JOB.SUBMIT(JOB=>:JOBNO,
      WHAT=>'shared_pool_mgmt.keep_obj_periodic;',
      NEXT_DATE=>TRUNC(SYSDATE)+1+17/24,
      INTERVAL=>'TRUNC(SYSDATE)+1+17/24');
      COMMIT;
     END;
     /

SQL> PRINT JOBNO;

---

Below is the script, mentioned in Step#2, copy and paste it in a file called shared_pool_mgmt.SQL :-

CREATE OR REPLACE PACKAGE shared_pool_mgmt AS
---------------------------------------------------
---- Name: SHARED_POOL_MGMT
---- Schema: SYS
---- Created by: BabuMani
---- Created on: Dec-05-2006
-- Description: This package helps database
-- administrators to KEEP frequently used database
-- objects like PROCEDURES, PACKAGES, SEQUENCES,
-- TRIGGERS and CURSORS in the SHARED_POOL of SGA.
---------------------------------------------------
PROCEDURE list_obj_table;
-- PROCEDURE LIST_OBJ_TABLE: Identifies the top 10
-- objects based on various system resource
-- consuming factors and writes them in a table
-- called SP_OBJ_TAB. This table is populated
-- during database shutdown. This procedure is run
-- using database shutdown trigger (BEFORE
-- SHUTDOWN ON DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_startup;
-- PROCEDURE KEEP_OBJ_STARTUP: Reads the objects
-- listed in table SP_OBJ_TAB during database
-- startup and KEEP them (except CURSORS)in
-- SHARED_POOL. This procedure is run using
-- database startup trigger (AFTER STARTUP ON
-- DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_periodic;
-- PROCEDURE KEEP_OBJ_PERIODIC: It refreshes the
-- list of objects in table SP_OBJ_TAB and KEEP
-- them (including CURSORS) in SHARED_POOL.
-- Suggested to run this procedure at scheduled
-- intervals.
---------------------------------------------------
END shared_pool_mgmt;
/

CREATE OR REPLACE PACKAGE BODY shared_pool_mgmt AS

PROCEDURE list_obj_table IS
PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
  DELETE FROM sys.sp_obj_tab;
  COMMIT;
  INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
  SELECT DISTINCT owner,name,DECODE(type,'TRIGGER','R','P'),SYSDATE,'Listed to Keep in Shared Pool'
    FROM v$db_object_cache
   WHERE type IN ('PACKAGE','PROCEDURE','PACKAGE BODY','TRIGGER')
     AND loads >= 1 AND executions > 0
  UNION
  SELECT DISTINCT owner,name,'Q',SYSDATE,'Listed to Keep in Shared Pool'
    FROM v$db_object_cache
   WHERE type = 'SEQUENCE'
     AND loads >= 1 ;
  INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY executions DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY loads DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY buffer_gets DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY disk_reads DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY sharable_mem DESC)
         WHERE ROWNUM<=10;
  COMMIT;
 END list_obj_table;

 PROCEDURE keep_obj_startup IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  own VARCHAR2(64);
  nam VARCHAR2(1000);
  typ VARCHAR2(1);
  CURSOR cur1 IS
  SELECT objownr, objname, objtype FROM SYS.sp_obj_tab WHERE objtype<>'C';
 BEGIN
  OPEN cur1;
   LOOP
    FETCH cur1 into own, nam, typ;
    EXIT WHEN cur1%NOTFOUND;
    SYS.DBMS_SHARED_POOL.KEEP(''|| own || '.' || nam || '',''||typ||'');
    UPDATE sys.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
    COMMIT;
   END LOOP;
  CLOSE cur1;
 END keep_obj_startup;

 PROCEDURE keep_obj_periodic IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  own VARCHAR2(64);
  nam VARCHAR2(1000);
  typ VARCHAR2(1);
  CURSOR cur2 IS
  SELECT objownr, objname, objtype FROM SYS.sp_obj_tab;
 BEGIN
  shared_pool_mgmt.list_obj_table;
  OPEN cur2;
   LOOP
    FETCH cur2 INTO own, nam, typ;
    EXIT WHEN cur2%NOTFOUND;
    IF typ='C' THEN
     SYS.dbms_shared_pool.keep(''|| own ||'',''||typ||'');
    ELSE
     SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '',''||typ||'');
    END IF;
    UPDATE SYS.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
    COMMIT;
   END LOOP;
  CLOSE cur2;
 END keep_obj_periodic;

END shared_pool_mgmt;
/

---

Below are some Handy-Dandy queries:-

COLUMN pool FORMAT A25

SELECT pool||(' -- Total') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE pool IS NOT NULL GROUP BY pool
UNION
SELECT pool||(' --- Free') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE name='free memory' AND pool IS NOT NULL GROUP BY pool;

SELECT type,COUNT(*) COUNT FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;

SELECT DECODE(objtype,'R','Trigger','P','Package','C','Cursor','Q','Sequence',objtype) OBJTYPE, COUNT(*) COUNT FROM sp_obj_tab GROUP BY objtype ORDER BY 2 ;

COMPUTE SUM LABEL 'TOTAL ' OF COUNT ON REPORT
COMPUTE SUM LABEL 'TOTAL ' OF SHARABLE_MEM_K ON REPORT
BREAK ON REPORT

SELECT type,COUNT(*) COUNT, round(sum(SHARABLE_MEM)/1024) SHARABLE_MEM_K FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;

Library Cache Hit Ratio:

COMPUTE SUM LABEL 'TOTAL' OF PINHITS ON REPORT
COMPUTE SUM LABEL 'TOTAL' OF PINS ON REPORT
BREAK ON REPORT
  SELECT namespace
       , pins
       , pinhits
       , reloads
       , invalidations
    FROM v$librarycache
ORDER BY namespace;

CLEAR COMPUTE
CLEAR BREAK

  SELECT sum(pinhits) pinhits,
         sum(pins) pins,
         round((sum(pinhits)/sum(pins))*100,2) LIBRARY_CACHE_HIT_RATIO
    FROM v$librarycache
ORDER BY namespace;

Dictionary Cache (Row Cache) Hit Ratio:

column parameter format a25
column RATIO format 999.9
column UPDATES format 999,999,999

  SELECT parameter
       , SUM(gets) gets
       , SUM(getmisses) getmisses
       , 100*SUM(gets - getmisses) / SUM(gets) RATIO
       , SUM(modifications) UPDATES
    FROM v$rowcache
   WHERE gets > 0
GROUP BY parameter;

SELECT ROUND((SUM(gets - getmisses - fixed)) / SUM(gets)*100,2) "ROW CACHE" FROM v$rowcache;

---

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