Re: Oracle 9i performance troubles

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Wed, 08 Aug 2007 18:03:52 -0000
Message-ID: <1186596232.574171.295000@m37g2000prh.googlegroups.com>

On Aug 8, 12:28 pm, Joseph <papers...._at_gmail.com> wrote:
> Hi Folks,
>
> I have a database running Oracle 9i that from 2 days seems to have
> troubles in performance.
>
> Ho can i see which users have more activity in queries or in resources
> consumpion?
>
> Many thanks
> BR
> Joseph

Hi Joseph,

What OS and dot release of Oracle, i,e. Windows 2000 Oracle 9.2.0.1, etc.

That's a pretty wide question, but if you have OEM installed, you can (if licensed) use the performance tuning pack to find out general things OEM thinks may be a problem.

I would also read the following...

http://download.oracle.com/doc/cd/B10501_01/server.920/a96533/toc.htm

I would also install statspack (info should be in the link above) to get an idea as to what the top waits are.

If you are looking for a quick overview, try the following...

select event,round(time_waited/100, 0) time_waited_secs   from (select *

          from v$system_event
          where wait_class != 'Idle'
          order by time_waited desc)

  where rownum < 11
/

...run that a few times with 60 seconds or so between runs, and waits that pop out at the top are worth googling, or searching for the in the link above.

HTH, Steve Received on Wed Aug 08 2007 - 13:03:52 CDT