High CPU utilization

[Rafael Flores_1]

Hello,

This is my first posting to this group and as you would expect caused by a critical situation...

I've got an app running an Oracle instance on my HPUX11 box. From time to time I can see one Oracle session "eating" up all my available CPU though I can't see no real activity on Oracle going on, nor swapping is strangely abnormal. Furthermore, if I attempt to trace the Oracle session I don't get any trace file at all.

You can see a "top" of the situation on the attached file.

Have you ever seen this behaviour? What would you look for here? Any hints?

Many thanks!

[RAC_1]

Check the underlying sql query. You really can not make out what is it doing by looking at top.

OR run tusc and check what it doing. lsof will also help to some extent.

[John Palmer]

Yes, seen it lots of times! Usually it's caused by badly written SQL and/or a badly designed database.

I agree with Anil, you need to check the query that's being run, then do an 'explain plan' to find out how Oracle is processing it. Often you'll find that full table scans of large tables are occurring. Sometimes you can improve things by adding an index.

Regards,
John

[David_246]

Hi Rafael,

Whereis tusc ??

If you run this command it will tell you where tusc is installed, else you'dd better get it.

tusc -p

f.e. : /usr/local/bin/tusc -p 2398

Will show you on a low level what the process is doing. Be carefull, it will show you a lot !!
tusc will show you what your programm does.
f.e.
tusc ls -l

Maybe it will help a little in debugging.

Regs David

[Paddy_1]

As Anil has said look at your query.
I would just like to add that there is this command called "EXPLAIN PLAN FOR " iwhich will tell you how Oracle schedules the query and is a good diagnostic to see if you have a CPU bound stuff.

[Zafar A. Mohammed_1]

All the above answers are pointing in a correct direction. You can set EXPLAIN ON and check the Costs, and even its better to ANALYZE and do some stats_pack & dbms_stats. We have very good Oracle Guru's in this group, if you can send the Queries and Explain outputs we can dig more. Check some performance and tunning material also.

Thanks
Zafar

[Michael Steele_2]

Use 'lsof' to see what is being used:

lsof -p pid

-or-

lsof -p 3908 (* using your attachement*)

Check your disk activity with :

sar -d 5 5

Note anything greater than 50% utilizied. See what file system resides upon it:

pvdisplay -v /dev/dsk/cXtYdZ

Redo logs will be high for instance.

And cross reference with 'v$filestat' and statspack.

Need to check cache performance as well as anything less then 90% cache hit needs adjustment.