MaxDB 7.7.7.17 performance issue with inner join

[Former Member]

Hi all,

I do have a select using an inner join, which runs very fast on the test system but causes 100 % CPU on productive system showing a "ExclLock Yield" in task manager for nearly an hour (than I stopped it).

I know about issues with 7.6 to be solved with setting OPTIMIZE_OPERATOR_JOIN_COSTFUNC to NO. Does anybody know such issues in MaxDB 7.7?

By the way, an explain join shows

STRATEGY;SCHEMANAME;TABLENAME;MAX ADDNL. ROWS;MULTIPLIER;REVERSE MULTIPLIER;ADDITIONAL PAGES;ADDITIONAL ROWS;ACCUMULATED_COSTS; ;;T_00;3537;1;1;147.375;3537;393 JOIN VIA MORE THAN ONE COLUMN;;T_01;26150;6;1;2122.2;21222;5085.078044874681 JOIN VIA MORE THAN ONE COLUMN;;T_02;656;1;160.2630285327539;2358;21222;7446.300385552313

on development system and

STRATEGY;SCHEMANAME;TABLENAME;MAX ADDNL. ROWS;MULTIPLIER;REVERSE MULTIPLIER;ADDITIONAL PAGES;ADDITIONAL ROWS;ACCUMULATED_COSTS; ;;T_01;30078;1;1;2148.428571428572;30078;3290 JOIN VIA MORE THAN ONE COLUMN;;T_02;720;1;236.6645336119364;2506.5;30078;5799.656337326651 JOIN VIA MORE THAN ONE COLUMN;;T_00;5016;5405;5.892063077475595;3538.511214861732;31846.60093375559;9965.167552188383

on productive system.

The parameters are mostly the same on both systems, optimizer statistics are up to date and data is nearly the same amount...

Does anbody have an idea what could be the problem?

Thank you

Christian

[janlars_goedtke · Accepted solution]

Hi,

try to disable EnableQueryRewrite (can be done online) and have a look at Note 1166914....

Jan

[Former Member · Accepted solution]

Hi Jan,

already tried this did, not help!

Thanks for your suggestion.

Best regards

Christian

[Former Member]

Hi,

workaround was found using hind keyaccess.

Best regards

Christian