Optimizer get different strategy on production and backup instan

[Former Member]

Hi

These days I'm getting a strange problem, a sql statement[1] that in production executes fast, now consumes 100% of CPU and becomes slow, in production the explain[2] show a bad choice that causes the slow thing, but checking backup  of database and that explain[3] show the right and the good choice for that statement stay fast, I already issued UPDATE STAT in all tables but the problem persist, there is a way force a strategy? or what can be the problem?

MaxDB version 7.6.6.10 64bit running on Linux CentOS

[1] Statement

SELECT
     MF.DATA DATA,
     MF.OID OID_MF,
     PC.OID OID_CONTA,
     PC.CONTA CONTA,
     PC.DESCRICAO DESCR_CONTA,
     PCPAI.OID OID_CONTA_PAI,
     PCPAI.CONTA CONTA_PAI,
     PCPAI.DESCRICAO DESCR_CONTA_PAI,
    MF.VALOR SOMA 
FROM MOVIMENTOFINANCEIRO MF,
     TIPOMOVIMENTO TM,
     PLANOCONTAS PC,
     PLANOCONTAS PCPAI 
WHERE MF.MY_SACADO = 'AAAADluq' 
AND MF.TIPO IN 
    ('MB',
    'CE'
    ) 
AND MF.MY_TIPO_MOVIMENTO = TM.OID 
AND TM.MY_CONTA_CREDITO = PC.OID 
AND PC.CONTA LIKE '3303%' 
AND MF.DATA BETWEEN '2012-02-01 00:00:00.000000' 
AND '2012-02-29 23:59:59.999000' 
AND PCPAI.OID = PC.MY_CONTA_PAI

[2] production explain

OWNER	TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
	PC	IPLANOCONTAS	RANGE CONDITION FOR INDEX	7
		CONTA	(USED INDEX COLUMN)
	PCPAI	OID	JOIN VIA KEY COLUMN	7
	MF	IMOVIMENTOFINANCEIROX5	JOIN VIA RANGE OF MULTIPLE INDEXED COL.	36452
			ADDNL. QUALIFICATION ON INDEX
		MY_SACADO	(USED INDEX COLUMN)
	TM	OID	JOIN VIA KEY COLUMN	49
			NO TEMPORARY RESULTS CREATED
	JDBC_CURSOR_23		RESULT IS COPIED  , COSTVALUE IS	11

[3] backup explain

OWNER	TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
	MF	IMOVIMENTOFINANCEIROX1	IN CONDITION FOR INDEX	34734
		MY_SACADO	(USED INDEX COLUMN)
		TIPO	(USED INDEX COLUMN)
		DATA	(USED INDEX COLUMN)
	TM	OID	JOIN VIA KEY COLUMN	49
	PC	OID	JOIN VIA KEY COLUMN	7
			TABLE HASHED
	PCPAI	OID	JOIN VIA KEY COLUMN	7
			NO TEMPORARY RESULTS CREATED
	JDBC_CURSOR_19		RESULT IS COPIED  , COSTVALUE IS	6

[urs_schuerer · Accepted solution]

Of course there is a way to force strategy with Optimizer Hints but since it works fine in backup please check all those funny OPTIMIZE_ params first for identical settings like:

OPTIMIZE_AGGREGATION
OPTIMIZE_FETCH_REVERSE
OPTIMIZE_STAR_JOIN
OPTIMIZE_JOIN_ONEPHASE
OPTIMIZE_JOIN_OUTER
[...]

If those are identical the table sample size for the statistic update might be another hint but only if it works well with a smaller and fails with a bigger database size.

Good Luck,
Urs

[Former Member · Accepted solution]

Hi Urs,

Both servers have same parameters:

OPTIM_MAX_MERGE                       500
OPTIM_INV_ONLY                        YES
OPTIM_CACHE                           NO
OPTIM_JOIN_FETCH                      0
TRACE_OPTIMIZE                        NO
OPTIMIZE_AGGREGATION                  YES
OPTIMIZE_FETCH_REVERSE                YES
OPTIMIZE_STAR_JOIN                    YES
OPTIMIZE_JOIN_ONEPHASE                YES
OPTIMIZE_JOIN_OUTER                   YES
OPTIMIZE_MIN_MAX                      YES
OPTIMIZE_FIRST_ROWS                   YES
OPTIMIZE_OPERATOR_JOIN                YES
OPTIMIZE_JOIN_HASHTABLE               YES
OPTIMIZE_JOIN_HASH_MINIMAL_RATIO      1
OPTIMIZE_OPERATOR_JOIN_COSTFUNC       YES
OPTIMIZE_JOIN_PARALLEL_MINSIZE        1000000
OPTIMIZE_JOIN_PARALLEL_SERVERS        0
OPTIMIZE_JOIN_OPERATOR_SORT           YES
OPTIMIZE_QUAL_ON_INDEX                YES
OPTIMIZE_QUERYREWRITE                 OPERATOR

Then I tried your tip about hints, with /*+ORDERED+/ works on production and we got same explain as backup, then I tried a better strategy that is using another index, but dont works, with INDEXACCESS hint, allways get default optimization, i used the hint /*+INDEXACCESS(IMOVIMENTOFINANCEIROX7)*/ any mistake? or INDEXACCESS hint dont works for my version? any idea how to check why production dont get the better strategy without using hints?