Re: script to see explain plan
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 6 Oct 2006 07:29:31 +0100
The demo was probably the other way round - index a
character column and supply a numeric bind variable and
Oracle has to coerce from character to number, which
makes the index irrelevant.
GTTs - 10g style - add the problem that the default for
optimizer_dynamic_sampling is 2, which means you
sample tables without statistics when optimizing: so
the GTT stats at explain plan time are not the same as
the GTT stats at runtime.
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
----- Original Message -----
From: <ryan_gaffuri@xxxxxxxxxxx>
To: <cary.millsap@xxxxxxxxxx>; <wojciech.skrzynecki@xxxxxxxxx>; "oracle-l"
<oracle-l@xxxxxxxxxxxxx>
Cc: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
Sent: Friday, October 06, 2006 4:44 AM
Subject: RE: script to see explain plan
We have been running into repeated cases when we join a regular table to a GTT
where we get a different explain plan than we get an actual plan in a trace
file.
I believe Cary showed a case 1-2 years ago where if you have a column that has
an index on a number field and you pass it a varchar in a bind variable you can
get the wrong plan.
How often do people see inaccuracies in the explain plan?
-------------- Original message --------------
From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
One detail that may seem subtle, but it's important. Notice that EXPLAIN
PLAN doesn't show an execution plan, it shows a *predicted* execution
plan. Contrast this to the notion that there's a real execution plan in
your trace data (if you've activated it) or in your V$ fixed views, but
only after having executed the query in question.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba
Hotsos Symposium 2007 / March 4-8 / Dallas
Visit www.hotsos.com for curriculum and schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wojciech Skrzynecki
Sent: Thursday, October 05, 2006 4:04 PM
To: oracle-l
Subject: script to see explain plan
Hello Everybody
I would like ask you to about explain plan. I am looking for the best
script to see explain plan. I know that it is possible to see explain
plan for active session of other users. I use script from metalink
Note:260942.1. Maybe you use better scripts to see explain plan.
--
Wojciech Skrzynecki
Database Administrator
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
