RE: explain plan and the real execution plan
- From: "Lei Zeng" <lzeng@xxxxxxxxxxxxx>
- To: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>, <vishal@xxxxxxxxxxxxxxx>, "Oracle - L" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Nov 2009 21:45:40 -0800
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" <
<http://us.mc320.mail.yahoo.com/mc/compose?to=lzeng@xxxxxxxxxxxxx>
lzeng@xxxxxxxxxxxxx
<http://us.mc320.mail.yahoo.com/mc/compose?to=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:
