re: dumping records to disk instead of in-memory processing, how to avoid
- From: cosmin ioan <cosmini@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 6 Dec 2006 13:34:56 -0800 (PST)
hello all,
I'm doing some queries, or dbms_analyze jobs and frequently see data
automatically dumped to disk by Oracle, in temp tables:
INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */INTO
"SYS"."SYS_TEMP_0FD9D6660_22AF8354"
Typically, what memory structures would one need to bump up or hints to use
to eliminate this dumping to disk? -- here's my configuration of a 9.2.0.6
system:
bitmap_merge_area_size 1,048,576
create_bitmap_area_size 8,388,608
db_block_buffers 20,000
block_size 8,192
hash_area_size 131,072
object_cache_optimal_size 102,400
pga_aggregate_target 2.6 gb
sga_max_size 5.2 gb
shared_pool_reserved_size 300,000,000
shared_pool_size 3,003,121,664
sort_area_size 65,536
I have tried toying with the hash_area_size, sort_area_size and a few others,
but with no avail. [I know, from the above params, that this is a hardly
tuned system but that's another topic ;-) ]
Any thoughts on how I could prevent this dumping to disk by Oracle
(relatively speaking, as one cannot dump a 2gb job to disk, etc -- things of
that nature ...)
thx much,
Cosmin
Other related posts:
