Re: Perf. problems with multiple users

Jonas Malmsten <jonas_at_malmsten.net> wrote:
>We have an Oracle 8.0.5 server running on an Enterprise 450
 server with
>2 CPUs and 2 Gig RAM.
>
>As long as no-one performs any heavy queries we have no
 performance
>problems. Bu when I log into SQL plus to retrieve some
 statistics
>manually - ie run some heavy queries with several joins etc the
>performance drops to below acceptable level. I can live with
 that my
>heavy query takes a while to execute but I don't understand why
 that
>should have such a huge impact on the other users. My query
 does not
>lock any tables (select statements only).
>
>Is there a way to limit the resources eaten by my query so that
 other
>people can work as usual.
>
>An optimal solution would be that if you had 10 sessions
 connected each
>session should be able to use at least 10% of the server
 performance if
>needed. If only one of those ten sessions is active then
 offcource this
>one session should get all the performance but if all 10
 session where
>active I want the power to be equally distributed through the
 sessions.
>
>Thanks for any help
>
>//Jonas
>

It sounds like you should look into tuning your query! When was the last time statistics were updated on your tables and how large was the sample size?

You might want to run an explain plan or turn autotrace on the next time you run it to see what it is doing. Compare the plan shown to the available indexes.

You can run bstat/estat and look for resource bottlenecks on your system.

But it is often the case that bad system statistics can be eliminated by tuning a couple of heavy hitter SQL's.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jul 13 2000 - 00:00:00 CDT