Re: Mysterious FILTER operation ;)

  • From: J.Velikanovs@xxxxxxxx
  • To: Edgar Chupit <chupit@xxxxxxxxx>
  • Date: Tue, 4 Jan 2005 15:21:54 +0200

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.

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

Jurijs
+371 9268222 (+2 GMT)
============================================
Thank you for teaching me.
http://otn.oracle.com/ocm/jvelikanovs.html






Edgar Chupit <chupit@xxxxxxxxx>
03.01.2005 23:26
Please respond to Edgar Chupit
 
        To:     J.Velikanovs@xxxxxxxx
        cc:     oracle-l@xxxxxxxxxxxxx
        Subject:        Re: Mysterious FILTER operation ;)


If your database is like mine and have default block size 8K, than
TESTRS table will consume 1188 blocks and index will consume 1114
blocks. In first case explain plan simply lies and in reality you have
FULL TABLE SCAN + FILTER (cr close to number of blocks in table), in
second case you have RANGE SCAN + TABLE ACCESS BY ROWID (cr close to
number of blocks in table + number of blocks in index).

I think that moral of this story is that you should always check
SQL_TRACE trace files for real execution plans.

On Mon, 3 Jan 2005 20:19:16 +0200, J.Velikanovs@xxxxxxxx
<J.Velikanovs@xxxxxxxx> wrote:
> Win2000 9.2.0.6 (tested on 9.2.0.4/SPARC Solaris as well)
> FULL TEST text see at the end of letter.
> I just trying to understand what FILTER operation doing in case 
described
> below.
> I have ran the same SQL two times (with and without stats, CBO/RBO)
> 
> There is index range scan caused by "where n between :v_p1 and :v_p2;"
> predicate usage.
> Only difference between two runs is additional FILTER operation added by
> CBO.
> 
> What mysterious for me is why operation added by CBO reduce LIO as well 
as
> execution time.

-- 
  Edgar



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

Other related posts: