Re: 10,000+ Simultaneous Users

Regardless of what platform you choose, with 10,000+ users, you'll have to pay close attention to a couple of things in particular.

Number One: Parsing. Parse calls serialize on a library cache latch (there are many). Too many parse calls will cause contention for this latch, and no amount of CPU upgrading will fix the problem. Parse calls that are converted to hard parses are even worse; they serialize on *the* shared pool latch (there's only one). Hard parses are motivated by parse calls that cannot share parse trees generated by other sessions. Excessive hard parsing is caused by not using bind variables. Using cursor_sharing=force in 8.1.6+ is a nice workaround, but it's not as efficient as using bind variables in the first place (we estimate 25% degradation in tests).

Summary on parsing: Eliminate hard parses by using bind variables. Eliminate parse calls by writing apps that don't parse when they don't have to. Reuse cursors within a session instead of re-parsing. Use a middle tier that pre-parses commonly used SQL so and allows many client sessions to share those cursors.

Number Two: LIOs (logical I/O calls). Many of us were taught that "LIOs are free"; that when we've eliminated PIOs, we're "finished tuning." Very, very wrong. PIOs consume only about 200-500 times more elapsed time than a LIO (not tens of thousands, as you might have heard). Every LIO an Oracle system does serializes on a cache buffers chains latch. An Oracle user session obtains one of these latches for every LIO it executes, to determine whether the block it needs is already in the database buffer cache. If you have too many LIOs, your system will die because of contention for cache buffers chains latches.

With lots of users, it's easy to have lots of LIOs. Even if a SQL statement is only moderately inefficient, multiplying inefficiencies by 10,000+ can make life very difficult for you. Fight the temptation to allow any SQL statement on your system that does more than about 20 LIOs per row returned. Aggregation queries (queries using sum, min, max, etc. with a "group by" clause) are excepted. You can count LIOs executed by a SQL statement by summing query+current in your tkprof output (or in the "LIO blocks" statistic of the performance diagnosis tool my company will beta later this month). Of course, when you eliminate LIOs, you'll have naturally eliminated PIOs as well. But you have to focus on LIO elimination to get where you want to go.

Summary on LIOs: Eliminate LIO calls by prohibiting inefficient SQL from ever reaching your system. Eliminating PIOs is not enough.

Very best of luck to you!

Cary Millsap
www.hotsos.com

"Alan" <alanshein_at_spambuster.erols.com> wrote in message news:99aq3g$8me$1_at_bob.news.rcn.net...
> What do I need in the way of hardware to support 10,000+ simulataneous
> users, given the following:
>
> 1. OS must be NT (Windows 2000) as opposed to Unix.
> 2. Each user's processing is not expected to be intense. They will be
> running canned queries against denormalized reporting tables.
>
> Thanks.
>
>
Received on Wed Apr 04 2001 - 06:17:03 CEST