re: dumping records to disk instead of in-memory processing, how to avoid

add'l stuff/insight:    

  WORKAREA_SIZE_POLICY=AUTO;       I'm mainly concerned about an example which is basically a query using an inline view.    

  The thing is, the query, with the one inline view queried **one time only**, does not generate the temp table, however, if that inline view is queried more than one time [it is technically a horizontal view with many summary columns and we convert it into a vertical recordset using UNIONs] then the temp table gets generated.    

  so, are there any hints that force inline view contents to not be dumped to disk?    

  thx much,
  Cosmin   

cosmin ioan <cosmini_at_bridge-tech.com> wrote:

    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

--
http://www.freelists.org/webpage/oracle-l