Transparent Gateway - performance problem

From: <bdg_at_hotmail.com>
Date: Fri, 29 Jan 1999 20:52:09 GMT
Message-ID: <78t71l$6qc$1@nnrp1.dejanews.com>

Our installation of the Oracle Transparent Gateway for Microsoft SQL Server is having performance problems. Our tests indicate that the problems occur whenever an Oracle built-in function (ex USER or SYSDATE) is used in a query of SQL Server data, or there is a heterogenous join made.

Oracle Server 7.3.4 (Sun Solaris 2.6)
Microsoft SQL Server 6.5 (Windows NT 4.0) Oracle Transparent Gateway for Microsoft SQL Server 4.0.0.2.0

In the following example, the query returns in ~1 second if the select clause contains a literal value or a column from the SQL Server database, but does not return (>45 minutes) if the select clause contains the Oracle function SYSDATE. The coverage and vt_state tables physically exist on the SQL Server database.

Explain plan analysis indicates that both queries are using the same execution strategy.

Any pointers appreciated,
Brian Gastineau

SQLWKS> select to_char(sysdate,'YYYYMMDD HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:SS')

---

19990126 15:17:13
1 row selected.

SQLWKS>
SQLWKS> select 1
     2> FROM vt_state_at_msql vt_state
     3>    , coverage_at_msql coverage
     4> where vt_state."state_cd" = coverage."cov_st" and
     5>       coverage."sys_assign_nbr_id" = '10';

1

---

         1
         1
         1
         1

4 rows selected.
SQLWKS>
SQLWKS> select to_char(sysdate,'YYYYMMDD HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:SS')

---

19990126 15:17:14
1 row selected.

SQLWKS>
SQLWKS> select sysdate
     2> FROM vt_state_at_msql vt_state
     3>    , coverage_at_msql coverage
     4> where vt_state."state_cd" = coverage."cov_st" and
     5>       coverage."sys_assign_nbr_id" = '10';

SYSDATE

---

ORA-00028: your session has been killed SQLWKS>

---

The following is from another session which killed the first. The date is fairly accurate (and indicates I waited ~45 minutes).

---

SVRMGR> alter system kill session '16,2057'; Statement processed.
SVRMGR> select to_char(sysdate,'YYYYMMDD HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:SS')

---

19990126 16:03:22
1 row selected.

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Jan 29 1999 - 14:52:09 CST