Re: Oracle Instances performance?

Billy,

Thanks for the feedback. I was guessing that multiple instances would not necessarily yeild better performance due to the bottleneck of hardware.

The situation I am dealing with does not come down to inappropriate or incorrect use of SQL, but rather to the size of some of the schemas employed.  I have a total of eight schemas with 7 of them no larger than 200M. The 8th schema is quite large, at 1.7G. That is where most of the difficulty will be coming from. Even simple queries joining a handful of tables against 1.7G of data will slow down the performance of anyone attempting to run queries/updates against the other schemas.

Increasing the capability of this box is a possible route, but it may make even more sense to yank the 1.7G schema out and place it onto its own resource. I was considering that perhaps multiple Oracle instance might help out somewhat, but from your comments and my research that does not seem to be a viable option.

Thank you again for your insight!
Daniel

In article <71u289$a0r$1_at_hermes.is.co.za>,   "Billy Verreynne" <vslabs_at_onwe.co.za> wrote:
> drpaner_at_intrex.net wrote in message <71shau$e2l$1_at_nnrp1.dejanews.com>...
> >
> >Is there a performance increase for using multiple Oracle database
> instances
> >to house multiple schemas versus one Oracle instance for all schemas?
>
> Depends on what the requirements are.
>
> >I have about 7 schemas running in one Oracle instance and find that
> everyone
> >gets clobbered when one individual ties up the engine running major
> >queries/updates against one schema.
>
> If you're not adding extra horsepower and memory, you're likely to get even
> worse performance. Instead of having a single instance's overheads and
> memory requirements, you will now have multiple instances wanting memory and
> CPU power. Remember that the instance is only as fast as the hardware. If
> that user now clobbers another instance on the same box, he will be taking
> horsepower away from any other instances and they will suffer.
>
> >I'm wondering if there is a noteable performance increase if I were to take
> >the largest schemas and run them in their own Oracle instances rather than
> >one instance.
>
> Off the cuff, I doubt it. However, it depends on the situation. You may put
> this large schema into a smaller instance, scale down the SGA, number of
> processes, etc for that instance to try and give the user of that large
> schema less of the machine's resources and make the other instance bigger
> and better ito resources.
>
> But will that really solve the problem? Any of these methods are only
> addressing the symptom of the problem and not the problem itself. Is that
> user doing the correct thing? Or is it due to ignorance that can be
> addressed by training? Can front-end tools be purschased or develop to meet
> that user's requirements (having users use SQL directly is a no-no in most
> cases IMO). Is the back-end database design correct for the business
> requirements of the user? Etc. Etc.
>
> IMHO we too often try and find technical solutions to human related
> problems. Like implementing internet filters in the business environment to
> address the "morality issues" of the business and the employees. And from
> your brief comments above, it seems to me to be the same type of issue. My
> thoughts anyway. :-)
>
> regards,
> Billy
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Nov 06 1998 - 07:39:01 CST