OraFAQ Forum: Performance Tuning » index ignored problem

Home » RDBMS Server » Performance Tuning » index ignored problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

index ignored problem [message #135768]

Fri, 02 September 2005 08:17

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

I have the strangest thing happening in one of my Oracle 10.1.0.4, Aix5.2 servers. Whe the app is running all queries make full table scan although the statistics has been just run and there are plenty of records (over 30,000) in almost each table, and good indexing for all of them. I run the 1046 trace on the application session and I can see the extensive waits for each quesry due to full table scans. I tried to troubleshoot the situation and start tracing each query alone with execution plan in SQLPlus and TOAD. No matter which quesry I run in both, I get different exec plan and all the time using the proper indexing - of course, the time is 100 time less. So, when the app runs it, it doesn't use the index, when I run it from either TOAD/SQLPLUS it does...
What could be the problem and how I'm suppose to troubleshoot this situation?
Thanks a lot for the help. mj

Report message to a moderator

Re: index ignored problem [message #135783 is a reply to message #135768]

Fri, 02 September 2005 09:19

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

What is your app? A bunch of plsql packages? Are they dynamically generated queries or static?

I'm not sure where to go on this, how to help, other than to try to find the difference between your app and running the queries directly. Are the session values different?

Report message to a moderator

Re: index ignored problem [message #135822 is a reply to message #135783]

Fri, 02 September 2005 13:07

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

No difference.
Stopped and restart the instance. generate statistics again. Now the rows are about 2mln.
The app scans is all, the same statement runs without tables scan - uses index scan fro SQLPLUs and runs for mlnsec.
I have a trace from the app with all the timing...
Is it possible some of the Oracle params to be change without me knowing about?
What params I need to look at so I'll be sure that somebody did not change something unappropriately?
Thanks,
mj

Report message to a moderator

Re: index ignored problem [message #135824 is a reply to message #135783]

Fri, 02 September 2005 13:46

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

Runnig the same app on another Oracle server - runs like charm!
So, obviously somebody changed something on the box...
What params I could check to figure out?
Thanks a lot for the help,mj

Report message to a moderator

Re: index ignored problem [message #135825 is a reply to message #135768]

Fri, 02 September 2005 13:55

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

When you said it ran faster when you ran the same thing in sqlplus, did you run it in sqlplus on the same server/database that the application uses? From your last post I'm guessing no?

If you have two different databases, could be any number of things. Compare the pfiles from both. Make sure both have complete statistics. Heck, make sure both actually have indexes. You are now in a "compare the two databases" mode.

Report message to a moderator

Re: index ignored problem [message #135829 is a reply to message #135825]

Fri, 02 September 2005 14:29

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

Both DBs are absolutely the same - hardware, Oracle, application running, indexing statistics run. I made a fail copy form the bad server and run statistics. The number of records...
the same app running on the same machine... Did compare the 2 DBs - same.
still doing the same... On one of the machines I have index usage from client tools and full table scans when the statememtns is issued from the app. On the other all uses indexes.
It should be a changes in the init params...
Comparing them now
Thanks,mj

Report message to a moderator

Previous Topic:	Using Hint
Next Topic:	How I can remove the table from cache? Which command I should execute

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 15:41:21 CDT 2025