We are migrating from informix level 1 to oracle level 2.
The results are rather good (15% less disk space, queries much faster), but some sessions slower.

Specially: tccom1101, tccom2101, tdsls4101 and tdpur4101. Also the first-set/last-set actions are slow. (about 15 seconds).
The response time was 3 minutes before changing optimizer_max_permutations to 1500.
The time is so bad at the very first execution. Did you make the same experience and what were the actions to solve the problem ?

Hi Richard,

The optimizer_max_permutation will prevent the Oracle optimizer from going into a 'loop' when looking for the best query execution path. The default value for max. number of permutation is 80,000!

Also make sure that you have set up the optimizer_mode to CHOOSE and that you have up-to-date histogram data (statistics) on all your Baan tables. If no statistics exist, Oracle will switch to rule-based execution pathes which is bad. Only cost-based optimization (CBO) should be used.

Are you talking about computing statistics for columns?

Yes

I don't believe histograms will have any effect on Oracle's processing of Baan SQL because Baan uses bind variables. When the CBO processes a query containing bind variables, it must use default selectivities. If anyone disagrees, please explain.

I would be interested to know if you do notice an improvement after computing columns stats - please let me know.

Thanks,
Brandon

thats correct.

which database version you are using ?
we have 8.1.7 and i have set the optimizer mode to "first_rows"
(because Baan set a sql-hint for use first_rows).
the optimizer_max_permutation is set to 800.
But the best performance hint is to activate parallel query an update for tables and indexes, so i have now responstime for starting sessions for 3 seconds and skip to last row for 5 seconds.

It's not a matter of 'or-or' but rather 'and-and'. First of all, I think every one would agree that CBO is the best way to drive any SQL query for Baan. The way the CBO will work depends on several things:

parameter OPTIMIZER_MODE
session OPTIMIZER_GOAL
SQL hints

The order of taking precedance is from bottom to top. Thus a hardcoded SQL hint will overrule the standard if OPTIMIZER_MODE if they wouldn't agree.

Using OPTIMIZER_MODE=CHOOSE does not exclude the FIRST_ROW hints (hints take precedence) but makes sure that any queries where the SQL hint is incomplete, invalid or missing will use a proper execution plan IF statistics are available. A good example of invalidhints is any statement with an ORDER BY clause. In those cases the FIRST_ROWS hint is ignored.

As to the bind variables: the Oracle CBO does have problems with optimizing queries when bind variables are used in 'like' and range constructs but from experience I can say that failing to update the statistics on a weekly basis (or more frequent) *does* cause a performance hit.

I am not an export in Oracle matters so if someone can give me better insights, I will stand corrected!

I agree that computing statistics regularly (weekly) is a must for the CBO to work optimally, but you do not need to compute statistics on columns. Column statistics = histograms. These are ignored by the CBO when using bind variables and default selectivities are used instead.

This is all you need to run:
EXECUTE dbms_utility.analyze_schema('BAAN','COMPUTE')


You do NOT need to run:
analyze table ttxyz123100 compute statistics for columns . . .

That's my mistake, the statistics we calculate are not for the columns, I assumed they were the same thing. Another thing learned today :)