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: Query performance dramatically different

Re: Query performance dramatically different

From: Sylvestre <Sylvestre_at_world.com>
Date: Wed, 1 Feb 2006 21:57:04 +0100
Message-ID: <43e120a5$0$17442$626a54ce@news.free.fr>


Are the network interface the same on all the computer ?
for request with lot of data, array size must be setup to the max..

"Dusan Bolek" <spambin_at_seznam.cz> a écrit dans le message de news:
1138780719.320158.97750_at_g14g2000cwa.googlegroups.com...
> Hello,
>
> I have encountered a weird problem last week and
> been dealing with it
> since. I have a query that is accessing several
> tables in remote
> Teradata databases using Oracle OpenGateway(
> formerly known as
> Transparent GW). The problem is that the same
> query is finishing either
> in 30 seconds or in 30 minutes with this
> pattern:
>
> 1) if executed from sqlplus on my computer
> (9.2.0.6 client) - 30s
> 2) executed from my colleague's computer
> (9.2.0.1 client) - 30 minutes
> 3) executed from another colleague's computer
> (9.2.0.6 client) - 30
> minutes
> 4) executed using JDBC on all computers
> including mine - 30 minutes
>
> All statements are executed againts the very
> same database, with no
> changed parameters for actual session, using
> same user and same
> TNSNAMES.ORA section.
> I have tried to use event 10053 to evaluate
> execution plans (they are
> my primary concern because of nature of the
> problem), but it looks like
> event 10053 is not supporting OpenGateway
> connections to non-Oracle
> databases. The same applies for SQL_TRACE
> facility.
> I also tried event 10053 with a local select
> just to get all CBO
> related parameters. I was successful, but they
> are obviously identical.
> Using explain plan I got two variants, a quick
> one from my computer
> (though not very describing):
>
> --------------------------------------------------------------------
> | Id | Operation | Name |
> Rows | Bytes | Cost |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> | | |
> | 1 | REMOTE | |
> | | |
> --------------------------------------------------------------------
>
>
> and the slow one from my colleague:
>
> --------------------------------------------------------------------
> | Id | Operation | Name |
> Rows | Bytes | Cost |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> | | |
> | 1 | MERGE JOIN | |
> | | |
> | 2 | SORT JOIN | |
> | | |
> | 3 | MERGE JOIN | |
> | | |
> | 4 | SORT JOIN | |
> | | |
> | 5 | MERGE JOIN | |
> | | |
> | 6 | MERGE JOIN | |
> | | |
> | 7 | SORT JOIN | |
> | | |
> | 8 | MERGE JOIN | |
> | | |
> | 9 | SORT JOIN | |
> | | |
> | 10 | REMOTE | |
> | | |
> | 11 | SORT JOIN | |
> | | |
> | 12 | REMOTE | |
> | | |
> | 13 | SORT JOIN | |
> | | |
> | 14 | REMOTE | |
> | | |
> | 15 | SORT JOIN | |
> | | |
> | 16 | REMOTE | |
> | | |
> | 17 | SORT JOIN | |
> | | |
> | 18 | REMOTE | |
> | | |
> | 19 | SORT JOIN | |
> | | |
> | 20 | REMOTE | |
> | | |
> --------------------------------------------------------------------
>
> My not very well-founded theory is that the slow
> variant is using
> Oracle database as a driving site (more
> information from explain plan),
> while the fast one is using Teradata (less
> explain information, better
> performance because all data used for query are
> there), but I can't
> prove this theory and we were unable to tune
> statement using
> DRIVING_SITE hint (we even do not know if it is
> supported for
> OpenGateway connections). Does anyone has some
> idea what can be causing
> this behaviour.
>
> Thank you
> --
> Dusan Bolek
>
Received on Wed Feb 01 2006 - 14:57:04 CST

Original text of this message

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