Re: no mem, server hung

On 20 Jun 2002 11:54:19 -0700, rickraster_at_hotmail.com (RR) wrote:

>Hi Hemant, thanks for the reply.
>
>> "Hemant K Chitale" <hkchital_nospam_at_singnet_nospam.com.sg> wrote in message > news:<aesqau$50h$1_at_clematis.singnet.com.sg>...
>> Check to see if the SORT_AREA_SIZE
>> has been increased recently [either in the init.ora
>> or in a login.sql or application program with the
>> ALTER SESSION SET ..].
>
>Nope, the sort_area_size has not been modified at all for months. No
>applications are altering sessions either. I've included (most) of out
>init.ora file in case there is something in there that is glaringly
>obvious.
>
>Thanks again for the reply, and to anyone else who has any suggestions
>for me.
>
>#audit_trail = true
>bitmap_merge_area_size = 8388608 # 8M
>create_bitmap_area_size = 16777216 # 16M
>db_block_buffers = 4096
>db_block_checking = FALSE
>db_block_size = 8192
>db_file_direct_io_count = 64
>db_file_multiblock_read_count = 8
>db_files = 2048
>db_writer_processes = 4
>dbwr_io_slaves = 4
>distributed_transactions = 400
>instance_name = prod
>java_pool_size = 20971520 # 20M
>large_pool_size = 20971520 # 20M
>log_buffer = 10485760 # 10M
>log_checkpoint_interval = 10000
>log_checkpoint_timeout = 1800
>max_dump_file_size = 10240 # 5M
>max_enabled_roles = 30
>nls_date_format = "DD/MON/YYYY"
>nls_time_format = "HH24:MI:SS"
>nls_timestamp_format = "DD/MON/YYYY HH24:MI:SS"
>open_cursors = 300
># optimizer_index_caching = 10
># optimizer_index_cost_adj = 100
>optimizer_percent_parallel = 100
>parallel_adaptive_multi_user = false
>parallel_automatic_tuning = true
>parallel_broadcast_enabled = true
>parallel_max_servers = 4
>parallel_min_servers = 1
>parallel_threads_per_cpu = 4
>partition_view_enabled = true
>processes = 300
>query_rewrite_enabled = true
>query_rewrite_integrity = trusted
>shared_pool_reserved_size = 31457280 # 30 M
>shared_pool_size = 83886080 # 80 M
>sort_area_retained_size = 10485760 # 10 M
>sort_area_size = 31457280 # 30 M
>sort_multiblock_read_count = 32
>star_transformation_enabled = false
>timed_statistics = false
>log_archive_start = true
>log_archive_dest_1 = "location=/home/oracle/admin/prod/arch"
>log_archive_format = arch_%t_%s.arc
>global_names = false
>background_dump_dest = /home/oracle/admin/prod/bdump
>core_dump_dest = /home/oracle/admin/prod/cdump
>user_dump_dest = /home/oracle/admin/prod/udump
>remote_login_passwordfile = exclusive
>os_authent_prefix = ""
>job_queue_processes = 36
>job_queue_interval = 30
>open_links = 10
>session_max_open_files = 250
>distributed_transactions = 100
>compatible = 8.1.7
>
>
>
>;-) Rick

You are aware sort_area_size is a *per process* limit? Decrease that to something in the range of 256k - 1M. For individual programs an alter session should take care of a temporarily increase. You're wasting memory, and you don't have it, as is quite clear from your specs.
If you add up 32M buffer cache + 20M java + 80 M shared pool, that tells me you must be pagefaulting and swapping all day long. So I wouldn't be surprised at all the server is 'hung'

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jun 20 2002 - 15:31:38 CDT