Re: How to tell which Oracle process goes with which instance on a Windows box

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<ffi6busm1k7g9ih4ld3b3fs23ds9g4p53b_at_4ax.com>...
> On 9 Apr 2002 09:10:19 -0700, surooot_at_hotmail.com (Wayne) wrote:
>
> >I have two NT boxes with multiple database instances each (up to 20
> >each at times) for non-production testing (performance is not a
> >concern). One box has 8.0.4 & 8.1.5 and the other has 8.0.5 & 8.1.7.
> >
> >The issue is this, one of the boxes suddenly get pegged at 100% CPU
> >(they are both dual CPU w/1GB ram) and I need to figure out which
> >insatnces as getting hit?
> >
> >In the process list I have many Oracle80.exe and some Oracle.exe, the
> >later being the 8.1.x and the prior being 8.0.x. Lets say that there
> >is one of each (an oracle.exe and an oracle80.exe) at 50% CPU - how do
> >I tell which database instance they represent?
> >
> >Currently I start looking through the directories for each instance
> >(they are always in their own sub-folders under an oracle dir) for one
> >with a recent time stamp on the data files, when I find one I look at
> >the active sessions in that DB to see if that is the one - sometimes
> >it is and sometimes it is not.
> >
> >Any suggestions (not about running 20 Oracle instances on a Windows
> >box - that part works fine) on how to track the process name in the
> >process list to an actual instance?
> >
> >Wayne.
>
> I know you are not going to like this, however: anyone running 20
> instances on a single server with only 1G of memory clearly doesn't
> have the faintest idea what he is doing. A database in Oracle is not
> identical to a database in sqlserver. Did you ever check Oracle's
> performance statistics? Did you ever determine the fault rate of your
> windows server? If they are adequate we are discussing toy databases
> and I don't see what we are talking about, and your databases may be
> well converted back to sqlserver or access. However, you still seem to
> be amazed that you are reaching 100 percent, which confirms my
> suspicions. Your statement 'that part works fine' is clearly rubbish.
> If your server is reaching 100 percent, 'that part' doesn't work
> 'fine'
> That all said, as Oracle is implemented multithreaded you have no
> other choice than to show the indidvidual thread numbers, and to
> connect to every individual instance and to verify whether any spid
> column of your v$process is showing up the thread id identified by NT
> tools.
> Yes, running 20 instances on a single server is working fine, you
> have just demonstrated yourself it is completely unmanageable.
>
> Regards
>
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Sybrand,

Thanks for you reply. In my original post I did not go into great detail because the details about running multiple databases on an NT box was not related to my question. You obviously are interested in those details and think that I am having my CPU's get pegged *because* I run multiple databases so I will explain some more.

As mentioned earlier I have two boxes with dual Xeon procs w/1GB RAM (DELL power edge 4300's w/300GB of 10k rpm SCSI-2 disks on a perc controller with 128MB cache). On each box I have anywhere from 15-20 databases at any time running. The performance on these boxes is great, matter of fact it is equal if not better to our SUN 450 boxes.

I am not promoting Windows as being better the UNIX (I run UWIN on windows as I am an old UNIX admin), but these are simply the facts. The ONLY time I get pegged at 100% CPU is when some idiot runs bad SQL and creates a Cartesian result in one DB at the same time we are exporting/importing on another instance on the same box (with other users using the client apps in other databases). My databases range in size from 500MB to the biggie at 75GB - most being around 10GB.

So when I see 100% usage, I know someone has done something dumb, I just need to figure out which database they have done it in so I can kill that process and tell them to stop it!

So, someone running 20 instances on a single server with 1GB of memory actually knows exactly what he is doing or he would not have been able to tune each database to use around 50MB or memory (remember that performance is not a concern). I admit it is not the smartest thing to do, but you can only work with what you have - and that is all I have so I make the best use out of it that I can.

As far as my original questions - I think I will create a small GUI that connects to each DB and displays all active sessions with session reads (similar to top sessions) to determine which DB is the really busy one.

-Wayne Received on Fri Apr 12 2002 - 20:50:11 CDT