Re: Transparent Gateway - performance problem

A little more information . . .

From Transparent Gateway logs, it appears that the first query (select 1) is received by the TG in much the same format as it appears. The second query (select sysdate) is altered from its original format. The incoming and outgoing parses select only from the coverage table and do not use the key value (10). The coverage table has more than 3.5 million records. I assume that if this query ever completed, there would be a second associated query of the vt_state table.

It appears to be a problem with the Oracle optimizer not efficiently accessing the remote database. Any recommendations for correcting this.

Brian Gastineau

"select 1" Incoming Parse SELECT 1 FROM "VT_STATE" A2,"COVERAGE" A1 WHERE A2."state_cd"=A1."cov_st" AND A1."sys_assign_nbr_id"='10'

"select sysdate"
Incoming Parse SELECT "sys_assign_nbr_id","cov_st" FROM "COVERAGE"

In article <78t71l$6qc$1_at_nnrp1.dejanews.com>,   bdg_at_hotmail.com wrote:
> 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
>

...
> 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';
...
> 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';
...
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Feb 01 1999 - 09:16:19 CST