<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
>
Received on Sun May 27 2007 - 02:48:45 CDT
