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

<rigatony1_at_gmail.com> wrote in message
news:1180137497.251795.214530_at_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
>

It's a little hard to see from your text, but I think the optimizer is having a problem with its choice of unnesting, complex view merging, and pushing predicates.

The following is effectively what you did in the tests where you ran the inner query and used the result set (of one row) to drive the outer query. In principle the optimizer can do automatically if the arithmetic works out - and if certain limiting features don't get in the way..

select

        bv.*
from

        (
            select    distinct link_id
            from     links_table
            where
                        projectid = 116448
            and       blocklist = 'N'
        )                       lk,
        my_view           bv
where
            bv.object_id = lk.linkid
and       bv.gisdatasetname = 'XXX'
order by
            bv.physical_length desc

There are various reasons why the optimizer might work out the wrong plan for this query, but the one you want to see involves:

        no_merge on the lk view (and the bv view,
        but that's happening anyway, I think)

        nested_loop join from lk to bv using
        the indexed path on object_id

        predicate pushing (if necessary) to make
        the nested loop happen

If this plan doesn't appear automatically, then try adding the following hint set to the query:

    /*+ no_merge(lk) no_merge(bv) ordered use_nl(bv) push_pred(bv) */

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html