Thursday, March 6, 2008

Remote SQL Statements Tuning

If you have a remote sql statement and the execution elapsed time is not the expected, the plan on the remote site is not the desirable. Especially if you using hints to tune your query on the remote site , this may not work on the actual execution because of Oracle behavior.
E.g

If the entire SQL statement is sent to the remote database, the optimizer uses table aliases A1, A2, and so on, for all tables and columns in the query, in order to avoid possible naming conflicts. For example:

On remote site you tune the query as follows ans runs ok:

SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT a, EMP b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

And on your site you do

insert into table mytable
SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT@REMOTE a, EMP@REMOTE b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

The query is sent to the remote database as:

   SELECT /*+ index(a pk1) index(b pk2) */
A2.DNAME, A1.ENAME
FROM DEPT A2, EMP A1
WHERE A1.DEPTNO = A2.DEPTNO;

So the hint is not working.

Best way is to create a view on remote site with the tuned query
and then use the view in the local insert.

REMOTE SITE :
create or replace myview as
SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT a, EMP b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

LOCAL SITE :

insert into table mytable select * from myview@remote;





No comments:

Post a Comment