Path: text.usenetserver.com!out01b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!m37g2000prh.googlegroups.com!not-for-mail
From:  Steve Howard <stevedhoward@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle 9i performance troubles
Date: Wed, 08 Aug 2007 18:03:52 -0000
Organization: http://groups.google.com
Lines: 47
Message-ID: <1186596232.574171.295000@m37g2000prh.googlegroups.com>
References: <1186590533.167751.243770@o61g2000hsh.googlegroups.com>
NNTP-Posting-Host: 132.174.33.96
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1186596233 8511 127.0.0.1 (8 Aug 2007 18:03:53 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 8 Aug 2007 18:03:53 +0000 (UTC)
In-Reply-To: <1186590533.167751.243770@o61g2000hsh.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.6) Gecko/20070725 Firefox/2.0.0.6,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: m37g2000prh.googlegroups.com; posting-host=132.174.33.96;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.server:432656
X-Received-Date: Wed, 08 Aug 2007 14:03:53 EDT (text.usenetserver.com)

On Aug 8, 12:28 pm, Joseph <papers....@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