ORA-0113 Generated by Specific Query through DB-Link

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

.

Relevant Pages

  • Re: ORA-0113 Generated by Specific Query through DB-Link
    ... good old VB6 application, via ADODB. ... The query that works, but slowly, is: ... The explain plan for this query is: ... INDEX RANGE SCAN EXTDB.A97_ROWID_IX1 ...
    (comp.databases.oracle.server)
  • Re: Weird behaviour on a RAC
    ... > query were running fine, all in a sudden we had several performance ... original plan lasts 50minutes index range scan on pk is 13 ... Forced the parameter in the session Oracle exposes ...
    (comp.databases.oracle.server)
  • Re: ORA-0113 Generated by Specific Query through DB-Link
    ... good old VB6 application, via ADODB. ... The query that works, but slowly, is: ... The explain plan for this query is: ... INDEX RANGE SCAN EXTDB.A97_ROWID_IX1 ...
    (comp.databases.oracle.server)
  • Re: EXPLAIN Plan
    ... In the EXPLAN PLAN there was an INDEX RANGE SCAN which the ... Am I missing something or does what the DBA not really make sense? ... The same plan is returned whether or not statistics were gathered (this ... The real question may be, does "help with the query" mean, "analyse all ...
    (comp.databases.oracle.server)
  • Re: Why is this
    ... would be to have a single index on (lastrecon, acctnum, deposit) [or ... cover the query and handle the restrictive condition immediately. ... there is no perfect query plan. ... use the index for TOP 1, even if it used quite a few bookmark lookups, ...
    (microsoft.public.sqlserver.server)