@JOHN: For information.
See attached file. It contains an analysis text file, and a couple of
HTML files showing the code actually executed in your trace files, with
all the binds replaced by the actual text.
SQL Plus did a straight PARSE-EXEC-FETCH-CLOSE with a good plan. Toad
too executed the good plan, once, but on two other occasions, it
executed the bad plan. Toad seems to run a couple of extra PARSE-CLOSE
operations too. This seems a bit strange to me, maybe John knows why? It
might be so that Toad can determine type and size of the required bind
variables so that the user can be prompted for values at run time? (I'm
guessing, but that's what Perl and Java does.)
The text file basically walks through the trace files extracting the
relevant details. I can see why Toad's execution takes much longer,
there's a HASH JOIN with million plus blocks being read (from UNDO it
seems) which happens twice in the Toad trace, SQL Plus doesn't have this
operation. Mind you, the execution plan in Johann's original file, shows
that this step never actually took place! The trace file says otherwise.
I would love to say it's a bug, but I cannot think of any kind of bug
which would always run SQL Plus quickly and never Toad. Unless Oracle
are doing something underhand - which I'm not offering as a serious
The good plans read 8 rows from BATCHES using an index lookup, the bad
plan does a full scan of 13,478,003 rows. Given that the one, singular
bind variable is identical in each case, the optimiser should know to
use an index lookup!
Anyway, there's more in the analysis.txt file in the attached zip file.
I'm afraid I don't have a good reason for the problem.
@Johann, do you see this problem with other (simpler?) SQL statements on
the same database? How about on other databases?
Does SQL*Plus have any startup files that execute? perhaps changing the
optimiser environment, etc? How about Toad?
Can you tell I'm grasping at straws?
(Attachment TraceFiles.html.zip is missing)