Index not being used
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-lReceived on Tue Apr 20 2010 - 04:49:22 CDT