In another thread we had a fex that executed with random results. Apprently when we included SQL SQLORA SET FETCHSIZE 1 in the fex it corrected the problem and it has been working since. The size was set to either 20 or 100 before.

The quick digging I did says that this can be set from 1-5000 but doesn't mention that I will get random results with some of the numbers in between. I'll be honest, this one sounds like some kind of BUG in a loop where fetchsize doesn't completely read the recorset from Oracle but I'd like others opinions and knowledge on this one.

Here is my old post about the issue in more detail Random Fex Execution Thread

- john

When an RDB returns a resultset it has to be communicated back to the wf Server. FETCHSIZE is the number of records it should send at a time.

You would think that with a reliable line the bigger FETCHSIZE, the better since there will be less packaging relative to the data. In practice however the additional packaging makes hardly any difference.

More important would be the TCP/IP packets size which can be set in TNSNAMES.ORA I think and this should be set as high as possible and probably defaults to as high as possible.

In short, you only need to mess around with comms parameter if you have a bad line since making packets smaller means its less catastrophic if you lose a packet and have to retransmit.

Thanks for that info. I still am not sure why if it is to retrieve 419 rows from the database and the fetchsize is set to 100 why it wouldn't do something like this:

step1. get 100 rows (319 remain)
step2. get 100 rows (219 remain)
step3. get 100 rows (119 remain)
step4. get 100 rows (019 remain)
step5. get 100 rows and bomb out because there wasn't a full payload

It doesn't lose data it just decides not to read all the rows whether in groups of 100 or some other value. All we know is that if you make it = 1 it reads all the rows.

If I wrote that code I would start betting that I had some kind of unhandled exception (even if there is one bad row for some reason the whole fetch should bomb out right?).

Jon

I take it that the missing record problem was cured by setting FETCHSIZE to 1 in EDASPROF.

This sounds to me like some sort of memory lock/memory time out type double buffer type problem.

Normally there are 2 buffers wf empties one ant then it waits for the other to be filled by Oracle and the 2 processes switch.

If the synchronisation is out or the 2 buffers share or do not have sufficient address space
it does not take Bill Gates to see there are going to be problems.

Setting to the lower value means there are less memory requirements and no half measures about the buffer.

This is a rough guess about what's going on. I've been using Oracle 9.2 with fetchsize=5000 for 6 months now with no problems.

Is your version of Oracle a comparitively early one, I might venture?

Jon

It would be great if you could put a bit more info about your s/w in your signature in the personal area of the forum and then switch it on.

Thanks for the suggestion. I'll have to add in more site information into my signature.

We are running WebFocus 5.3 (with some version of a hotfix) and Oracle 9.2 (to one of the later patches) and now some Oracle 10G instances as well.

If what you stated above was correct about the buffers would that be an Oracle issue (memory/sizing) or somthing on the WebFocus server (memory/sizing)?

Just testing to see if the new sig worked.

I reckon it would be a wf thing. Possibly it is making the assumption that Oracle will fill any buffer quicker than wf will empty it or some assumption rather than using a proper lock.

Interestingly Oracle operates a fetch scheme from its cursor in that it does not have to calculate the entire result set before it returns results to a user.

eg A.if you are joining 2 tables and not doing a sort once you have joined the first 100 or so records you might as well give the result to the user and then go on calculating the rest.
Sim to TABLEF

eg B. If you are sorting the entire table you will have to complete the entire sort before returning the first fetch to your user.
Sim to TABLE

Now I think that Oracle is quite sophisticated in that if the user asks the Optimizer to try and get him the first N rows it will try and do something inbetween A and B.

For example to find the highest 100 records you would use a different faster technique to find them than to sort the whole file, but repeated invocation of this technique 100 records at a time would be very slow compared to sorting the file.

I am guessing this is what is happening in your example in that you have optimization set to return the first n records.

So instead of passing the data to wf from a calcuated cursor which means a quick fetch, when wf asks for the next 100 records it is having to calculate the cursor and this delay is unacceptable to wf and it overwrites instead of waiting till Oracle responds.

It is a bit of wild speculation but I would be interested to what your Oracle dba has set the optimization setting to and whether changing to a calculate full result set before returning any results would cure the problem.

As it turns out Oracle has a known issue in 9i at least where the same SQL can behave differntly because of a hidden parameter related to PREFETCH.

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.

Just wanted to close this out in case anyone else found the same issue. We pulled our hair out for about a month an a half trying to figure this one out.