cpu utilization 100% when starting database.

Hi

My database size is 30GB.
When i am starting the database, it takes 350MB of RAM, but cpu utilization 
shows 100% and system crashes.Please help me in this. Where am i wrong in 
setting configuration?

Regards
SWathi

SWathi,

It's hard to tell you exactly what is wrong, because you are providing very 
little details, not even a version of Oracle and your OS.

Start by checking your alert log for any messages/errors, also try to narrow 
down which process is taking the most amount of CPU using OS utilities (top, ps 
etc..).

If I remember correctly there was an issue with Oracle 10g -- if the database 
was shutdown for a while, upon startup gather schema stats (or similar) would 
kick in and take the server down.  Try do disable database jobs by modifying 
your spfile:

startup nomount;
alter system set job_queue_processes=0 scope=spfile;
shutdown immediate;
startup;

If you are using PFILE instead of spfile then just edit it directly.

HTH,
- Vitaliy

Hi Vitaliy

OS is windows 2003 server. Oracle 9.0.2.0.8 version is being used.
there is no error in alert log file.
I will try with job_queue_processes =0 in pfile as suggested.

Regards
swathi

Hi

Yes.Now after changing the value of job_queue_processes to 0 , cpu utilization 
is very less.Whats this parameter signify as we have set it to 0.

REgards
SWathi

SWathi,

I am glad it helped -- job_queue_processes parameter specifies the maximum 
amount of DBMS_JOB processes that can run concurrently in the database.

Probably you have a DBMS_JOB scheduled which is poorly tuned and when the 
database starts up the jobs kicks in and causes the server to slow down.

Check you active dbms_jobs:

set lines 132
set trims on
col WHOS_JOB format a55

select job,PRIV_USER||'.'||what whos_job,
       to_char(next_date,'yyyy-mon-dd hh24:mi:ss') next_date,
       last_sec,BROKEN
from dba_jobs;


to see what they are, run them one at a time from sqlplus (as the owner of the 
job [PRIV_USER]) and you'll find the culprit.

- Vitaliy

Hi Vitaly

Thanks a lot.
Now when i run this query given by you, it shows like this 
JOB WHOS_JOB                                     NEXT_DATE           LAST_SEC B
---------- ------------------------------------------------------- ------------
1 ORA_MONITOR.ora_monitor.db_perf_monitor;       2010-06-08 11:06:42 11:13:42 N
2 ORA_MONITOR.db_trends.collect;                 2010-06-12 02:06:00 11:05:10 N
3 ORA_MONITOR.log_monitor.run_analysis;          4000-01-01 00:01:00          Y
4 ORA_MONITOR.space_monitor.run_analysis;        2010-06-08 11:06:24 11:13:24 N
5 ORA_MONITOR.job_monitor.start_monitor;         2010-06-08 11:06:22 11:13:22 N
6 ORA_MONITOR.job_monitor.collect_job_statistics;2009-10-09 15:10:29 15:35:17 N
7 ORA_MONITOR.app_monitor.start_monitor;         2010-06-08 11:06:39 11:21:29 N

Now how shall i run these in sqlplus.please give example for one job.
Are these all default jobs?because i did not set these jobs. why is this process running to capture database statistics

REgards
Swathi

Swathi,

Searching for ORA_MONITOR on google it looks like it's a third party (non 
ORACLE) monitoring tool, which obviously is causing a problem in your database:

   http://www.orafaq.com/tools/softtree/db_tools

I would suggest to disable all these jobs by ORA_MONITOR since you didn't 
install them and don't seem to be using this product.

Here's how:

For each job in the above list issue the following command from sqlplus (as 
sysdba) [replace JOB_ID with the job id's from the list):

exec dbms_job.broken(JOB_ID,TRUE,null);
commit;

Once you've done that you can re-enable job_queue_processes back ...


- Vitaliy

Hi Vitaliy

Thank u very much.
Changed all jobs broken status to 'N' and enabled parameter job_queue_processes 
back. Now there is  no problem and database is fine.

Regards
SWathi

Excellent!  I am glad I was able to help.

PS: you must have meant "broken status to 'Y'"?

   N="not broken/enabled"
   Y="broken/disabled"

- Vitaliy

Yes. BROKEN status changed to 'Y'.mistakenly written as 'N'