Re: Oracle 9i performance troubles

On Aug 9, 2:57 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Aug 9, 6:29 am, Cristian Cudizio <cristian.cudi..._at_yahoo.it> wrote:
>
>
>
> > On Aug 8, 8:03 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
> > > 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
>
> > Just a little poin, i think a lapsus, but wait_class column in 9i does
> > not exists it has been
> > introduced with 10g for not idle events you have to select what you
> > are interested in.
> > Suggestion of Statspack is much simpler.
>
> > regards,
> > Cristian Cudizio
>
> >http://oracledb.wordpress.comhttp://cristiancudizio.wordpress.com
>
> Thanks, Cristian.
>
> I thought about that later, but I think you can still get it in 9i if
> you join to v$event_name?
>
> Regards,
>
> Steve

No, unfortunately you have to know by yourself (by reading on performance tuning guide appendix events descriptions) which event is idle. In v$event_name you find only the event description and meaning of p1, p2 p3 columns of v$session_wait. For example it not written that "pmon timer" is an idle event, you have to go to documentation.

regards,
Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Thu Aug 09 2007 - 10:15:47 CDT