RE: ORA-4031 error help.

Avnish,

4031 (as well as 0155 and 1652) are considered 'user' errors and will NOT be logged in the alert.log by default. You could add the following into your init.ora to capture them: (Make sure that you keep *all* event lines together, including previous ones in the init file, otherwise only the last set is considered):

event="1555 trace name errorstack level 3"
event="4031 trace name errorstack level 3"
event="1652 trace name processstate level 10"

I also see that you are at 9202 and I do know that there are *lots* of shared pool related errors below 9204. I would suggest an upgrade first...

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

God's word wrapped in great music - 24x7x365 at http://www.klove.com

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Jeremiah Wilton [mailto:jwilton_at_speakeasy.net]
>Sent: Wednesday, October 29, 2003 11:55 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: ORA-4031 error help.
>
>
>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_LAWS
>ON_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))
>(DISPATCHERS=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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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).