Re: Perf. problems with multiple users

The problem is not that my query runs slow. It will do no matter how much I tune my Oracle database/Query. It's a complex join with several million records. I know ways to make it a bit faster but it don't need to be. The problem is that other users are suffering because I'm run a complex query.

If there were three active sessions all performance issues would be solved if I could get Oracle to distribute its processing powers and resources equally between the active sessions.

Most sessions are only active for a short period of time, it's only my session that can be active for up to 5 minutes. During these five minutes the system is unworkable for all other users.

In article <0143b2b6.7133ccef_at_usw-ex0102-015.remarq.com>,   Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 13 2000 - 00:00:00 CDT