Re: Finding which DB is consuming max resources

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:<3f38a674$0$10355$afc38c87_at_news.optusnet.com.au>...
> "Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:3F37C0D4.31D5FDBF_at_remove_spam.peasland.com...
>
>
> > Sign on to each of your 30 instances (or is it 25? Your posts conflict
> > with each other) with SQL*Plus and issue SHOW SGA. This will give you
> > the memory for the *shared* portion of the instance. That's a start...
> >
>
>
> don't worry. there was one the other day
> somewhere else asking for how to run 100 instances
> in the same box. and he got meaningful replies...

Had this happen to me once (beyond my control, will describe why at the end). What I would do is run a top command and get the top 10 user spids for oracle and then pass those to a script that looged into each instance and looked for the spid in v$sessions. Usually just one or two causing the heavy load.

Why did this happen, an application was purchased from a vender which could only deal with one hard coded schema name. We used the software to provide its functionality as a service bureau to multiple companies. The vendor system required a seperate instance for each company otherwise they would all be able to see each other's data. Business was good and before we knew it there were 20 instances. Had to live with this for a long time until the vendor rewrote their software. Received on Fri Aug 15 2003 - 15:53:38 CDT