December 4, 2009
Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.
Some time ago the following question appeared in a forum:
The query is against a view (MY_VIEW below) in the same schema where the procedure lives. The view selects from another view (EXT_VIEW below) in an external DB via a DBLINK.
Both servers are running Oracle 10g. The host server is Red Hat Linux. The external server is Win 2003. If exact versions become important, I can post them.
The query that works, but slowly, is:SELECT bv.* FROM MY_VIEW bv WHERE bv.objectid in (select linkid from LINKS_TABLE where projectid = 116448 AND blocklist = 'N') AND bv.GISDATASETNAME = 'XXX' ORDER BY bv.physical_length DESC
However, this takes 12 seconds to return exactly one row from the external database. Here’s the explain plan:Operation Object Name SELECT STATEMENT Optimizer Mode=ALL_ROWS SORT ORDER BY NESTED LOOPS SEMI REMOTE .EXT_VIEW EXTDB.US.OPVANTEK.COM TABLE ACCESS BY INDEX ROWID MY_SCHEMA.LINKS_TABLE INDEX UNIQUE SCAN OPDSCP.PK_PGM 1 0
Oracle appears to be using the results of the remote data to drive into the MY_SCHEMA.LINKS_TABLE table using an index on the LINKS_TABLE table. Thus, Oracle is retrieving all rows from the remote database where GISDATASETNAME = ‘XXX’ and probing the MY_SCHEMA.LINKS_TABLE for matching LINKID values. You might repeat your explain plan using DBMS_XPLAN with the ALLSTATS LAST parameters to see how it compares.
Is it possible to rewrite the SQL statement into a more efficient form?
First question – can any one tell me a way to force the first query above to use the faster explain plan? We know there will not be very many linkids returned by the inner query (less than 10).
Having given up on re-writing the first query, I then wrote some PL/SQL to loop over the inner query with a cursor and construct a comma separate list of all the linkids, which I can then use as the IN() clause of my dyanamic sql statement. That works and runs much faster from a SQL Editor window (we use TOAD).
Let’s take another look at rewriting the original query, something like this:
SELECT /*+ ORDERED */ BV.* FROM (SELECT DISTINCT LINKID FROM LINKS_TABLE WHERE PROJECTID = 116448 AND BLOCKLIST = 'N') L, MY_VIEW BV WHERE L.LINKID=BV.OBJECTID AND BV.GISDATASETNAME = 'XXX' ORDER BY BV.PHYSICAL_LENGTH DESC;
The above will instruct Oracle to retrieve the 10 or fewer rows from the LINK_TABLE, and use the results to drive into MY_VIEW, which points to the remote database.
How does a DBMS_XPLAN with the ALLSTATS LAST parameters for your original query compare with my rewrite above?