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

From: barraboombarrabin <barraboombarrabing_at_yahoo.com>
Date: 3 Nov 2006 06:30:57 -0800
Message-ID: <1162564256.871352.301270@b28g2000cwb.googlegroups.com>

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 Received on Fri Nov 03 2006 - 08:30:57 CST