Re: query doesn't execute as expected

On Jul 11, 4:42 pm, Ben <bal..._at_comcast.net> wrote:
> 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.
>
> The same advisor that is setup to run every night by default??

Google search:
  Oracle SQL tuning advisor license

Second hit:
  http://www.oracle.com/technology/products/oem/pdf/ds_db_tp.pdf

It you have not had a chance to log into Grid Control or Database Control as SYSMAN, you might want to do so in order to disable the unlicensed features.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Jul 11 2007 - 16:34:39 CDT