Re: different results in sql and plsql execution
- From: Maxim Demenko <mdemenko@xxxxxxxxx>
- To: Andrey.Kriushin@xxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 01 Aug 2006 19:50:53 +0200
Andrey Kriushin schrieb:
Hi, Maxim,
Have you noticed the difference in the execution plans? There is VIEW
step in PL/SQL. Don't know if this is anyhow related to the problem.
HTH
-- Andrey
Hi, Andrey,
yes , i've seen that. The wrong result itself isn't a big surprise, i've
often seen similar effects if optimizer choose wrong execution plan (
well, may be not too often, but function based index on null is a good
example). I'm pretty sure, this issue is due to different plans, but
this is the secondary thing - what drives me crazy - i can't understand
, why comes this plan instability. I had even a test instance with wrong
results, 3 days later the same query - correct results - restarted
instance - again wrong - inbetween - no user activity at all on the
database nor on the host ( except the scheduled statistics gathering ).
It goes better - i get different results at the same time in different
clients - correct on the host sqlplus , wrong on the windows client
sqlplus. There are no invalid objects, no invalid indexes, all is
wonderful, but yields wrong results. Sometimes.
I rewrote my query using UNDER_PATH() with level specified , so
eliminated need in DEPTH() function - in this case results are stable -
so my business need is resolved, but i still would like to know - what
are the reasons for optimizer to switch the plan ( and how it can be
avoided). I got actually update from Oracle Support ( this guy saw my
testcase in webconference ) - he couldn't reproduce it as well, but his
environment was as by Dimitre on Solaris. I think , this can be however
plattformdependent - for sure i could reproduce it on 10.2.0.1/10.2.0.2
on linux and for Oracle XE on linux and windows, 9.2.0.6/7 produces
always correct results ( in my tests ).
Best regards
Maxim
--
//www.freelists.org/webpage/oracle-l
Other related posts:
