Index rebuild check criteria

[Former Member]

Hi All,

I was going through some tips of tuning Oracle performance for specific long running reports , though overall performance of database is fine and hit ratios are also perfect , some of reports taking very long time , i thought of analysing the same at index level.

while going through some notes i found we need to rebuild index depending on output generated and values of deleted leaf nodes can be easily identified by running the <b>IDL.SQL script</b> , but can anybody let me know where can i find this particular script , <b>also am i on right path to fix long running reports</b> , as our ABAP developers already tried their level best to fine tune the same.

I just want to know what else we can check for those long running reports apart from index rebuild option.

Best Regards,

AjitR

[Former Member · Accepted solution]

Dear Ajju,

One of the measures to fix long running abap reports is to look at the activity. Wether its doing a sequential read, insert etc.

Also, apart from fixing the problem at index level, (creatnig or editing indexes), try to edit the ABAP report "where" clauses to suit already existing indexes. Remember, the key field entries in the SQL statement should be in the same sequence as the index.

Primary rule, to avoid long running sql statements in abap, is to avoid using "select *" statements.

Also, regularly update the Optimizer Statistics for all tables, that change frequently in a production environment.

Hope this helps.

Regards

Shantanu

[Former Member]

Apart from rebuild option for long running reports these poins may be helpfull

look at execution path

Access method , Use Hints

Reorganization of Objects , Latest statistics with Good Stats

Adjust optimizer parameters ,Optimize your instance

Remove unnecessary large-table full-table scans,Verify optimal index usage

These are all the options might help

Vinod

[Former Member]

Hello Ajju,

the best entry point in terms of index fragmentation should be SAP note 771929. As a quick first step you can use the CBO statistics based approach to check for fragmented indexes that is described in this note.

Kind regards

Martin

[Former Member]

statistics are running on alternate day basis and the Database part is also fine.

I have identified Expensive stmts also and they perform sequential read , no inserts.

whats the way to go ahead...i think to find index fragmentation will give better result and program tuning already done from ABAP side.

Because this index rebuild gibe better result for BW reports so thought of trying in R3 now.

[Former Member]

Hello Ajju,

please read my above answer carefully. I didn't say that something is wrong with your CBO statistics. I said that you should determine index fragmentation based on the CBO statistics approach described in note 771929.

Regards

Martin

[Former Member]

Thanks i will work on it

[Former Member]

Points rewarded

[Former Member]

Hello ajju,

I hope you have also checked for 'Expensive SQL satements' for long running reports.

Regards,

Ammey Kesarkar