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.
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
