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
>
> > when running the following query it is taking forever I do an explain
> > plan and see that it should be using an index, but when I look in Grid
> > Control the session running the query is incuring a large amount of I/
> > O and when I look at the sql activity and plan within GC it shows it
> > doing a full table scan.
>
> > Here's the xplan from sqlplus:
>
> > SQL >explain plan for
> > 2 SELECT "QUOTENO" ,"QTY" ,"PRICE" ,"STD_COST" FROM
> > "QMS"."QTE_LINE" WHERE ("QUOTENO" = :1);
>
> > Explained.
> > SQL >SELECT * FROM TABLE(dbms_xplan.display);
> > Plan hash value: 2685032388
>
> > ---------------------------------------------------------------------------ญญ----------------
> > | Id | Operation | Name | Rows | Bytes |
> > Cost (%CPU)| Time |
> > ---------------------------------------------------------------------------ญญ----------------
> > | 0 | SELECT STATEMENT | | 53 | 954 |
> > 25 (0)| 00:00:01 |
> > | 1 | TABLE ACCESS BY INDEX ROWID| QTE_LINE | 53 | 954 |
> > 25 (0)| 00:00:01 |
> > |* 2 | INDEX RANGE SCAN | QTE_LINE_U1 | 54 |
> > | 3 (0)| 00:00:01 |
> > ---------------------------------------------------------------------------ญญ----------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> > 2 - access("QUOTENO"=TO_NUMBER(:1))
>
> > 14 rows selected.
>
> > 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.- Hide quoted text -
>
> - Show quoted text -
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?
Received on Wed Jul 11 2007 - 14:37:52 CDT
