DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Urgent! ORA-04030 error on production.

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    43

    Exclamation

    Database version - 8.1.6.3.4. Platforms - NT, win2000.
    Problem:
    firing a typical select with hint like /*+ parallel(u,4) */.
    getting a:

    The following error has occurred:

    ORA-12801: error signaled in parallel query server P004
    ORA-04030: out of process memory when trying to allocate 1049100 bytes (bind var heap,kllcqas:kllsltba)

    star_tranformation_enabled = false, sort_area_size = 5214400, sort_area_retained_size = 5214400.

    WHAT ELSE TO DO? I checked all metalink, found nothing good... That error happens unpredictably, sometimes it happens during index creation, sometimes during select,
    sometime it complains about PGA - so, the problem is about PGA memory allocation, I suppose. It happens on several instances, 8.0.5 and 8.1.6.3.4 instances, different clients - seems like it`s something deep in kernel. How to avoid it?
    I cannot change select - it`s hardcoded in application.
    i also want to continue use PQ - it`s a huge data warehouse...

    The current box: 7gig 4cpu dell, win2000, oracle has 4G in db-buffers and 500M in SGA. no stored procedures in memory, nothing - i shutdown it, startup, then fire a select - get this message.

    Any ideas are welcome.

    this is a init.ora:
    db_files = 32
    db_writer_processes=2
    control_files = ("C:\Oracle\oradata\seth\control01.ctl", "C:\Oracle\oradata\seth\control02.ctl", "C:\Oracle\oradata\seth\control03.ctl")
    open_cursors = 127
    optimizer_mode = all_rows
    parallel_automatic_tuning=true
    parallel_broadcast_enabled=true
    partition_view_enabled=true
    star_transformation_enabled = false
    query_rewrite_enabled = true
    resource_limit=true
    resource_manager_plan='SYSTEM_PLAN'
    cursor_space_for_time = true
    session_cached_cursors = 64
    always_anti_join=hash
    always_semi_join=hash
    hash_area_size = 26428800

    max_enabled_roles = 30
    db_file_multiblock_read_count = 32
    USE_INDIRECT_DATA_BUFFERS=TRUE


    db_block_buffers = 253727

    shared_pool_size = 529006591

    large_pool_size = 5614400
    java_pool_size = 16384

    log_checkpoint_interval = 1000000
    log_checkpoint_timeout = 0

    processes = 127

    parallel_max_servers = 64

    log_buffer = 1048576
    max_dump_file_size = 10240 # limit trace file size to 5M each
    rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6, RBS7, RBS8, RBS9, RBS10, RBS11, RBS12, RBS13, RBS14, RBS15, RBS16, RBS17, RBS18, RBS19 )
    global_names = true
    oracle_trace_collection_name = ""
    background_dump_dest = C:\Oracle\admin\seth\bdump
    user_dump_dest = C:\Oracle\admin\seth\udump
    db_block_size = 16384
    job_queue_processes = 4
    job_queue_interval = 60
    open_links = 4
    distributed_transactions = 10
    compatible = 8.1.6
    sort_area_size = 5214400
    sort_area_retained_size = 5214400



    [Edited by utkinpol on 05-25-2001 at 01:54 PM]

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    This looks like the system is running out of memory. One way is add some good amout of virtual memory or some physical memory to the system. That would mostly fix the problem. Some of the other parameters you might want to consider are changing the optimizer mode from all_rows to choose, and analyze all your tables and indices for efficient query selection.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    You might also try reducing the parallelism of the query. It looks like it is the 4th parallel read process that is failing.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  4. #4
    Join Date
    Dec 2000
    Posts
    43

    reply to sambavan

    Well, the current info from task manager
    is: 8G memory total, 3.6G available, 1G in cashe.
    total size of cash - 2x4G.
    4G in db_buffers, 500M SGA. 8G total.
    no, i don`t think that i`m running out of memory, i have more than enough free memory in this system all the time,

    moreover i had the same story on 4G box where only 1.5G were allocated for Oracle.
    I can tell that is going on - sessions performs table scan, then perform sort operation well, then produces this message.
    it works that way on huge result sets only.
    i cannot reduce the level of parallelizm, moreover it doesnt change anything - it fails anyway, even without that /*+ parallel */ hint. to be honest, i must say - sometime it fails, sometime not.
    i cannot catch the cause, this is the worse part of this.
    i just hoped that may be somebody had this situation before.


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Try increasing your large pool. During parallel executions oracle uses this part of DGA extensively for inter-process comunication.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You seems to have put 48M of sort size and sort retain size. There could be the problem over here too if your full table scan is to be performed with the sort exceeding the 48M then they would get transfered to TEMP tablespace and sort would be don. Once the session is done with its sort operation it would hold 48M of that space without releasing back, because your sort_retain_size instructs it to do so. Now immagine that you have 22 users connecting to the system, then you would be using out of 1GB memory, and would be holding them even though those sessions are not going to do any more sort operations. As a result you are exhausting the memory. So what is the best option, have a small sort_retain_size to shrink the sort area and release the unwanted memory back to the OS.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Sam's remarks are correct, but sort memory usage could be connected with this ORA-4030 only if the database runs in MTS configuration. If the paralel query is run through dedicated server process then sorting is done entirely outside shared pool.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2000
    Posts
    43
    [QUOTE]Originally posted by sambavan
    [B]You seems to have put 48M of sort size and sort retain size. There could be the problem over here too if your full table scan is to be performed with the sort exceeding the 48M

    which 48M are you speaking about? i posted my init.ora -
    i have `em 5M each.
    well, i gonna reduce `em down to 1M and hash_area_size to 2M, let`s see what will happen...
    actually, i already tried 5m and 10M combination - didn`t help.
    it runs slightly longer, then dies anyway.
    shhooot! :(

  9. #9
    Join Date
    Dec 2000
    Posts
    43
    also got more nice messages:
    Completed: alter database open
    Fri May 25 16:40:30 2001
    skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
    skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
    Fri May 25 16:40:56 2001
    skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
    Fri May 25 16:42:23 2001
    skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn

    goddamn windows!!! never, never use it for oracle or you`ll lost your mind completely... :((((

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width