Custom F0911 index not always being used by system

I added a custom index to our F0911 table to be used by a custom UBE that we created. I used OMW to create and promote the Index. The UBE without the index would take about 10 hours and with the index it takes about 5 minutes. The UBE is run about once a month by our finance group.
The problem is that when the UBE hasn't been run for for a couple of weeks when they run it, it doesn't use the index. To fix it we go into oracle and change the optimizer back and forth from CHOOSE to ALL_ROWS, and then back to CHOOSE while the UBE is running. After we do this the UBE starts flying again.
We have tried analyizing the database, recreating the indexes, etc.. However the only thing that seems to get the UBE going is changing the Optimizer settings.

Has anyone seen this before?

Thanks in advance for your help.

Andres Ortega
E1 8.10 XU1, Oracle 10g, on Intel/NT

>>Has anyone seen this before?<<

Not with the F0911 but with the F4111.

And I am not on Oracle but SQL Server.

I have a report that runs weekly against the F4111 and sometimes it flies and other times it drags. And this is just a day after an index defrag and and update of the statistics.

So when I get in on Monday morning and the job is still running from the night before I update the statistics and off it flies..

Don't think this is going to help but you're not alone...

Dave Schlieder

"I have a report that runs weekly against the F4111 and sometimes it flies and other times it drags"

I've found that a report will some times start with a select statement without any lock hints. If this initial "attemt" stalls the select will be resubmitted with a "WITH (NOLOCK)" modification. You can catch this with debugging on (be careful about switching debugging on on the fly (eg with Alex Pastuhov's OWDLC) as the debug output then doesn't show the Select's.

Have a look at the SQL Server locking performance when you are having poor performance. This is what led me to notice the "WITH (NOLOCK)" change when performance was better.

Regards
Edmund

NOTE TO Everyone - Per Oracle Documentation.... If you use a Not Equal in
the syntax of any SQL.... a Full Table Scan will be conducted over an Oracle
Database (grrrrrrrr)....

Just an FYI - something I found in one of their documents - that bugs the
BGezus out of me!

(db)




--