On Jul 11, 4:12 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jul 11, 3:37 pm, Ben <bal..._at_comcast.net> wrote:
>
>
>
>
>
> > On Jul 11, 3:15 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > On Jul 11, 2:58 pm, Ben <bal..._at_comcast.net> wrote:
> > > > 10.2.0.2 Ent Ed AIX5L
> > > > Not real sure how to give an example of what I'm seeing in the Grid
> > > > Control, you'll just have to trust me that it is showing a fts and the
> > > > to_number(:1) doesn't show in the plan either.
> > > > I performed the explain plan as system, the user that typically runs
> > > > the query is different.
> > > > Please bare with me, as I just upgraded to 10.2.0.2 and have yet to
> > > > learn all the tricks of the trade with the new release.
>
> > > Google search:
> > > explain plan bind kyte
>
> > > First hit is this page:http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite....
> > > "The last bit about explain plan I'll look at is the fact that explain
> > > plan doesn't see your bind datatype. It presumes all binds are
> > > varchar2's regardless of how the developer is binding."
>
> > > The above seems to indicate that bind variables and explain plans do
> > > not mix.
>
> > > You might try using v$sql_plan to see the execution plan:
> > > http://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/
>
> > > You may be able to create an outline for the SQL statement to force it
> > > to use the index. Also, verify that statistics are up to date on the
> > > table and indexes. A histogram on the QUOTENO column may have
> > > convinced Oracle that a full tablescan would be more appropriate than
> > > an index lookup when the first bind variable value was passed in,
> > > while that was not the case with later bind variable values.
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc
>
> > I did notice on metalink bug? # 5946782, that mentions bind variable
> > datatypes not working in explain plans, I made a seperate post
> > addressing that on here, but no one replied. With that does that mean
> > that the advisor doesn't 'advise' correctly when it comes to bind
> > variables?
>
> Be careful using the Advisor - that is an additional cost item, even
> though it is installed by default, even on the Standard Edition - if
> you use it, you must buy it.
>
> More than likely, the Advisor is showing you the actual execution
> plan, which might be in one of the views shown by this SQL statement:
> SELECT
> OBJECT_NAME
> FROM
> DBA_OBJECTS
> WHERE
> OBJECT_NAME LIKE '%PLAN%'
> AND OWNER='SYS'
> ORDER BY
> OBJECT_NAME;
>
> Note: querying from the "wrong" view may require the purchase of the
> same extra cost license. You may want to check the definitions of
> those views before querying.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -
The same advisor that is setup to run every night by default??
Received on Wed Jul 11 2007 - 15:42:10 CDT
