Re: Mysterious FILTER operation ;)

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: J.Velikanovs@xxxxxxxx
  • Date: Tue, 4 Jan 2005 15:28:45 +0000

On Tue, 4 Jan 2005 15:21:54 +0200, J.Velikanovs@xxxxxxxx
<J.Velikanovs@xxxxxxxx> wrote:
> Thanks to all for responses, I was blind.
> >> I think that moral of this story is that you should always check
> >> SQL_TRACE trace files for real execution plans.
> Some times even SQL_TRACE will no show explain plan.
> For example long job, which runs 5 hours.
> If I switch 10046 event for this job, and trace it for 5-10-30-60 min and
> job not close cursor during this time, then there is no explain plan in
> trace file.


In addition, I *currently believe* that at least in recent versions of
9.2 and 10 you may not get STAT lines even if the cursor is closed by
the app under certain circumstances. I do not yet know what those
circumstances might be - or if I am just wrong about the underlying
session closing cursors. I suspect one or more of

obviously with well written applications we won't be setting these,
but equally with well written apps we probably won't be doing 10046

> The only opportunity to see real explain plan is to check V$SQL_PLAN, by
> my opinion. Or I am wrong?

A problem in 8i, and in 10 you have DBA_HIST_SQL_PLAN for historical
plans. The latter is in principle great for folks like me who have
long suggested that a change in execution plan is worth investigating
secure in the knowledge that determining whether a plan has changed or
not is somewhat difficult in earlier versions...

Niall Litchfield
Oracle DBA

Other related posts: