FAQ
How often does the error occur? How many sessions are connected when
the error occurs? What is the status of the shared pool reserved? If
you flush the SGA does the error clear for a period?

Also, in 8i there used to be a bug that required setting
_db_handles_cached=0

Regards,

-Daniel

--
Daniel Harron
Database Management
IPsoft, Inc.
daniel.harron_at_ip-soft.net
http://www.ip-soft.net/
Phone: 888.IPSOFT8
Fax: 801.681.7664

-----Original Message-----
Avnish.Rastogi_at_providence.org
Sent: Wednesday, October 29, 2003 3:39 PM
To: Multiple recipients of list ORACLE-L

Full error message is ....

ORA-04031: unable to allocate 4032 bytes of shared memory ("large
pool","unknown object","session heap","frame segment"))

I am already monitoing both shared pool and large pool free memory every
30 minutes and there is no issue with that. As I mentioned below Oracle
is not displaying any error message or trace file.

-----Original Message-----
Sent: Wednesday, October 29, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L

Well, you neet to check the full error, because otherwise there's no way
to tell if you are running low on shared or large pool.

The view that shows space usage in both places in v$sgastat. I suggest
you start looking there. Maybe your third-party application doesn't use
bind variables and is bloating the shared pool. You could verify this
by observing that the sqlarea component of the shared pool is very large
as seen in v$sgastat. If this is the case then you might consider
testing with cursor_sharing=force.

You could also count different versions of similar SQL from the
application by grouping sql_text in v$sqlarea by the first 30 characters
or so. This assumes your problem is shared pool sqlarea bloat. You
could just be runnning out of space for MTS session heaps in the large
pool. You have to look at v$sgastat first.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Wed, 29 Oct 2003, Avnish.Rastogi_at_providence.org wrote:

Hello List, Need some help in resolving ORA-4031 error message. We are
using Lawson and for last few days users are getting ORA-4031 error
2-3 times a day in LAWSON log files but there is no error message in
alert log file or any trace file. Both shared pool and large pool is
set to 1GB. Below is the current init.ora file. We are on Oracle 9202
and AIX 5.1, using MTS.

# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=LAWSON
DB_FILES=1500
GLOBAL_NAMES=TRUE
DB_BLOCK_SIZE=8192
DB_CACHE_SIZE=1792M
DB_KEEP_CACHE_SIZE=16M
LARGE_POOL_SIZE=1024M
SHARED_POOL_SIZE=1024M
SGA_MAX_SIZE = 5G
DB_FILE_MULTIBLOCK_READ_COUNT=8 CONTROL_FILE_RECORD_KEEP_TIME=45
CURSOR_SHARING=SIMILAR
OPEN_CURSORS=750 # From Lawson--Raised from 500 to 750 10/24/03
BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump
CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump
USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump
TIMED_STATISTICS=TRUE
CONTROL_FILES=("/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWSON_01.ctl
",
>
"/appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl",
>
"/appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl",
>
"/appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl",
>
"/appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl")
# Archive LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/
LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/
LOG_ARCHIVE_FORMAT="ARC_LAWSON_%S.%T"
LOG_ARCHIVE_START=TRUE
# LOG_ARCHIVE_TRACE = 1

# Distributed, Replication and Snapshot
DB_DOMAIN=PHSOR.ORG

# Pools
JAVA_POOL_SIZE=0

# Processes and Sessions
# PROCESSES=800 Increased value per vendor JMK 6/09/03 PROCESSES=1000
SESSIONS=1140
ENQUEUE_RESOURCES=8000
TRANSACTION_AUDITING=FALSE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAST_START_MTTR_TARGET=1200
SORT_AREA_SIZE=0
HASH_AREA_SIZE=0
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undo
UNDO_RETENTION = 10800
PGA_AGGREGATE_TARGET=1G
WORKAREA_SIZE_POLICY = AUTO
JOB_QUEUE_PROCESSES = 10
LOG_BUFFER = 8192000 # To reduce 'log file parallel write' wait
event in v$system_event
CURSOR_SPACE_FOR_TIME = TRUE
SERVICE_NAMES=lawson_ax3202a
LOCAL_LISTENER=lawson_ax3202a
# Network Registration
INSTANCE_NAME=LAWSON
DISK_ASYNCH_IO = FALSE
BACKUP_TAPE_IO_SLAVES=TRUE
PARALLEL_THREADS_PER_CPU = 6
PARALLEL_MAX_SERVERS = 6
PARALLEL_MIN_SERVERS = 1
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000))(DISPATCH
ERS=1)"
MAX_DISPATCHERS = 3
SHARED_SERVERS = 10
MAX_SHARED_SERVERS = 50
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jeremiah Wilton
INET: jwilton_at_speakeasy.net

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).

DISCLAIMER:
This message is intended for the sole use of the individual to whom it
is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are
not the addressee you are hereby notified that you may not use, copy,
disclose, or distribute to anyone the message or any information
contained in the message. If you have received this message in error,
please immediately advise the sender by reply email and delete this
message.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daniel Harron
INET: daniel_at_ip-soft.net

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 6 of 10 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 29, '03 at 7:39p
activeOct 30, '03 at 3:49p
posts10
users9
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase