Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: query doesn't execute as expected

Re: query doesn't execute as expected

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 11 Jul 2007 12:15:22 -0700
Message-ID: <1184181322.146132.317150@22g2000hsm.googlegroups.com>


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.html "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. Received on Wed Jul 11 2007 - 14:15:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US