Re: query doesn't execute as expected

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. Received on Wed Jul 11 2007 - 15:12:13 CDT