Fw: RE: explain plan and the real execution plan

So others can correct me if I'm missing something here-  I do love dynamic 
sampling, (NOT!)

--- On Tue, 11/17/09, Kellyn Pedersen <kjped1313@xxxxxxxxx> wrote:


From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
Subject: RE: explain plan and the real execution plan
To: lzeng@xxxxxxxxxxxxx
Date: Tuesday, November 17, 2009, 9:30 AM







It could-  If the column data was askew across the columns enough for different 
samples to cause different execution plans and not statistics on the table and 
the query was using literals to the point to cause the CBO to take a dynamic 
sample each time.  I have seen this, but I've also fixed the stats and made the 
developers fix the code...:)
 
If you seeing this, you could create a SQL Profile, provide hints in the 
statement involved, lock down the stats or disable dynamic sampling on the 
database if you NEED a more stable execution plan, 
(OPTIMIZER_DYNAMIC_SAMPLING=0).  I've done this at a previous company where it 
involved a web property that NEEDED to stay very static with it's performance.  
We were about 99% successful as there are a number of parameters involved in 
the CBO doing it's job of finding better execution plans and rewriting 
execution plans and queries... :)


Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
 
"Go away before I replace you with a very small and efficient shell script..."

--- On Mon, 11/16/09, Lei Zeng <lzeng@xxxxxxxxxxxxx> wrote:


From: Lei Zeng <lzeng@xxxxxxxxxxxxx>
Subject: RE: explain plan and the real execution plan
To: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>, vishal@xxxxxxxxxxxxxxx, "Oracle - 
L" <oracle-l@xxxxxxxxxxxxx>
Date: Monday, November 16, 2009, 10:45 PM








Would dynamic sampling generates different stats each time if the underlying 
table stays static ( no ddl or dml change, no new indexes, ... nothing changes 
) ?
 
Thanks,
Lei 



From: Kellyn Pedersen [mailto:kjped1313@xxxxxxxxx] 
Sent: Monday, November 16, 2009 7:41 PM
To: vishal@xxxxxxxxxxxxxxx; Oracle - L; Lei Zeng
Subject: RE: explain plan and the real execution plan 
  





Yes-  After your explain plan and then upon executing the statement, dynamic 
sampling could easily occur and calculate a better execution plan, utilizing it 
for the actual execution than it did for the explain plan.

A quick trace of the session would capture the statement, (You are looking for 
OPT_DYN statement for one or more tables involved..) or if unable to reproduce, 
you could look in the SQL history, either through AWR or through OEM.

 

Hope this helps,

Kellyn

Kellyn Pedersen

Multi-Platform DBA

I-Behavior Inc.

http://www.linkedin.com/in/kellynpedersen

 

"Go away before I replace you with a very small and efficient shell script..."


--- On Mon, 11/16/09, Lei Zeng <lzeng@xxxxxxxxxxxxx> wrote: 

From: Lei Zeng <lzeng@xxxxxxxxxxxxx>
Subject: RE: explain plan and the real execution plan
To: vishal@xxxxxxxxxxxxxxx, "Oracle - L" <oracle-l@xxxxxxxxxxxxx>
Date: Monday, November 16, 2009, 4:47 PM 


Suppose I am using the same session to run both. Could it be different?
Thanks, 
Lei 
  




From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Vishal Gupta
Sent: Monday, November 16, 2009 3:39 PM
To: Oracle - L
Subject: Fwd: explain plan and the real execution plan
  

 



Changes in explain plan and real execution plan depends on the env differences 
between the sessions in which you are explain planning and the one in which you 
are executing the statement.  Sometimes app changes session level parameters at 
login time which can change execution plan. 

Regards,

Vishal Gupta


On 16 Nov 2009, at 23:28, "Lei Zeng" <lzeng@xxxxxxxxxxxxx> wrote:



Hi, all:
I have a question about the explain plan. 
 
Under what conditions, could the plan I get out of ‘explain plan’ differ from 
what I get from running the sql statement? 
  
Suppose the database has no change during I ran ‘explain plan’ and run the sql 
– I mean, no ddl and dml – You would think this database is static. Or the time 
gap between I run ‘explain plan’ and run sql statement is too narrow to allow 
any change (ideal environment). No bind variable peeking because 
_optim_peek_user_binds is set to FALSE. 
  
Any idea? Thanks in advance. 

Lei


 
 

Other related posts: