Hello,

We have been plagued with an issue for a long time now and neither Infor or our Oracle support people can give me a resolution.

Every now and then when running the tdilc4201m000 - Generate Outbound Advice - session the process is taking 2 hours instead of the usual 1 minute.

There doesn't appear to be a pattern when the issue arises. It can be fine for weeks and then all of a sudden it will start to run slowly.

I have used the Baan call profiler debug tool which details query times and the sql statements (attached) to identify what's taking so long to run. I have then reorganised these tables but to no avail.

The same query is seen by our Oracle support people but they haven't really come up with any suggestions.

Can anybody make some suggestions as to what else I can look at.

We are running Oracle 10.2.0.4 in RHEL 5.

Thanks

PJ

I seriously hate call_graph profile, let's not use it since you've got better tools available to you.

First - i assume you've set the parameters in solution #166049 from Infor. The oracle 10 ones there pretty much specifically address the type of problem you're seeing. Specifically:
_optim_peek_user_binds = FALSE
_unnest_subquery = FALSE

Assuming you've tried that and it's still an issue, what you may be seeing is a bug in oracle that involves it's automatic statistics gathering messing up and sometimes allowing you to get a horrible execution plan.

In Oracle 10.2.0.4 you can use AWRs to collect information on queries across a span of time. [ See metalink note: 748642.1 for how to do this ]

What you can do is get an execution plan of the query when it runs well and when it doesn't run well, you can compare that. I guarantee that they are different - show that to oracle, ask them why. Maybe they've got a patch for it.

But if they don't then you need to think about generating statistics on those tables and then "locking" them so that oracle doesn't mess them up.

Dave

Thanks Dave, your reply was most helpful. I'll pass this information on to our Oracle support people.

I had also re-opened a support case with Infor from some months back and they've managed to find a solution that addresses performance issues with tdilc4201m000 (232583)

Cheers

Dave,

have you had any experience with the _optim_peek_user_binds=FALSE parameter?

Will it have any adverse effect on sessions that are not giving performance issues?

Thanks

Philip

I'd say pretty confidently that it won't affect "out of the box" baan.

but custom sessions.. i can't be too sure.

To be fair though, that basically just sets the default behavior back to oracle 8i
and most baan code runs just fine in that version.

Dave