Re: ORA-0113 Generated by Specific Query through DB-Link

On May 25, 7:58 pm, rigato..._at_gmail.com wrote:
> 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:

If I read the SQL statement and plan correctly: 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?

> If I run the inner select above, it returns exactly one linkid
> (1007244) from LINKS_TABLE. If I execute the following query using
> that linkid:
>
> Operation Object Name Rows Bytes Cost Object
> Node In/Out PStart PStop
>
> SELECT STATEMENT REMOTE Optimizer Mode=ALL_ROWS
> 2
> 9
> SORT ORDER BY 2 4 K
> 9
> VIEW EXTDB.EXT_VIEW 2 4 K
> 8
> EXTDB.US.OPVANTEK.COM
> UNION-
> ALL
>
> FILTER
> NESTED LOOPS OUTER 1 158
> 4
> TABLE ACCESS BY INDEX ROWID EXTDB.EXT_TABLE
> 1 144 2
> EXTDB.US.OPVANTEK.COM
> INDEX UNIQUE SCAN EXTDB.EXT_TABLE_ROWID_UK
> 1 1
> EXTDB.US.OPVANTEK.COM
> TABLE ACCESS BY INDEX ROWID MGC.D97
> 1 14 2
> EXTDB.US.OPVANTEK.COM
> INDEX RANGE SCAN EXTDB.D97_IDX1
> 1 1
> EXTDB.US.OPVANTEK.COM
>
> FILTER
> NESTED LOOPS OUTER 1 165
> 4
> TABLE ACCESS BY INDEX ROWID EXTDB.A97
> 1 151 2
> EXTDB.US.OPVANTEK.COM
> INDEX RANGE SCAN EXTDB.A97_ROWID_IX1
> 1 1
> EXTDB.US.OPVANTEK.COM
> TABLE ACCESS BY INDEX ROWID EXTDB.D97
> 1 14 2
> EXTDB.US.OPVANTEK.COM
> INDEX RANGE SCAN EXTDB.D97_IDX1
> 1 1
> EXTDB.US.OPVANTEK.COM
Looks like there are a lot of steps involved in the plan to retrieve one record from the remote database.

> 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).
>
> Thanks in advance!
> Tony

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?

Sorry for ignoring the ORA-0113, but it appears based on what you wrote that if the original query executed faster, you would not need to use the other method that generates the error.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat May 26 2007 - 19:48:16 CDT