Skip to Content
0
Former Member
May 13, 2011 at 11:50 AM

MaxDB 7.7.7.17 performance issue with inner join

28 Views

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