Connecting Multiple Oracle Databases with one Application

[satnam singh negi]

Hi All,

I have to communicate with more then 50 Oracle databases from a single application , how can i achieve this ? If i am succeeded in this then i have to check the performance with several databases by firing the queries to each one.

Please share your ideas as i have only worked with MySql .


Thanks in advance.

[Jan Cumps]

No difference for MySQL or Oracle.


Do you need to connect with all 50 at the same time, or is it: connect to #1-> do somethiong -> disconnect -> connect to #2 .... ?

[satnam singh negi]

Jan Cumps wrote:No difference for MySQL or Oracle.


Do you need to connect with all 50 at the same time, or is it: connect to #1-> do somethiong -> disconnect -> connect to #2 .... ?

actually the scenario is ,this is an Enterprise data management and i have to develop an application in java to monitor it, means i have to check the performance of database(s) , there are many parameters to monitor the database(s). like cpu performance,hit ratio,memory utilization. suppose there is select query and i got results in 3 sec, but the best case was suppose 1 sec. so 2 sec is kind of threshold. this is how i have to check the performance but got stuck with first point that how to get connected with database(s) in java.

it may be like a cronjobservlet that will run in each 5 minutes in order to Monitor the performance.

Thanks for the quality time.

[Martin Vashko]

Doesn't Oracle itself come with utilities to monitor performance? I'd say there is something like Enterprise Manager for Oracle, though it might be a standalone product. There are plenty of tools (including third-party ones) to monitor infrastructure nowadays; it seems strange that someone with an infrastructure so large would get to develop proprietary solution....

Anyway, measuring response time of a query is quite a crude way to asses database performance. The timing might be affected by events outside the database, what if the delay will be in your application? Oracle provides dedicated views to query performance data which are going to be much more precise (distinguish several types of bottlenecks, for example). This is quite a broad topic, but you might start here.

[satnam singh negi]

Martin Vajsar wrote:Doesn't Oracle itself come with utilities to monitor performance? I'd say there is something like Enterprise Manager for Oracle, though it might be a standalone product. There are plenty of tools (including third-party ones) to monitor infrastructure nowadays; it seems strange that someone with an infrastructure so large would get to develop proprietary solution....

Anyway, measuring response time of a query is quite a crude way to asses database performance. The timing might be affected by events outside the database, what if the delay will be in your application? Oracle provides dedicated views to query performance data which are going to be much more precise (distinguish several types of bottlenecks, for example). This is quite a broad topic, but you might start here.

Thanks for the response Martin, of course there are plenty of utilities but they have complicated representation, the client does not understand them,so the requirement is to make a simple graphical format like red and green buttons to show the performance, if performance is ok then green one otherwise red .

A bunch of thanks to aware me about application impacts and performance.

[Martin Vashko]

satnam singh negi wrote: of course there are plenty of utilities but they have complicated representation, the client does not understand them,so the requirement is to make a simple graphical format like red and green buttons to show the performance, if performance is ok then green one otherwise red .

I'd be quite surprised if these existing tools would not provide some reporting screens with just the red and green icons your customer desires. Plus, these tools would probably include functionality to email, page or SMS someone who can actually do something about the critical situation, other that just look at the pretty blinking red icons

The effort you plan to invest into this project might instead go to designing the reporting screens according to your client wishes. Of course, the decision is up to you and your client. I was only remotely involved in designing a monitoring system like this (with icons and connecting lines representing the hierarchy of the infrastructure), so can't offer deeper insight than this.