Index not being used

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Tue, 20 Apr 2010 02:49:22 -0700
Message-ID: <u2sa2b1e7611004200249o706a0dc1l1289f4510ecccc54_at_mail.gmail.com>



Hi
Database 1 is on 11.1.0.7.2
SQL> explain plan for
  2 SELECT *
  3 FROM pa_expenditure_items_all
  4 WHERE system_linkage_function = 'VI' AND cost_distributed_flag = 'N'; Explained.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT

Plan hash value: 997445706

| Id  | Operation         | Name                     | Rows  | Bytes | Cost
(%CP
U)| Time     |

--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT


|   0 | SELECT STATEMENT  |                          | 47861 |    12M|
679K (
3)| 02:15:51 |
|* 1 | TABLE ACCESS FULL| PA_EXPENDITURE_ITEMS_ALL | 47861 | 12M| 679K (
3)| 02:15:51 |

Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT



   1 - filter("SYSTEM_LINKAGE_FUNCTION"='VI' AND "COST_DISTRIBUTED_FLAG"='N')



Database 2 is on 11.1.0.7.1 and
QL> explain plan
  2 for
  3 SELECT *
  4 FROM pa_expenditure_items_all
  5 WHERE system_linkage_function = 'VI' AND cost_distributed_flag = 'N'; Explained.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT

Plan hash value: 1598013654

| Id  | Operation                   | Name                     | Rows  |
Bytes |
 Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
------------------------

PLAN_TABLE_OUTPUT


|   0 | SELECT STATEMENT            |                          |    13 |
3588 |
     7   (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| PA_EXPENDITURE_ITEMS_ALL | 13 | 3588 |
     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PA_EXPENDITURE_ITEMS_N3  |    50
|       |
     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------
------------------------

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):


   1 - filter("SYSTEM_LINKAGE_FUNCTION"='VI')    2 - access("COST_DISTRIBUTED_FLAG"='N') 15 rows selected.

Before blaming it on PSU, I was thinking if there was anything else that I can do
1. Stats are most recent and representative on the involved 2. Checked distinct_keys, clustering_factor, num_rows between both databases and about the same.
3. Checked this
http://jonathanlewis.wordpress.com/2007/02/15/index-not-used-10g/ and it may be that I am hitting it b ut I am not able to validate it. 4. If I hint the 11.1.0.7.2 database to use the index hint, then the index gets used

I am going to switch Oracle Homes and try but just want to make sure that I there was anything else I could try

Thank you
Kumar

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 20 2010 - 04:49:22 CDT

Original text of this message