Open files on windows

Ralph wrote on the SUN VS INTEL thread.

"Hi all,

I think that NT and also W2k is probably not able to do the job if it is necessary to have
up to 400 shadow processes/threads. The reason is, that AFAIK, a process on Windows can
only have 65536 open Handles. If your database has 400 shadow threads (on Windows the
shadow processes are implemented as threads), your database should not have much more than
160 files. This statement is not valid if each shadow thread do not need to open all the files.
But I would not count on that."

So I thought I'd try a little test

SQL>
SQL> create tablespace daft_test1
  2 datafile 'c:\oracle\daft_test1\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test2
  2 datafile 'c:\oracle\daft_test2\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test3
  2 datafile 'c:\oracle\daft_test3\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test4
  2 datafile 'c:\oracle\daft_test4\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test5
  2 datafile 'c:\oracle\daft_test5\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test6
  2 datafile 'c:\oracle\daft_test6\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test7
  2 datafile 'c:\oracle\daft_test7\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test8
  2 datafile 'c:\oracle\daft_test8\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test9
  2 datafile 'c:\oracle\daft_test9\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> create tablespace daft_test10
  2 datafile 'c:\oracle\daft_test10\t1.dbf' size 128k   3 extent management local uniform size 16k;

Tablespace created.

SQL>
SQL> begin
  2 for j in 1..10 loop
  3 for i in 1..1000 loop
  4 execute immediate 'alter tablespace daft_test'||to_char(j)||' add datafile ''c:\oracle\daft_test'||to_char(j)||'\'||to_char(i)||'.dbf'' size 64k';
  5 end loop;
  6 end loop;
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL> spool off

so now we have ten tablespaces with 1001 datafiles in each. we have 12 processes active (from v$session) and all appears well.

Then I issue 'ALTER SYSTEM CHECKPOINT;'

• 2002-11-27 12:12:20.000
• SESSION ID:(4.1) 2002-11-27 12:12:19.000 error 4030 detected in background process ORA-04030: out of process memory when trying to allocate 16908 bytes (pga heap,Get kfha asynch mem)

and the instance terminates. Now is this a demonstration of Ralph's point or am I in fact really running out of RAM (this is a 320mb ram machine sga of 110mb), before I hit any open file handle problem. Suggestions of a better test of the thesis would be welcomed.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************