HELP! performance tuning for large table in SAP CRM 5.0(only for SAPers)

[Will Sun]

大家好，

 

以下内容，不明白的朋友请略过，打扰啦……

 

现在碰到一个比较棘手的问题，CRM系统里面有一张表（主要业务表）CRMD_ORDERADM_H

，从legacy系统倒过来的数据有接近2000万件， 与其相关的Partner关联表更是达到了上亿条数据。

 

创建了一个索引在创建时间上 ，但是用这个INDEX去检索的时候，结果0条：最短的时候5秒，最长10分钟左右。

 

我猜想是不是由于其他很重的任务也在执行，占用了系统资源（CPU，ORACLE线程等等），但是我觉得硬件应该不是问题。大概是哪个地方配置不好……  实在是找不出原因啊。

 

客户硬是要解决性能问题，于是我们建了一个addon表，去作为index表，把需要的字段全部从各个表抽出来，放在同一张表里平铺。 创建service order的时候 实时去更新这张表。 写了一个程序把legacy数据全部导入这张表。

这种做法不知道是不是最优的解决方案，现在数据还没导完，最终结果如何，还很难说。

 

顺便问1个问题：range scan 和 skip scan有什么区别？（ST05监视的时候，同一个SQL语句，在不同的系统里面，表现出了不同的结果）

 

现在的系统现状是，ORACLE数据库，SOLARIS操作系统。

16个CPU， 50G左右内存，SAP CRM跟ORACLE放在同一个服务器上，网络问题应该是没有。

最大WPS数量 Dialog process 30多个， background process 16个，如果没记错的话。

 

谁有好的建议或者猜想，欢迎指导啊！

 

--
-----------Will

[Will Sun]

thank you for your reply hansen!

 

I checked this table before , but it seems that theres no field like created_at changed_at and some enhancemented field.

 

Is there any other BASIS approches to improve this performance?
 

I will check that table again and find some notes for the master table.
 

On 10/14/07, Chen, Hansen <hanse...@bearingpoint.com> wrote:

You can try to use the CRM_order_index table where the standard CRM index is build. The SAP crm standard query(report package) is based on this table.

If that table is also slow, then you got the problem. Because all the standard SAP 1O query is based on this table. Try to check whether the is any performance notes for this table.

Thanks,
Hansen Chen

Senior Consultant, SAP CRM
BearingPoint
Management & Technology Consultants
C + 1 408 693 1748
www.bearingpoint.com < http://www.bearingpoint.com/>

________________________________

From: Will Sun [mailto:hyt...@gmail.com]
Sent: Sat 10/13/2007 8:21
To: h...@abeam.com; beidi ma; Chai, Lisa; Chen, Hansen; Du ZiRong; Du, Chris; Fu, Qiang; Gao, Sophia; gui yiran; Guo, Gary; Hu Koi; Jia, Paul; Jiang, Potato; Jin, May; Liu, Flora; Lu, Eveline; Lu, Luis; Ma, Jesse; Wan, Helen; Wang, Aaron; Wang, Frank; webber....@accenture.com; XiongWei; Xu, Ark; yanya...@accenture.com; Yao Gordon; ZHAO FENG, Andy; carme...@cardinal.com; chenxi...@baosight.com; che...@baosight.com; che...@shenzhong.net ; eimi...@hotmail.com; gogo9...@gmail.com; huji...@shanda.com.cn; huzh...@snda.com.cn; luya...@gmail.com; yan....@163.com; zhangx...@baosight.com; ??; Ark Xu; Carol Tan; chen, tingting; Cindy Gao (Worksoft); Crystal Quan; GuJian; hlee; Jessie; ldx; Lu, Rachel; Lynn; Ren Yanyan; Ricess; Rose Yang; Roy; Sheena (Xin) Zhao (xinzh); shi, yu; SophieHao; sun, liang; sxy; Tracy Fang; viola.lu 13816914230; Wang YingYing; Wang, Carmen; weixiaowei0; Winnie; Xia Summer; Xiang Wang; Xiao Na; Xu, Chang; Yang, Bonnie; yangbinbin; Zhang, Amy (PDG); ??; ???; ??; ??; ECNU-SEI
Subject: HELP! performance tuning for large table in SAP CRM 5.0(only for SAPers)


???,

????,?????????,???......

?????????????,CRM????????(?????)CRMD_ORDERADM_H
,?legacy???????????2000??, ?????Partner??????????????

????????????? ,?????INDEX??????,??0?:?????5?,??10?????

???????????????????,???????(CPU,ORACLE????),?????????????????????????......  ??????????

???????????,????????addon?,???index?,???????????????,?????????? ??service order??? ????????? ???????legacy??????????
?????????????????,????????,??????,?????

???1???:range scan ? skip scan??????(ST05?????,???SQL??,????????,?????????)

????????,ORACLE???,SOLARIS?????
16?CPU, 50G????,SAP CRM?ORACLE?????????,??????????
??WPS?? Dialog process 30??, background process 16?,????????

??????????,?????!



--
-----------Will
***************************************************************************************************
The information in this email is confidential and may be legally privileged. Access to this email by anyone other than the intended addressee is unauthorized. If you are not the intended recipient of this message, any review, disclosure, copying, distribution, retention, or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. If you are not the intended recipient, please reply to or forward a copy of this message to the sender and delete the message, any attachments, and any copies thereof from your system.
***************************************************************************************************

--
-----------Will