Re: Different explain plans for a query on SQL Plus and PL/SQL

barraboombarrabin wrote:
> Hi,
> I have a couple of queries where there explain plan that I get from
> TOAD or SQL Plus is different from the explain plan that is being used
> when the same select statement is executed from PL/SQL package (which I
> determined by looking at V$SQL_PLAN table). The table is truncated and
> loaded with data every day and during the data load process the indexes
> are dropped and recreated after the data load. After the index creation
> is done, the table in question is also analyzed using dbms_stats.
> I would like to use a hint only as a last resort.
> I would appreciate it if someone can help me with some steps that can
> be taken to ensure that the explain plan within the PL/SQL is the
> desired one without using the hint.
>
> Note that the index SCP_STORE_ITEM_MD_NU_IDX01 has the same columns
> that are in the where clause of the select statement.
>
> Plan from TOAD
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 897 755
>
> TABLE ACCESS BY LOCAL INDEX
> ROWID EBIPROD.SCP_ODS_STORE_ITEM_MODELS 897 38 K 755
> 3 3
> INDEX RANGE SCAN EBIPROD.SCP_STORE_ITEM_MD_NU_IDX01 897 4
> 3 3
>
> Plan from V$SQL_PLAN
> -----------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes | Cost | Pstart| Pstop |
> -----------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | |
> | 59 | | |
> | 1 | PARTITION RANGE SINGLE| | |
> | | KEY | KEY |
> | 2 | TABLE ACCESS FULL | SCP_ODS_STORE_ITEM_MODELS | 491 |
> 25532 | 59 | KEY | KEY |
> -----------------------------------------------------------------------------------------------------
>
> Any help would be appreciated
>
> Thanks
>

What is the SQL statement? What is the Oracle version?

Neither of the above is what I would call an EXPLAIN PLAN. Run using DBMS_XPLAN.DISPLAY and DBMS_XPLAN.DISPLAY_CURSOR if in 10g so we can see more of what is happening.

Does the schema that owns the table own the index?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org