FreeLists / oracle-l / RE: sys.sys_temp* tables caveats (9.2.0.6)

[cosmin ioan <cosmini@xxxxxxxxxxxxxxx>]

hello gentlemen,
  I've run across many instances where, whether I'm doing a dbms_stats or just 
convoluted queries using inline views (pretty awesome time savings! there)   
the engine simply creates sys.sys_temp* tables.
   
  Of course, it would create a tremendous number of them, based on the 
operations, the number of users, etc....   I think this is bound to be 
problematic at times, not to mention slow, for inserting to disk.
   
  I was wondering if there are additional pitfalls (or ora error messages) that 
one should be aware when seeing such temp tables creation;  
   
  I'm suspecting that probably, the easiest way to avoid this would be to 
increase some memory areas  ... hash or sort area sizes, if I'm not mistaken.  
??
   
  any recommendations/thoughts for this issue, within an OLTP environment?
   
  thx much,
  Cosmin