SQL – Reformatting to Improve Performance 3

4 12 2009

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

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:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/8661f136bdaefae4

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?

——————————–


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: