ORA-0113 Generated by Specific Query through DB-Link

From: <rigatony1_at_gmail.com>
Date: 25 May 2007 16:58:17 -0700
Message-ID: <1180137497.251795.214530@p77g2000hsh.googlegroups.com>

Hi All,

I'm hoping someone can shed some light on this. I've found various references to ORA-0113 problems, but nothing yet that seems to match what we're experiencing.

I have a stored procedure which builds some dynamic sql and then opens an output REF CURSOR using the SQL. The procedure is called from a good old VB6 application, via ADODB.

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     Rows    Bytes    Cost     Object
Node      In/Out   PStart   PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1
4

  SORT ORDER BY                   1          2 K
4
    NESTED LOOPS SEMI                      1          2 K
3
      REMOTE    .EXT_VIEW       1          2 K       2
EXTDB.US.OPVANTEK.COM     SERIAL
      TABLE ACCESS BY INDEX ROWID  MY_SCHEMA.LINKS_TABLE
1          13
1
        INDEX UNIQUE SCAN        OPDSCP.PK_PGM

1
0

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:

SELECT bv.* FROM MY_VIEW bv
WHERE bv.objectid in (1007244)
AND bv.GISDATASETNAME = XXX' ORDER BY bv.physical_length DESC

it takes 15 mecs to return the same row (vs. 12 seconds above)!!

The explain plan for this query is:

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
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).

But, it leads to my second problem. If I use the comma-separated IN() clause and call this from VB6, we get the ORA-0113 End-of-file on communication channel error immediately after opening the ADODB.RecordSet. e.g., myrst.RecordCount returns that error. If I change back to the original sub-select in the IN() clause, then call it from VB6, it works fine, but takes 12 seconds.

I'm using a client side cursor from VB6. OpenStatic, LockReadOnly. I've tried a few other CursorTypes/LockTypes etc in VB6 to no avail. If someone thinks that's the area to focus, I'll post more details on what I've tried.

If I change to select directly from a table in the EXTDB, I get better performance, but I still get the ORA-0113 error. And it's not really an option for our application to select directly from the table. I need to use the view.

The one thing I've focused on is the different explain plans above, and particularly the "REMOTE" operation in the first plan. That plan is slow, but it works. The other plan is fast, but fails when the record set is returned to VB6.

I'll pause at this point. Anyone have any suggestions or additional questions about what I'm seeing?

Thanks in advance!
Tony Received on Fri May 25 2007 - 18:58:17 CDT