Query Optimization using Aggregates

[Former Member]

Hi Folks

We have a performance tunning activity in which we need to optimize the query running time using aaggregates. The aggregates are already present in the system. I need to find to find out what more aggregates can i create or delete the existing non-usable aggregate.

I have taken the list of the long running query and there multiprovider from the t-code st03.

Can you please guide me how to procede forward in this regard.

Regards

Nitin

[KodandaPani_KV · Accepted solution]

Hi,

you need to find the trace in ST05.

first activate the trace  -> then execute the query at RSRT -> after query exection compeleted deactive the trace -> now read the select statement.

based on the select statement you will decide the which object required for the aggegates ot not.

plase read the document for more detail.

Thanks,

Phani.

[Former Member · Accepted solution]

Hi pani,

thanks for reply, apart from running the SQL trace, is there any other way to identify the object to be used for aggregate.

Nitin....

[chanda · Accepted solution]

Hi Nitin,

Firstly is check if your query is hitting the aggregates or not based on your input .

Even though aggregates exist, it is not necessary that they being hit.

If you do not know how to check it below is the procedure.

RSRT--> provide query name --> click on Execute & debug--> Select Display   Aggregate Found ( Under AGGS). then execute.

You will see which aggregate on which cube have been hit.

If for any cube it does not hit, you can take screen shot and create an aggregate based on the characteristics shown .

Hope it helps,

Regards,

Sudhir

[Former Member · Accepted solution]

Hi nitin,

First make sure that the Aggregates are being used during Query Execution...Aggegrates may be present but they may not be used...there wud nt be any hit to it.

This can be checked using RSRT + Execute +Debug with Display Aggregates.

rgds

SVU

[Former Member · Accepted solution]

Hi pani,

I ran the SQL trace, but output came with SQL program and /BIC/E* table. Is it possible that the available aggregates are not being hit at all.

Regards

Nitin

[Former Member · Accepted solution]

Hi Chandra,

Thanks for ur reply, as i went to execute+debug and selected the display aggregate and a list appear ..

I can see a list of objects that are present under maintain aggregate of infocube and some other object is also displayed.

do i need to build aggregate on these "other objects".

Also Can you please elaborate on your statment:

If for any cube it does not hit, you can take screen shot and create an aggregate based on the characteristics shown ."

[KodandaPani_KV]

Hi,

you need to check the aggregates being used or not in RSRT + Execute +Debug

and check the aggregates panle - selec the cube -> right clcik -> select the maintain aggregates.

now you will find the column as USED - then it says last used aggregates for the cube.

Thanks,

Phani.