Re: max number of handles 9.2, win32, partitioning

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Thu, 29 Jul 2004 19:26:48 -0700 (PDT)
Message-ID: <20040730022648.83940.qmail@web20426.mail.yahoo.com>

Tanel_Põder <tanel.poder.003_at_mail.ee> wrote:

> Hi!

> > What is the largest number of user sessions *
> > datafiles = filesystem handles that you've
> > encountered in win32 land?

> Hm, strange, I always thought that due the
> single-process architecture in
> Win32, Oracle shares the file handles, but now when
> I started thinking a bit
> about OS IO architecture and checked with process
> explorer,

sysinternals.com rocks.
one virus software vendor now considers psexec and psshutdown to be malware.
Funny that they don't care about pskill. No one is taking away pskill from me. >:| I wonder what they think of the official MS Resource Kit tools that do the same thing? Ever have a server console become unresponsive under load? Guess what still works? psexec.
I haven't used it for years though, as the Cywgin sshd works much better.

> I see that yes,
> there are separate handles for some objects (btw,
> handles in Windows may
> mean file handles, registry handles and a great deal
> of other types of
> handles which I don't know..)

including network connections. think "everything is a file" in *nix. "\Device\tcp"

> > I had the bright idea of partitioning several
> large
> > tables at a client site that are frequently hit,
> that
> > typically supports 250 simultaneous sessions.

> That's dedicated server config?

yes. only using shared servers where its required for vpn setups on known fixed ports. /3GB +
pga_aggregate_target gets us by at around 2.7 GB max memory usage.

I might be more inclined to use shared server connections in 10g, as Mladen pointed out in comp.databases.oracle.server today, that there is a trace utility that will consolidate trace file data for a session across shared server trace files.

> > I'm currently seeing around 6000 handles for 225
> > threads in a non-partitioned setup for a single
> > oracle.exe process.

> I never checked the handle count, but the largest
> handle count could have
> been on a 8.0 database with 600-700 datafiles and
> about 800 concurrent users
> with dedicated sessions on WinNT 4.0 SP5. This was
> about 4 years ago, so I
> guess Oracle9.2 on W2k should cope with more
> novadays..

right.
currently out of 1270 handles, 777 are for datafiles, 51 for controlfiles, 9 semaphores, 35 for .msb.

> > To sum it up, If I partition 42 tables into 42
> > partitions (months) that each have 42 indexes and
> 42
> > users simultaneously access the application, will
> it
> > throw an ora-00942 error? :)

> The handle usage isn't dependant on structure inside
> the Oracle datafiles,
> it depends on how many datafiles you have and how
> many processes (!) you
> have accessing those datafiles (as you asked in your
> first question).

sorry that I didn't be more clear in that attempt at humor that smells of sucking up to Pete and Mogens. only using 1 tablespace each per partitioning key shared for all partitioned tables and indexes. It won't be 42 tables and they don't quite have 42 indexes each.
one did have 21, but I was able to reduce that somewhat during the re-org. skip scan, AND EQUAL are nice features.

> > thanks in advance.
> my 2 cents :)

> Tanel.

Paul

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jul 29 2004 - 21:23:26 CDT