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, [EMAIL PROTECTED] 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))(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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
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:[EMAIL PROTECTED]
>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, [EMAIL PROTECTED] 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
>&g
RE: ORA-4031 error help.
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, [EMAIL PROTECTED] 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))(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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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, cop
RE: ORA-4031 error help.
Make sure you are pinning your large and often executed packages,
triggers, procedures, etc in the shared pool (should be done at
startup). That will help eliminate fragmentation.
select 'execute dbms_shared_pool.keep('||chr(39)||
owner||'.'||name||chr(39)||','||chr(39)||
decode(type,'TRIGGER','R','SEQUENCE','Q','P')||chr(39)||');'||chr(10)
from v$db_object_cache
where type in ('PACKAGE','PROCEDURE','TRIGGER','FUNCTION')
and owner='SYS'
and kept='NO'
order by sharable_mem desc
/
This can be done for your application schema as well.
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2003 11:39 AM
To: Multiple recipients of list ORACLE-L
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.
Thanks
# 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
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: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Tim Fleury
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: ORA-4031 error help.
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.
[EMAIL PROTECTED]
http://www.ip-soft.net/
Phone: 888.IPSOFT8
Fax: 801.681.7664
-Original Message-
[EMAIL PROTECTED]
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, [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
Re: ORA-4031 error help.
The standard question would be "Have you made any recent changes to code,
patches, parameters, etc?"
But I saw cursor_space_for_time = true in your init.ora and you've increased
open_cursors from 500 to 750 few days ago, these can cause excessive memory
usage for example.
Also, you might want to take a level 2 heapdump when the error occurs using:
event = "4031 trace name heapdump forever, level 2"
and/or errorstack as well
event = "4031 trace name errorstack forever, level 4"
If the code itself is ok (bind vars etc), then I'd look at setting
cursor_space_for_time false, unless you want to add more memory to shared
pool again...
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, October 29, 2003 9:39 PM
> 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.
>
> Thanks
>
>
>
>
> # 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))(DISPATCHERS=
1)"
> MAX_DISPATCHERS = 3
> SHARED_SERVERS = 10
> MAX_SHARED_SERVERS = 50
>
>
> 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: <[EMAIL PROTECTED]
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Tanel Poder
INET: [EMAIL PROTECTED]
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 li
RE: ORA-4031 error help.
Do you have cursor_sharing set?
Long term : Have the developers use bind variables. Short term identify the sql doing the most damage by looking at sql being reparsed etc. Fix them to use bind variables if possible.
About alert.log some errors go to alert.log some errors do not.
Do you have to bounce the database? If not then you are lucky and you better take action. You can create a on startup trigger to pin packages and schedule a shutdown. Then monitor the shared pool. Initially the percent used will go up and then it will start going down as the fragmentation occurs. Fragmentation is the problem in most cases not the size of shared pool. So you can try the above before increasing.Daniel Harron <[EMAIL PROTECTED]> wrote:
How often does the error occur? How many sessions are connected whenthe error occurs? What is the status of the shared pool reserved? Ifyou 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=0Regards,-Daniel-- Daniel HarronDatabase ManagementIPsoft, Inc.[EMAIL PROTECTED]http://www.ip-soft.net/Phone: 888.IPSOFT8Fax: 801.681.7664-Original Message-[EMAIL PROTECTED]Sent: Wednesday, October 29, 2003 3:39 PMTo: Multiple recipients of list ORACLE-LFull error message is ORA-04031: unable to allocate 4032 bytes of shared memory ("largepool","unknown object","session heap","frame segment"))I am already monitoing both shared pool and large pool free memory ev!
ery30
minutes and there is no issue with that. As I mentioned below Oracleis not displaying any error message or trace file.-Original Message-Sent: Wednesday, October 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LWell, you neet to check the full error, because otherwise there's no wayto tell if you are running low on shared or large pool.The view that shows space usage in both places in v$sgastat. I suggestyou start looking there. Maybe your third-party application doesn't usebind variables and is bloating the shared pool. You could verify thisby observing that the sqlarea component of the shared pool is very largeas seen in v$sgastat. If this is the case then you might considertesting with cursor_sharing=force.You could also count different versions of similar SQL from theapplication by grouping sql_text in v$sqlarea by the first 30 charactersor so. This assumes your problem is sha!
red pool
sqlarea bloat. Youcould just be runnning out of space for MTS session heaps in the largepool. You have to look at v$sgastat first.--Jeremiah Wiltonhttp://www.speakeasy.net/~jwiltonOn Wed, 29 Oct 2003, [EMAIL PROTECTED] 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' waitevent 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>DISPATCHE
RE: ORA-4031 error help.
It may have to do with an object not being able to load into SGA because of
not enough contiguous memory in the SGA. It happens in MTS environments. Try
pinning your larger objects.
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2003 12:39 PM
To: Multiple recipients of list ORACLE-L
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.
Thanks
# 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))(DISPATCHERS=
1)"
MAX_DISPATCHERS = 3
SHARED_SERVERS = 10
MAX_SHARED_SERVERS = 50
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: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Michael Milligan
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORA
RE: ORA-4031 error help.
Avnish
Glad to see you are getting some excellent suggestions. We run Lawson
here. Lawson itself uses the database in a pretty simple manner, so that
rules out a lot of stuff, except if you've added customizations of your own.
Lawson uses bind variables, so that rules out some suggestions. I notice you
run MTS (we don't), so I would recommend you give careful attention to the
MTS-related suggestions.
Lawson has a user email list if you haven't found it. Email me directly if
you want to discuss any Lawson specifics.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2003 2: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, [EMAIL PROTECTED] 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))(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: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing lis
