Oracle 9i performance troubles
Joseph
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
Steve Howard
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
Cristian Cudizio
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.com
http://cristiancudizio.wordpress.com
Steve Howard
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
Cristian Cudizio
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.
R.Wang
Hi Joseph,
Go try the script to show active users instantly. The script is
available at http://www.oraclepoint.com/topic.php?filename=238&extra=page%3D1#onlinearea
This is only a script to give you instant info. at one point of
time. If you need to do more investigation, please recur to STATSPACK
or others.
Hope it helps.