RE: explain plan and the real execution plan

From: Ojha, Ajeet (GE Healthcare, consultant) <"Ojha,>
Date: Mon, 23 Nov 2009 18:24:00 +0530
Message-ID: <A0B9A2F904FCDF43A5D1E286639257B6134B3C_at_BANMLVEM04.e2k.ad.ge.com>

There are quite few example on asktom as well as Tom kytes'blog posts.  

In one of his blogs he has given exact same answers which you want to understand.  

Regards  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder Sent: Friday, November 20, 2009 9:59 AM
To: Lei Zeng
Cc: ORACLE-L
Subject: Re: explain plan and the real execution plan  

Hi Lei,

Answers below          

1. The optimizer statistics

        I assume this database has no change - no ddl and dml. Under this situation, I would say the optimizer statistics would remain the same, correct?

Even if you have no DDL and no DML happening, someone may still have gathered optimizer statistics on the tables and changed them. Maybe the last time stats were gathered was months or years ago (in theory gathering stats should make things better, but in practice its not always so, especially if the new stats aren't collected appropriately).            

        2) The implicit data type conversion

        Would you provide a testing case on this? I tried some queries but I could not produce the effects.

I wrote an example in my blog:  

http://blog.tanelpoder.com/2009/11/17/explain-plan-for-command-may-show- you-the-wrong-execution-plan-part-1/

Tanel Poder
http://blog.tanelpoder.com    

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

Received on Mon Nov 23 2009 - 06:54:00 CST