Query performance dramatically different

From: Dusan Bolek <spambin_at_seznam.cz>
Date: 31 Jan 2006 23:58:39 -0800
Message-ID: <1138780719.320158.97750@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 - 01:58:39 CST