| 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: 4031 - errors
Kathy,
Ummmmmm, I think you missed my point. What I thought I said was we found the shared_pool_RESERVE wasn't getting used at all or hardly at all.
So we changed it from the default to 1M. shared_pool_reserved_size string 1024000 shared_pool_size string 514400000 I don't know how long your DB has been up but it looks like you had85 failures and you still had 25M you never used.
The problem is Only allocations larger than SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool's free lists.
Your query below says you had 25M free for something really big to stick in the SharedPoolReserve but 4132 was smaller then the SHARED_POOL_RESERVED_POOL_MIN_ALLOC so you got a 4031 when you had 25M free.
I didn't say anything about changing the SHARED_POOL size. It looks like you found the problem with .net. What I'm suggesting is you have 25M you might want to put to betteruse somewhere else.
Larry
>From Tom Kyte:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:773961292
7578
With 8.0, the large pool came along -- this obviated the need for the
"reserved"
portion of the shared pool. We moved those big allocations from the shared
pool
into the large pool. So, the reserved is not needed.
For the same reason, pinning is pretty much "not needed" either. It makes
some
people feel better but in 99.999% of the cases (eg: system is running
normally,
you use bind variables) pinning is pretty much a waste of programming effort
on
your part as well.
I would suggest not using either really. Ignore their existence.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Duret, Kathy
Sent: Friday, May 28, 2004 6:42 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: 4031 - errors
Looks like the problem is to a web service that runs .net stuff... They killed this and we got all our Solaris and Oracle memory back and everything went back to normal.
kathy
-----Original Message-----
From: Duret, Kathy [mailto:kduret_at_starkinvestments.com]
Sent: Friday, May 28, 2004 6:22 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: 4031 - errors
In fact we had problems today again..... we are going to bounce tonight.
SQL> 1 select
2 free_space 3 ,avg_free_size 4 ,used_space 5 ,avg_used_size 6 ,request_failures 7 ,last_failure_size 8 ,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24 MI SS') DATE_AN_TIME 9 from 10* v$shared_pool_reserved
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES
---------- ------------- ---------- ------------- ----------------LAST_FAILURE_SIZE DATE_AN_TIME
----------------- --------------------
25000000 25000000 0 0 85
4132 28-MAY-2004 18 03 17
we have the default have a shared pool of 500000000 so are you saying to decrease it to less than the default?
Also this has been this way for years. In fact I just increased shared_pool from 300000000 to 500000000
I just don't know. I did see leaks from our update against our db link and hs link.
I am trying to see if our .net server
-----Original Message-----
From: Wolfson Larry - lwolfs [mailto:lawrence.wolfson_at_acxiom.com]
Sent: Friday, May 28, 2004 5:18 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: 4031 - errors
Kathy, hi how are you?
Got in here a couple days late but we had a sever problem with 4031s and after looking on metalink I used this script to see how busy the shared_pool_reserve was.
SPOOL $DBS/Sharedpoolreserved.$ORACLE_SID
COL name FOR A05 COL REQUEST_FAILURES FOR 999,999 HEA "REQUEST|FAILURES"COL LAST_FAILURE_SIZE FOR 999,999,999 HEA "LAST |FAILURE |SIZE " select name
,free_space
,avg_free_size
,used_space
,avg_used_size
,request_failures
,last_failure_size
,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24 MI SS') DATE_AN_TIME
from db
,v$shared_pool_reserved
;
SPOOL OFF
name is just the our INTERNAL DB name like CHICago, JACKsonville,
DALLas etc. to tell one PROD from another.
We had a lot of free space and minimized the SPR to give more memory to SP. We didn't have any more problems
Larry
Information written to trace file.
17:14:22 SQL> SHOW PARAMETERS shar
NAME TYPE VALUE ------------------------------------ ------- ------------------------------ cursor_sharing string EXACT hi_shared_memory_address integer 0 shared_memory_address integer 0 shared_pool_reserved_size string 1024000 shared_pool_size string 514400000 PS What are these parms doing for you? _row_cache_instance_locks 1000 _row_cache_buffer_size 2000
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Duret, Kathy
Sent: Wednesday, May 26, 2004 2:53 PM
To: oracle-l_at_freelists.org
Subject: 4031 - errors
Had a strange problems on Friday afternoon. We are on 8.1.7.4 Solaris
Had a ton of Bam - ora_04031 errors.
Tried to flush the shared pool and it would release some space but a large chuck was not being released and this was strange since we have only acouple of objects pinned.
When I tried to pin an object that was failing right after I would flush the shared_pool I couldn't get space.
Nothing new was put in that week as far as I know.
Put in a tar with Oracle and they suggested to bounce the database - which I did later, increased the shared_pool and session_cached_cursors We have pinned some more objects that are being loaded alot.
I looked at the sql area and nothing looked horrible, all the same normal stuff. In fact there was really no load on the database or machine, no swapping or paging.
It was very strange. It looked like "something" had memory and wasn't releasing it.
Oracle Supportless was of no help.... except to say bounce the database and told me to set a parameter that isn't available until 9i.
I tried running quite a few of Steve Adams scripts but really didn't enlighten me as to what was causing the problem.
I have since set the event in the init.ora for 4031. I know we don't use enough bind variable since the "java" structure with Jbosse doesn't work with bind variable ( this is what I am told anyway)
the cursor_spaced_for_time is set to true which I believe was done for the
Jbosse app we are running since they pool connections and can't/dont use
bind variables. I am wondering
if this should be set to false.
My questions are:
Any suggestions, welcome.
init.ora - I took out the nls and archive,etc info
_spin_count 5000 processes 700 sessions 775 timed_statistics TRUE timed_os_statistics 0 resource_limit FALSE license_max_sessions 0 license_sessions_warning 0 cpu_count 4instance_groups
shared_pool_size 500000000 shared_pool_reserved_size 25000000 large_pool_size 614400 java_pool_size 20971520 java_soft_sessionspace_limit 0 java_max_sessionspace_size 0 pre_page_sga FALSE shared_memory_address 0 hi_shared_memory_address 0 use_indirect_data_buffers FALSE lock_sga FALSE lock_name_space enqueue_resources 1000 disk_asynch_io TRUE tape_asynch_io TRUE dbwr_io_slaves 0 backup_tape_io_slaves FALSE ops_interconnects db_file_direct_io_count 64resource_manager_plan
lm_ress 6000 lm_locks 12000active_instance_count
log_file_name_convert db_block_buffers 25000 db_block_checksum FALSE db_block_size 8192 db_block_lru_latches 8 db_writer_processes 2 db_block_max_dirty_target 25000buffer_pool_keep
max_commit_propagation_delay 700 compatible 8.1.7 log_archive_trace 0 log_buffer 2097152 log_checkpoint_interval 999999999 log_checkpoint_timeout 900 db_files 200 db_file_multiblock_read_count 8 read_only_open_delayed FALSE parallel_server FALSE parallel_server_instances 1 gc_releasable_locks 0 gc_rollback_locks 0-1024=32!8REACH gc_files_to_locks gc_defer_time 10 thread 0 fast_start_io_target 25000 log_checkpoints_to_alert FALSE recovery_parallelism 0 control_file_record_keep_time 7 dml_locks 3408 row_locking always serializable FALSE replication_dependency_tracking TRUE instance_number 0 transactions 852 transactions_per_rollback_segment 5 max_rollback_segments 170 transaction_auditing FALSE fast_start_parallel_rollback LOW db_block_checking FALSE _row_cache_instance_locks 1000 _row_cache_buffer_size 2000 _row_cache_cursors 100 os_roles FALSE rdbms_server_dn max_enabled_roles 30 remote_os_authent FALSE remote_os_roles FALSE O7_DICTIONARY_ACCESSIBILITY TRUE remote_login_passwordfile EXCLUSIVE dblink_encrypt_login FALSE license_max_users 0 db_domain global_names FALSE distributed_transactions 213 commit_point_strength 1 mts_dispatchers mts_servers 0 mts_max_servers 20 mts_max_dispatchers 5 mts_sessions 0 mts_circuits 0 mts_listener_address mts_multiple_listeners FALSE open_links 100 open_links_per_instance 4 optimizer_features_enable 8.1.7 fixed_date audit_trail DB sort_area_size 2097152 sort_area_retained_size 524288 sort_multiblock_read_count 8 open_cursors 1500 ifile sql_trace FALSE os_authent_prefix optimizer_mode CHOOSE sql92_security FALSE blank_trimming FALSE always_anti_join NESTED_LOOPS partition_view_enabled FALSE star_transformation_enabled FALSE parallel_broadcast_enabled FALSE parallel_adaptive_multi_user FALSE parallel_threads_per_cpu 2 parallel_automatic_tuning FALSE always_semi_join STANDARD optimizer_max_permutations 80000 optimizer_index_cost_adj 100 optimizer_index_caching 0 query_rewrite_enabled FALSE query_rewrite_integrity enforced sql_version NATIVE serial_reuse DISABLE cursor_space_for_time TRUE session_cached_cursors 400 text_enable FALSE remote_dependencies_mode TIMESTAMP utl_file_dir plsql_v2_compatibility FALSE job_queue_processes 1 job_queue_interval 60 optimizer_percent_parallel 0 parallel_min_percent 0 create_bitmap_area_size 8388608 bitmap_merge_area_size 1048576 cursor_sharing EXACT parallel_min_servers 0 parallel_max_servers 5 parallel_instance_group parallel_execution_message_size 2148 hash_join_enabled TRUE hash_area_size 2097152 hash_multiblock_io_count 8 oracle_trace_enable FALSE oracle_trace_facility_path ?/otrace/admin/fdf oracle_trace_collection_path ?/otrace/admin/cdf oracle_trace_facility_name oracled oracle_trace_collection_name oracle_trace_collection_size 5242880 object_cache_optimal_size 1048576 object_cache_max_size_percent 10 session_max_open_files 10 aq_tm_processes 0 hs_autoregister TRUEthanks,
Kathy
This transmission contains information solely for intended recipient and may be privileged, confidential and/or otherwise protect from disclosure. If you are not the intended recipient, please contact the sender and delete all copies of this transmission. This message and/or the materials contained herein are not an offer to sell, or a solicitation of an offer to buy, any securities or other instruments. The information has been obtained or derived from sources believed by us to be reliable, but we do not represent that it is accurate or complete. Any opinions or estimates contained in this information constitute our judgment as of this date and are subject to change without notice. Any information you share with us will be used in the operation of our business, and we do not request and do not want any material, nonpublic information. Absent an express prior written agreement, we are not agreeing to treat any information confidentially and will use any and all information and reserve the right to publish or disclose any information you share with us.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- This transmission contains information solely for intended recipient and may be privileged, confidential and/or otherwise protect from disclosure. If you are not the intended recipient, please contact the sender and delete all copies of this transmission. This message and/or the materials contained herein are not an offer to sell, or a solicitation of an offer to buy, any securities or other instruments. The information has been obtained or derived from sources believed by us to be reliable, but we do not represent that it is accurate or complete. Any opinions or estimates contained in this information constitute our judgment as of this date and are subject to change without notice. Any information you share with us will be used in the operation of our business, and we do not request and do not want any material, nonpublic information. Absent an express prior written agreement, we are not agreeing to treat any information confidentially and will use any and all information and reserve the right to publish or disclose any information you share with us. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- This transmission contains information solely for intended recipient and may be privileged, confidential and/or otherwise protect from disclosure. If you are not the intended recipient, please contact the sender and delete all copies of this transmission. This message and/or the materials contained herein are not an offer to sell, or a solicitation of an offer to buy, any securities or other instruments. The information has been obtained or derived from sources believed by us to be reliable, but we do not represent that it is accurate or complete. Any opinions or estimates contained in this information constitute our judgment as of this date and are subject to change without notice. Any information you share with us will be used in the operation of our business, and we do not request and do not want any material, nonpublic information. Absent an express prior written agreement, we are not agreeing to treat any information confidentially and will use any and all information and reserve the right to publish or disclose any information you share with us. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri May 28 2004 - 20:18:23 CDT
 |
 |