Re: query across 3 Oracle DBs and MS-SQL
From: <sydneypuente_at_yahoo.com>
Date: Fri, 13 Mar 2009 02:56:11 -0700 (PDT)
Message-ID: <4fb908a1-bc80-4810-90de-6dd1d0c88bee_at_j38g2000yqa.googlegroups.com>
On Mar 12, 9:55 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
> sydneypue..._at_yahoo.com wrote:
> > Hello Guys,
>
> > I will shortly be tasked with running some complex queries across 3 X
> > Oracle DBs and MS-SQL. Each of these db has millions of lines. I have
> > run queries again a single Oracle DB with millions of lines with some
> > success - that take several hours to complete.
> > However I have little idea of what is best practice to query databases
> > on different servers and a vendor other than Oracle in the mix too.
>
> > hints and tips and URLs gratefully received!!
>
> > Syd
>
> While there is HS and gateways and database links, you need to
> understand how it functions before you pull that trigger. In my current
> job I helped fix an app issue where the app did a 5-way join via dblink
> to a mainframe (all tables were on the mainframe). At the point I was
> called in, their web-based app had almost fatally crippled a mainframe
> with many billions of I/O's sec in just an hour or so... It was 8 hrs
> before it recovered - after I stopped the "gateway".
>
> In the Oracle db the app did a 5-way join to 5 remotely defined tables.
> What the mainframe saw for EACH query was:
>
> select * from a;
> select * from b;
> select * from c;
> select * from d;
> select * from e where a=xyz;
>
> So Oracle pulled the 4 tables plus the one or two rows from table 5
> totaling more than 450MB for EACH query and then having Oracle perform
> the JOIN - and the front end app server was doing thousands of these
> per/hr.
>
> Due to semi-static nature of the data, the solution was to create MVIEWS
> on the Oracle DB and have them populated once a day. That way the JOIN
> occurred locally and NOT on the mainframe/network.
Ah - I think the crippling of the dataabase host is something I want to avoid at all costs.
However I dont quite undertand this:
> What the mainframe saw for EACH query was:
>
> select * from a;
> select * from b;
> select * from c;
> select * from d;
> select * from e where a=xyz;
What sort of query does this? Why all the columns and all the rows for the first 4 tables?
And also the data I need to query is semi-static, ie not often updated.
Can you give me just a bit more info on what the view would actually consist of?
Date: Fri, 13 Mar 2009 02:56:11 -0700 (PDT)
Message-ID: <4fb908a1-bc80-4810-90de-6dd1d0c88bee_at_j38g2000yqa.googlegroups.com>
On Mar 12, 9:55 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
> sydneypue..._at_yahoo.com wrote:
> > Hello Guys,
>
> > I will shortly be tasked with running some complex queries across 3 X
> > Oracle DBs and MS-SQL. Each of these db has millions of lines. I have
> > run queries again a single Oracle DB with millions of lines with some
> > success - that take several hours to complete.
> > However I have little idea of what is best practice to query databases
> > on different servers and a vendor other than Oracle in the mix too.
>
> > hints and tips and URLs gratefully received!!
>
> > Syd
>
> While there is HS and gateways and database links, you need to
> understand how it functions before you pull that trigger. In my current
> job I helped fix an app issue where the app did a 5-way join via dblink
> to a mainframe (all tables were on the mainframe). At the point I was
> called in, their web-based app had almost fatally crippled a mainframe
> with many billions of I/O's sec in just an hour or so... It was 8 hrs
> before it recovered - after I stopped the "gateway".
>
> In the Oracle db the app did a 5-way join to 5 remotely defined tables.
> What the mainframe saw for EACH query was:
>
> select * from a;
> select * from b;
> select * from c;
> select * from d;
> select * from e where a=xyz;
>
> So Oracle pulled the 4 tables plus the one or two rows from table 5
> totaling more than 450MB for EACH query and then having Oracle perform
> the JOIN - and the front end app server was doing thousands of these
> per/hr.
>
> Due to semi-static nature of the data, the solution was to create MVIEWS
> on the Oracle DB and have them populated once a day. That way the JOIN
> occurred locally and NOT on the mainframe/network.
Ah - I think the crippling of the dataabase host is something I want to avoid at all costs.
However I dont quite undertand this:
> What the mainframe saw for EACH query was:
>
> select * from a;
> select * from b;
> select * from c;
> select * from d;
> select * from e where a=xyz;
What sort of query does this? Why all the columns and all the rows for the first 4 tables?
And also the data I need to query is semi-static, ie not often updated.
Can you give me just a bit more info on what the view would actually consist of?
TIA Syd Received on Fri Mar 13 2009 - 04:56:11 CDT