Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow queries on distributed databases

Re: Slow queries on distributed databases

From: Frank <frankbo_at_interaccess.nl>
Date: Thu, 28 Sep 2000 10:01:17 +0200
Message-ID: <8qutsm$5eg$1@porthos.nl.uu.net>

Comments inline

--
Frank
Lisa McGrath <lmcgrath_at_uwsa.edu> schreef in berichtnieuws
39D21A25.94CA1032_at_uwsa.edu...

> If only we could write simple views...
>
> Let me give you a little background that might help explain what we are
trying
> to accomplish. Up until a month or so ago we had a single UNIX box (AIX
> 4.3.2.0) (Box P) that held student data, and everything worked great. The
> student data is split (logically and physically) into warehouse data
(static
> back to 1973) and production data (current and last years info,
updateable).
> The two types of data are the same - there is a production student table
and a
> warehouse student table, but the table definitions are exactly the same.
The
> two types of data shared many look-up tables. Then, for purely political
> reasons, we had to move the warehouse data onto a different UNIX box (AIX
> 4.3.2.0) (Box W) , and the lookup tables were moved with the warehouse
data.
>
> As much as possible we have tried to cut down on network traffic. We
created
> views on Box P that link the needed tables that are on box P, then we
have a
> view on box W that references the view on box P, so all the joins are done
on
> box P, and the results sent back to box W (and vice versa). Views set up
like
> that seem to work fine, even if the views on box P reference the small
lookup
> tables on box W.
Eh? Your production machine uses lookup tables of a datawarehouse? Duplicate! Your production data should be on the production machine!
>
> The problem pops up when an end user uses a view on box W (that uses box W
> tables) to get data for, say, 1989, and joins that with a view on box W
that
> references a view on box B (as described above) to get data for 1999. The
views
> that the users need are very complex, with many tables, user defined
functions,
> and calculations in them. The view on box P needs to reference the lookup
> tables that are on box W. If, from box W, we just run the view on box P
then
> it works fine. Its when we try to join the two views that it comes to a
> grinding halt.
That was (kind of) the point I was trying to make - you join views with views, possible getting the worst execution plan possible, or retrieving far too much data. Look into the result set expected, and create the appriopate view(s) on the appropiate machines - think inline views, dynamic sql.
>
> The Powers That Be don't like the idea of replicating data using
snapshots, so
> we're first looking into init<SID>.ora parameters to be sure that we are
tuned
> as much as possible for this type of processing.
Well, sometime you will have to copy/move production data to the datawarehouse, so why not do that daily, weekly or monthly? I've noticed a couple
> parameters from the v$parameter table, open_links and max # open links
per
> session, that might have some bearing, but I'm having a hard time finding
any
> info in the manuals about them.
That would be the Oracle8 reference manual - if not installed on the machine, it's still on CD. Otherwise, check http://technet.oracle.com If open_links is still default (4) then that might very well be one of the causes!
>
> Does anyone know of a good website or book that describes each parameter
> available for the init<SID>.ora file in 8.0.6?
>
> Thanks very much for your help and suggestions.
>
> Lisa
>
>
>
> Frank wrote:
>
> > In all the previous posts, I did not see any indication of table sizes.
> > All in all, consider network traffic as a threat - consider creating the
> > views on "the opposite side" if you feel it will reduce network traffic.
> > Besides, I strikes me as ineffective to refer to a view (on B) from A,
> > whereas this view uses data on A (being remote, when seen from B).
> > That is doubling network traffic!
> > Rather rework the select not to rely on that view, but on the
> > tables in A, joined with (simple) views to B.
> >
> > BTW, AIX 4.2 is not very effective with TCP/IP - consider 4.3.2 - you
> > didn't state the o/s version...
> > --
> > Frank
>
Received on Thu Sep 28 2000 - 03:01:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US