Re: Mysterious FILTER operation ;)

From: <J.Velikanovs_at_alise.lv>
Date: Tue, 4 Jan 2005 15:21:54 +0200
Message-ID: <OFFF07024F.AB83FA21-ONC2256F7F.00489DA1-C2256F7F.0049BF6A@alise.lv>

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_at_gmail.com>
03.01.2005 23:26
Please respond to Edgar Chupit

        To:     J.Velikanovs_at_alise.lv
        cc:     oracle-l_at_freelists.org
        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_at_alise.lv <J.Velikanovs_at_alise.lv> 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



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

Received on Tue Jan 04 2005 - 07:23:57 CST