max out CPU

I have a quick issue and I need some advise on this.

I have an Oracle server running windows 2000 server with 933 MHZ and 1.2 G of memory. I have 6 8 databases running on this box and the total memory allocated to these databases are about 800M.

everytime when someone do and insert or update to anyone of these databases, I look at the CPU and it's max out 100% and every body else experience the performance issues on the whole box.

I looked at the swap file and It's not bad at all. So what could be the problem ??? need more memory on the Box ??? add another CPU ????

How to approach this problem, please advise.

Thanks

Hey Mike, wasssuuuupppp?

Did you check the alert.log?
Did you check the event viewer on NT
Did you monitor task manager while it was pinned, you can sort the "processes" by CPU consumption, this should point you in the right direction.

MH

Mr Hanky,

Thanks for your reply.

Here are the responce to your advises.

1. Did you check the alert.log?
The alert.log look fine, there are no strange errors on my log file.

2. Did you check the event viewer on NT
Look in this and there is nothing strange.

3. Did you monitor task manager while it was pinned, you can sort the "processes" by CPU consumption, this should point you in the right direction.
I look into the CPU and everytime I do an insert to the table in one database, that database will use most of the CPU.

Any other advises ???

Thanks so much

Can someone give me some advise please ???? Jeff or Julian or any body ????

i bet itz a bad query. find the session that is pegging the cpu and start a trace on it.

otherwize, which oracle proces is pegging the cpu? dbwr, smon, pmon, ckp ?

It's not a bad query causing this, b/c it's just the basic insert into the database.

how about unindexed fks? have u traced it?

WHICH process is hogging the CPU? Is is a user process?

Jeff,

any insert or update process would hog the CPU. I talked to the ADMIN guy in my company and he said that b/c we have only one processor so that every time when the user do any DML would hog the CPU. To me , that make no sense b/c he is telling me that everybody had to wait until that DML finish ???

Is that true, Jeff???

Should I create a profile and grant that profile to each user so I can limit the CPU_PER_SESSION, and SPU_PER_CALL ???

Do you think it would help ???


Where have you been ??? This forum has been quiet lately without any experience people.

Please advise

Sure, on a single processor box, any DML operation will burn some CPU. However, a tuned insert or update statement should not consume 100% of the cpu for a long time.

When I say "WHICH Process", I mean which one of the oracle background processes is running the CPU to 100%? Each of the processes might indicate a different problem. If the process is a user process (not the statement, but the dedicated backend proceess) then it is probably a poorly tuned query. If the process is the DBWR, you may have db_block_buffers issues. If the process is LGWR, you may have I/O contention issues. It really depends on WHICH process is hanging.

On Solaris, you can use /usr/ucb/ps -aux | head to see exactly which process is consuming 100% of the CPU. Surely on Windoz there is a similar command/program that will show you which process is hung.

anyone has ideas of how to check the processes on windows???

Hi
Check out Oracle for Windows NT Performance Monitor, add a new log setting for all the Oracle Threads, as in NT there will be only one process Oracle.exe and the oracle processes which are seen in Unix are actually threads under Oracle Process, you will find them numbered, select all of them and monitor it for some time (settings are available) for that. stop if u want and analyse them using the System Monitor- click on View log file.
HTH
-dharma

dharma,

When you say Win NT performance monitor, did you mean Windows Task Manager? Can you give more detailed steps?
New to SA, really appreicate your help!

AHHHHRRRGGGHHH!!

The post that would not die!!

I've been thinking about this post for a little while and here is my question since this is now on it's third day.
Mike, please, PLEASE tell me you have re-booted this server in the past three days.
Have you?

MH

I have but it doesn't help

Please don't be upset and frustrated, we try to understand our problem Mr Hanky

Quote:

---

Originally posted by mike73
I have but it doesn't help

Please don't be upset and frustrated, we try to understand our problem Mr Hanky

---

I'm not upset or frustrated, if you told me that this has gone on for three days and you did not try a re-boot I'd be upset, that may be an understatement.

MH

elaine,
if u are using 8i, it's found under "database administration" menu in program files.(where u find dba studio etc) if u dont find it there, u have not installed it.
HTH.
-dharma