CJI3 poor performance after 10G upgrade

[Former Member]

Hello,

I am fairly new to SAP and am still learning my way around the transactions.

After we upgraded to 10G we noticed that the CJI3 report never completes -- it does not crash, it just does not complete, with the selection criteria given. The very same selection criteria is used in another instance with the same data and the report finishes in less than a minute. the difference between these two systems is that the long processing system is distributed -- two applications and a database each on separate Unix boxes.

I have applied the paramater changes identified in SAP Note 830576, run oradict_stats & system_stats and applied Optimizer mere patches 6488148 & 6455795.

Edited by: Juliann Smith on Sep 11, 2008 8:31 PM

[Former Member]

Hello,

I am fairly new to SAP and am still learning my way around the transactions.

I am looking for a transaction that will give process time for the report CJI3.

After we upgraded to 10G we noticed that the CJI3 report never completes -- it does not crash, it just does not complete, with the selection criteria given. The very same selection criteria used in another instance with the same data and the report finishes in less than a minute. The difference between these two systems is that the long processing system is distributed -- two SAP applications and a database each on separate Unix boxes.

As directed by SAP I have applied the parameter changes identified in SAP Note 830576, run oradict_stats & system_stats and applied Optimizer mere patches 6488148 & 6455795. The patches & stats have been applied to the long running instance, although the parameters have not been changed in that production environment. I need to have some "proof" that these settings will help before I take Production down to make parm changes.

This brings me to my basic question. What transaction can I run before and after I run CJI3 to prove to myself the parm setting changes do have a positive effect. I have used ST05 to turn on SQL Trace, but the trace has thousands of explain plans to wade through. Most look the same.

What transaction will show me how long a process ran? I want to start with the parameters set as they are in production to get a baseline statistic, then make the changes for an "after" statistic.

thank you,

[fidel_vales]

Hi,

Lets assume that is a database problem.

Then the ST05 is the place to go.

As you mention that there are a lot of statements, then you have to take a look at the SQL summary.

Do not remember exactly the menu now, I think it is in the first one.

On the summary you will see one entry per statement sorted by duration.

Then you start analyzing from the first one.

Take a look at this [blog |https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/7205] [original link is broken] [original link is broken] [original link is broken]; for "starting" help with ST05

[Former Member]

Hallo,

did you consider note 838725 and the disabilitation of the automatic statitiscs gatherer?

You can also launch the transaction and have a look on the oracle session through the detailed analysis of st04.

From SM50 on the server which is working the transaction you have to note down the PID and in ST04 (detail analysis -> oracle sessions) search (use the binocular) the PID. There you can find the SQL statement in use, and you can check the explain plan. That will narrow down the explain statement to check.

Hope this helps.

Ivan Bazzi.