Optimizer get different strategy on production and backup instan

39 Views

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