Oracle taking 50% CPU constantly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

OK, thank you. It seems to me that no jobs are running.

select job_name,state from dba_scheduler_jobs;

SM$CLEAN_AUTO_SPLIT_MERGE	SCHEDULED
RSE$CLEAN_RECOVERABLE_SCRIPT	SCHEDULED
FGR$AUTOPURGE_JOB	DISABLED
BSLN_MAINTAIN_STATS_JOB	SCHEDULED
DRA_REEVALUATE_OPEN_FAILURES	SCHEDULED
HM_CREATE_OFFLINE_DICTIONARY	DISABLED
ORA$AUTOTASK_CLEAN	SCHEDULED
FILE_WATCHER	DISABLED
PURGE_LOG	SCHEDULED
MGMT_STATS_CONFIG_JOB	SCHEDULED
MGMT_CONFIG_JOB	SCHEDULED

Open in new window

select job, this_date from dba_jobs;

1	(null)
2	(null)

Open in new window

Any other ideas?

I want to start the ADDM. But I can't find how to open it. It says the Database Homepage. But that doesn't seem to run.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

I'm positive that it is version 11g.

Anyone any more proposals?

An Oracle expert to take a look is also welcome...

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

OK, It is a Windows installation but in task manager the processes that are taking the constant CPU load of 50% each on one of the cores is just oracle.exe. Other processing are doing nothing. Does you explanation then still apply?

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

I appreciate all the help. But we are looking in the wrong direction I think.

The reason that makes me believe that is that the server had exactly the same config before they upgraded their code and made change to the database. Only then it started.

The strange thing I see is that on 2 database we see unusual high portions of CPU for certain queries that it should be doing when querying the TOP SQL calls by CPU. Seeing that looks like t a loop in the code to me. The problem is how do I proof that to the developers? :-)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

OK, I took all the screenshots and queries in a PDF. But it seems I forgot to upload them. So here's a second attempt...

I hope these queries and screenshots help a bit. Mind that it's 2 databases which both are showing a loop or bug in the code according to me.
Top-SQL-queries-for-Experts-Exchange.pdf

ASKER

additional problem is that it's not in-house developed software. It's a package we buy from fuji. So I don't have a view on the code. But the problem started ever since they made changes in November.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

I chose my own solution since it's not really solved and that is the solution people will see as the final answer when they search for this problem. Thanks again to everyone