Re: oracle - high cpu usage

On Jul 30, 10:16 am, kw <k..._at_spam.pl> wrote:
> hpuxrac pisze:
>
>
>
>
>
> > On Jul 30, 10:16 am, kawu <k..._at_spam.pl> wrote:
> >> Hi,
>
> >> Few days ago my database "eat" a lots of CPU and has high events waits
> >> and I/O waits. Now problem is gone, but I want to know what to do in
> >> future if that situation come back. How to find which session/sql cause
> >> problem? What to do with that session?
>
> >> Thanks in advance,
> >> kawu.
>
> > What version and what platform are you running oracle on?  Enterprise
> > edition, standard, or what?
>
> > What monitoring tools does your organization have setup if any?
>
> > Do you have any kind of direct access into the database and what kind
> > of role do you have?
>
> > If your database is relatively recent OEM provides a fairly nice way
> > to see what's impacting your database.  Using it well depends on your
> > experience level with oracle as a database and your experience as a
> > developer/tuner.
>
> It's Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production on
> SunOS 5.9 sparc.
>
> I could login to database as sysdba.

Sometimes it's as simple as an index missing. If you have inappropriate full table scans and a large SGA, you use up lots of cpu scanning through the buffers, waiting on certain latches, and i/o waits if you are thrashing the SGA. Statspack and OEM as the others have posted are a good start to figure out the sql involved, then you can figure out what the optimizer is trying to do. OEM can show you the plan if you grab the problem sql while it is running. See the performance tuning manual for lots of good information on where to start (and keep in mind some things are not quite perfectly stated in the manuals). Also see method-r.com.

jg

--
@home.com is bogus.
Bank error in your favor, collect $100,000.
http://kursinfo.himolde.no/in-kurs/inf111/pensum/bank.pdf