| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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 <daniel_at_ip-soft.net> wrote: 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:Received on Wed Oct 29 2003 - 15:24:25 CST
> 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: INET: Avnish.Rastogi_at_providence.org 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: 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). --------------------------------- Do you Yahoo!? Exclusive Video Premiere - Britney Spears -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: raju pa INET: raju_pa_24_at_yahoo.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).
 |
 |