Re: different results in sql and plsql execution

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

--
http://www.freelists.org/webpage/oracle-l