Re: Performace with 500,000 tables

Jonathan Lewis wrote:
>
> Isn't it wonderful how you get can get big
> answers by multiplying together a couple
> of fairly reasonable numbers !
>
> This leads to another issue, though.
> 500,000 tables MIGHT survive a very
> large shared pool - but if it were 10,000
> copies of 50 tables, and all the SQL was
> sharable SQL aimed at each user's copies
> of those 50 table, then there would be
> 10,000 child cursors for every SQL statement,
> and the library cache would be vast, with massive
> contention on every latch as people tried to
> execute their specific child cursor.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon a new one-day tutorial:
> Cost Based Optimisation
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____England______January 21/23
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Vladimir M. Zakharychev wrote in message ...
> >> I'd have to question any design for a database that requires half a
> million
> >> tables. Point of fact, I can't imagine any circumstances when that
> number of
> >> tables would be required.
> >>
> >
> >One possible case could be if they are porting an SQL Server app
> >that (most certainly) creates a lot of temporary tables during the
> >normal course of operation. If the app does, and they expect a lot
> >of concurrent users, 500,000 tables doesn't sound all that
> >unimaginable... Having, say, 50 temp tables created dynamically
> >per session, they only need 10,000 concurrent users to achieve their
> >goal (that's not counting permanent tables they will surely have.) :)
> >
> >--
> >Vladimir Zakharychev (bob_at_dpsp-yes.com)
> http://www.dpsp-yes.com
> >Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based
> internet applications.
> >All opinions are mine and do not necessarily go in line with those of
> my employer.
> >

Similarly, you'd want some good space planning - if you've ever done a 'select * from dba_segments' on full Oracle apps (around 50,000 segments), it comes back a week from Sunday...could take while for 1.5mill

hth
connor Received on Fri Dec 20 2002 - 15:23:36 CST