-
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]
-
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
-
You might also try reducing the parallelism of the query. It looks like it is the 4th parallel read process that is failing.
-
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.
-
Try increasing your large pool. During parallel executions oracle uses this part of DGA extensively for inter-process comunication.
-
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
-
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.
-
[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! :(
-
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... :((((
