Re: Can anybody explain this strange thing?

"vic" <v.tsien_at_comcast.net> wrote

> I used driving_site hint but forgot in my news group message. The explain
> with or without that hint is identical. However, the explain from server a
> (hp) is different from the explain on server b (sun).

Which explains why there is a difference in performance when running the SQL locally on the database (using SQL*Plus) and why it is so slow when pushing that SQL across a dblink from another database.

The fix for this problem is thus to get the complete SQL statement intact on the remote instance and have that instance determine the execution plan.

The DRIVING_SITE hint should do it - in the right circumstances. Which seems not to be case for your in this particular instance.

Two suggestions.

Change the SQL to an inline view. Something like:

SELECT /*+ DRIVING_SITE(a) */
  *
FROM (SELECT /*+ hint hint */ bluh FROM foo_at_dblink) a

Create the SQL as a view on the remote instance - include whatever hints you want to optimise performance on that instance.

Then use the view to select from via the dblink, again using the DRIVING_SITE hint. Something like:

SELECT /*+ DRIVING_SITE */
  *
FROM remoteview_at_dblink

--
Billy