Re: script to see explain plan

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_at_comcast.net> To: <cary.millsap_at_hotsos.com>; <wojciech.skrzynecki_at_gmail.com>; "oracle-l" <oracle-l_at_freelists.org> Cc: "Cary Millsap" <cary.millsap_at_hotsos.com> 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_at_hotsos.com>

> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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