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@xxxxxxxxxx]
Sent: Friday, May 28, 2004 5:18 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Duret, Kathy
Sent: Wednesday, May 26, 2004 2:53 PM
To: oracle-l@xxxxxxxxxxxxx
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:
 
1) Is there a way to tell an object is holding/leaking memory in the
database.  Or a way to monitor the shared_pool so I can be proactive if
fragmentation is getting out of hand.
 
2)  This Steve Adams query says to set kgl_bucket_count to 7  the hash table
from growing
-- dynamically, and thus prevents the performance problems
-- associated with hash table growth - 
How can I see if I have a problem with this hash table growth?
select
  least(8, ceil(log(2, ceil(count(*) / 509))))  "INDEX"
from
  sys.x_$kglob  o
where
  o.inst_id = userenv('Instance') and
  o.kglhdadr = o.kglhdpar
/
 
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                                4
instance_groups
event
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                  64
resource_manager_plan
hpux_sched_noage
lm_ress                                  6000
lm_locks                                 12000
active_instance_count
db_file_name_convert
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                25000
buffer_pool_keep
buffer_pool_recycle
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                          TRUE
thanks,
 
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.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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
-----------------------------------------------------------------


**********************************************************************
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: