ORA-0113 Generated by Specific Query through DB-Link
riga...@gmail.com
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
DA Morgan
> 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:
An ORDER BY returning one row? Why?
In what version?
And what do you mean by "the external database?" Are you talking about
across a DB Link?
Assuming Oracle 9i or above run an explain plan using DBMS_XPLAN so we
can see what is actually happening. The script you are using is
obsolete (www.psoug.org/reference/explain_plan.html).
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Mladen Gogala
> 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.
You should look into the alert log and look for ora-600 or
ora-7445. When done, report the problem to Oracle support via
Metalink.
Charles Hooper
> 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:
>
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.
Jonathan Lewis
Jonathan Lewis
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
Mladen Gogala
> we get the ORA-0113 End-of-file on
> communication channel error immediately after opening the
> ADODB.RecordSet.
That would be ORA-03113. It means that the server process has died,
usually because of ORA-0600 or ORA-07445 errors. ORA-0113 is an Oracle*NET
configuration error which says the following:
$ oerr ora 113
00113, 00000, "protocol name %s is too long"
// *Cause: A protocol name specified in the DISPATCHERS system parameter
// is too long.
// *Action: Use a valid protocol name for the DISPATCHERS value.
I doubt you can get that error from ADODB.
--
http://www.mladen-gogala.com
Tony
Thought I posted a reply to this thread earlier in the week, but I
don't see it.
First - thank you everyone for the suggestions. I learned a bit more
today. Here are the results for reference...
It turns out re-arranging the query and using the /*+ ordered */ hint
solves my original performance problem (which is definitely preferable
to building the IN() clause on the fly!).
So - the following works in about 350 msec:
SELECT /*+ ordered */ bv.*
FROM (SELECT linkid
FROM PROJECTGAS_MAIN_LINKS
WHERE projectid = 116448
AND blocklist = 'N') lk,
gas_mains_mgcbaseview bv
WHERE lk.linkid = bv.objectid
ORDER BY bv.physical_length DESC
Leaving the hint off takes 12 seconds.
Case closed!
Charles Hooper
That is great that you were able to solve the performance problem.
Just one word of warning: consider adding DISTINCT between SELECT &
linkid, as you saw in the posts by Jonathan Lewis and me. Without
DISTINCT, if there are two rows in the PROJECTGAS_MAIN_LINKS table/
view with the same linkid that also have projectid = 116448 and
blocklist = 'N', the query will return twice as many rows as expected,
which means that it would not be equivalent to your original query.