Re: query across 3 Oracle DBs and MS-SQL
Date: Sun, 15 Mar 2009 15:44:40 -0700 (PDT)
Message-ID: <490ba396-4839-47a2-98a8-f59ff7682736_at_j35g2000yqh.googlegroups.com>
On Mar 13, 2:56 am, sydneypue..._at_yahoo.com wrote:
> 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-
I've also seen this. In a nutshell, people write dumbshit apps (often called "dashboards" or some other buzzword having to do with DSS or managerial information aggregates) to suck all the data out of the database rather than get the data they need beforehand and/or use the abilities of the database to get only what they need at the time.
You need to create a logical design and translate it to a physical design. The physical design has to avoid doing dumbshit things. If most of your data is static, it should be on its own machine, with some way of updating it that doesn't hammer your OLTP database. Oracle materialized views is one way of doing this, analytics can be another. Oracle and HP even have a product now that uses the storage to limit the data returned before the db has to filter it.
Of course, I'm assuming you have an OLTP database. Is that the case? I'm not real sure what "millions of lines" in a database means, so I'm interpreting it as you don't really know the fundamental relational concepts and terminology? This stuff isn't magic, but it also just follows directions, you can't really just assume it will figure out what you mean unless you speakie its language. tahiti.oracle.com has a concepts manual you need to read, and you should probably get your hands on a popular data warehousing book.
jg
-- _at_home.com is bogus. What happens when products call home and tell vendors what the product usage is: http://www3.signonsandiego.com/stories/2009/mar/13/1b13cymer205411-cymer-trims-more-workers/?uniontribReceived on Sun Mar 15 2009 - 17:44:40 CDT