query doesn't execute as expected

From: Ben <balvey_at_comcast.net>
Date: Wed, 11 Jul 2007 11:58:58 -0700
Message-ID: <1184180338.284435.261940@n60g2000hse.googlegroups.com>

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