Re: Mysterious FILTER operation ;)

On Tue, 4 Jan 2005 15:21:54 +0200, J.Velikanovs_at_alise.lv <J.Velikanovs_at_alise.lv> 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.

Correct.

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 CURSOR_SHARING=FORCE|SIMILAR and SESSION_CACHED_CURSORS; CURSOR_SPACE_FOR_TIME obviously with well written applications we won't be setting these, but equally with well written apps we probably won't be doing 10046 traces...

>
> 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
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l