quote:

the numbers might appear to be relatively small (419 against possible 76) but I am guessing that this is a small piece of the overall puzzle and is combining with the other TABLE requests to give rise to FOCSORT problems.

Yes, the code isn't doing the aggregation in Oracle but there actually is no aggregation to do. We ended up having to group by the Respondent_ID which becomes 1-to-1 with each row in the table.

I may just have him change that query to PRINTS and then HOLD the data, and then add it up from there.

Bottom line is WHY DOES IT WORK and NOT WORK at the same time. Something is botching the data request either when it pulls from Oracle or when it stores it off in a HOLD file. In my opinion the same piece of code should either work or fail but not both. Both leads me to believe that our process is "functioning" but external factors are changing how it behaves. It is hard to tell you manager you want to rewrite something that is working part of the time without answers

(do I detect GUI vs. manual code undertones?)

What's the GUI?

Johnny5

I think the uppermost thing in everyones mind is to find what the problem is.

Tony A's come up with a good workaround but we should be able to get the product to perform reliably with your code.

I think the Dev studio painter (GUI) vs hand code is one that's settled already in that the GUI is not good enough yet to do everything but it is very useful for certain things.

One thing we do need to solve this is a comparison that shows what records are being excluded from wf and the common factor in the exclusion.

Over to you buddy...

First off I'd like to say thanks to all that have helped question and point me down different paths in an effort to try to fix this truly random issue.

Friday my success-to-failure ratio was about 25%/75%. Monday my ratio went to about 50/50. Tuesday seemed to work about 80/20. Today in production we have yet to be able to see the failure scenario. No code was changed or released back into production. No changes were made period.

The only thing that happened yesterday was a data load issue on a completely different database. I plan to interrogate our production support team to see if they ended up having to do anything with the WebFocus areas of the system (even something as simple as a machine reboot).

This problem has been one of the most frustrating because of the seemingly random nature at the core. My greatest fear at this time is that it was fixed as if by magic but if it ever reverts back I can honestly say I wouldn't have an ETA as to what/where/when it will be fixed other than to wait for it to fix itself. This is a concerning issue once the users are hitting the report hard after the official release.

We are not done investigating but it is hard to do that now since it is working fine. I will post up if I ever do find the exact issue.

John

Hey John, ask your DBAs if they updated the RUNSTATS? (Does Oracle have this?)

T

Stats are gathered nightly.

We do still see this code executing randomly. I have a case open but we aren't getting anywhere quick.

Apparently if I take the SQL generated out and just create a SQL pass-through it works find all of the time.

If I run the report with the Table File command it sometimes decides to run the SQL correctly and sometimes not.

Tony A mentioned using IF RECORDLIMT EQ 1 with the TRACEs on, another way is the just turn the retrieval off

SET XRETRIEVAL = OFF

will still produce the Trace.

For simpler requests, you can use TABLEF from wihtin WebFOCUS, just PRINT the fields you need, then hold them. TABLEF will turn off the FOCSORT. But of course, if you can get the sort to be passed, that's a better option.

I know this is a dumb comment, but make sure in the Windows Option, under the Reporting Tab that someone didn't set the maximum number of records to be retrieved. That would be a cruel joke to play on someone!

I've blown FOCSORT out and found that a few records on each page were being dropped in an older release.

Good Luck!

I just wanted to update this thread since we may have found the issue. I will open a new thread discussing it in more detail and link it here.

We were given a "fix" to try that involved setting the FETCHSIZE to 1 instead of 100. I do not understand what this parameter does but it doesn't make sense to me (therefore the new thread)

My New Thread on FETCHSIZE

Just wanted to follow up on this ticket to close the loop. We finally found that Oracle had a few issues with some of their hidden parameters. It was causing the same SQL to be handled differently and that was making the reports fail intermittently.

Workaround or Resolution (Per Oracle)
Setting _table_lookup_prefetch_size=0 disables the problematic table prefetch feature.
It is a static parameter, in order to set it the instance must be recycled.
Disabling the feature has a performance impact but because of the dynamic component it is not possible to predict it's scale.


Bug 5509707 INTERMITTENT WRONG RESULTS WHEN SQL IS USING PREFETCH
Solves most of the generic issues.


@ UNPUBLISHED: Bug 4148420 Wrong results / dump in eva* routines with table prefetch enabled


Bug 5403855 WRONG RESULT WITH SQL_TRACE = TRUE
Specific when SQL_TRACE/10046 is turned on.


Bug 5068565 WRONG RESULTS WITH PREFETCH ENABLED (DEFAULT)

Specific to the use of pipelined table functions

THE REPORTS ARE NOW UP AND RUNNING.