| not using index from application [message #139864] |
Thu, 29 September 2005 19:20  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have a very strange situation - since this morning one of my ORAcle 10.1.0.4 servers refuses to use the right indexes. When run from the application It picks up the wrong one every time - for a couple of queries, not only one... If I run the same statements from SQLPlus or Toad, then the execution plan show and it uses the correct indexes.
How can I troubleshoot this situation?
Example:
SELECT ENTITY_ID, NUM_HASH, NUM_ID, DSRC_ACCT_ID,
VALID_FROM_DT, VALID_THRU_DT, NUM_LOCATION
FROM NUMS
WHERE NUM_HASH = :1 AND NUM_TYPE_ID = :2
AND ENTITY_ID != :3
AND SYS_DELETE_DT IS NULL ;
It usually uses index on NUM_HASH which is with very low cardinality!
Today it start choosing the index on num_type and entity_id...
No need to say that I run statistics several times...
Thanks for the help.mj
|
|
|
|
| Re: not using index from application [message #139869 is a reply to message #139864] |
Thu, 29 September 2005 21:03  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
Did the data in the tables change such that the cbo thinks it is now better to use the new index rather than the old? What makes the new index the bad one and the old index the good one? I take it you checked the status of the old index?
|
|
|
|
| Re: not using index from application [message #140017 is a reply to message #139869] |
Fri, 30 September 2005 08:47 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Both indexes are the same - in the gather_stats - the same...
The change was suddenly - just switch. The last stats were 18 h ago. The new stats did noithing - the same. I even tried to run an old application executable... new app using the same DB - the same app was running in the same time on the other DB server using the correct indexes - when I moved it to this server started to use the wrong indexes as the normal app.
Obviouslt, the problem was in the DB... I kind of cannot explain... I'll try to rebuild all indexes today, and may be to move the tables (the same tablespace). There was some data inserted in the time of the change of the exec plan, but usually I insert 40mln records for this time - now was like 400,000 - could not be the data...
Let me see is rebuilding will do.
Thanks a lot,mj
|
|
|
|
| Re: not using index from application [message #140158 is a reply to message #139864] |
Sat, 01 October 2005 09:24 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Issue resolved. The problem is old/some missing system statistics. After I re-run them, with no other changes, I got the server up and running as before - all plans and index usage was right.
Thanks a lot for the help.mj
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
