Index not being used
- From: Kumar Madduri <ksmadduri@xxxxxxxxx>
- To: oracle Freelists <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 20 Apr 2010 02:49:22 -0700
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
Other related posts:
