table "mseg" problem

[Former Member]

I did a system copy using backup/restore from my prd system to a new

machine. now this new machine is our prd system.

we encouter a problem that a function module which select some date from"mseg" table will run 8mins while in our old system this function moduleonly run 20seconds. In sm50, I can see this function module stay in

"sequential reading from mseg" for several mins , after 8 or 10 mins, itcan run successfully and show the data . Is there some problem with

"mseg" table? is it necessary to reorg this table?

thanks a lot.

our environment:aix 5.3 oracle9.2 ecc5 kernek247

Edited by: victor on Jun 16, 2009 9:16 AM

[Former Member]

the sql statement is:

SELECT

T_00 . MBLNR MBLNR , T_00 . MATNR MATNR , T_00 . MENGE MENGE , T_00 . CHARG CHARG ,

T_03 . CONTAINER CONTAINER , T_00 . EBELN EBELN

FROM

sapprd.MSEG T_00 , sapprd.MKPF T_01 , sapprd.MARA T_02 , sapprd.ZMESGT02 T_03

WHERE

( T_01 . MANDT = '800' AND T_00 . MBLNR = T_01 . MBLNR ) AND ( T_02 . MANDT = '800' AND T_00 .

MATNR = T_02 . MATNR ) AND ( T_03 . MANDT = '800' AND T_00 . CHARG = T_03 . BATCH ) AND T_00 .

MANDT = '800' AND T_00 . BWART = '105' AND T_00 . WERKS = '3000' AND T_02 . MATKL = '002' AND T_01 .

CPUDT >= '20090612' AND T_01 . CPUDT <= '20090614';

I try to run this sql in sqlplus command line, it only uses 2 seconds.

but this function module need more than 10 mins.

and this function module is very simple , it only contains a sql statement.

[Former Member]

I have no ideas and I did all the things:reorg table, rebuild index , update statistics,

but this function module still needs much time.

Could you give me some advices?

[JPReyes]

Sounds like a missing index and as said before you can run a trace via ST05 and find the expensive statement, also make sure that the statistics are up to date.

Regards

Juan

[Former Member]

thanks for your help.

I checked index, they are the same as my qas system and my old prd system, the index status is active.

and the log of update statistics seems all right .

[Former Member]

You need to activate SQL trace(ST05) (also read about st12) and check. It's also may be the Oracle bug, are you install all interim pathces for your Oracle release? Oracle 9 is old version, why you not use 10 ?

Read notes 619188, 805934 , 766349.

Regards