Query performance on ODS

[Former Member]

Hi mates.

Could any one tell me ,How to improve the Query performance on <b>ODS</b>.I understand Aggregates are used to improve query performance on <b>Infocube.</b>

Thank u

hari

[Former Member · Accepted solution]

Indexes. Create indexes on the fields that are being used. Fields in the index should be hte fields on which data is being restricted.

You can use SQL trace for a detailed analysis fo what index would get used. Check the execution path of the sql statement on the ODS table.

[Former Member · Accepted solution]

Yeap,

Thats one way of going about the performance of the ODS. The other way is to use DATA CLASSES. This basically contains the "MAINTAIN DB STORAGE PARAMETERS". Doing this, the entire ODS is going to be stored in a separate tablespace. If the tablespace is small when compared to the previous one and partitioning of the tables is taken care off, then it will certainly improve the query reporting performance.

Hope this makes sense!!!

Regards,

GPK.

[Former Member · Accepted solution]

Indexs can help - assuming the queries are sufficently selective ( return a small % of rows in the ODS )enough. Indexes won't help a query that should run a full table scan because it reads more a few percent of the rows.

Full table scans are the fastest access method if more than a small % of rows is to be retrieved.

Seldom / Unused indexes just waste disk space and slow load times.

ST04 can show you the SQL queries that have been run recently. You really need to understand what the queries look like and what people are filtering on before you go off and start building indexes.

Table partitioning (depending on the DB you use - they don't all support partitioning) can be a very big help by restricting the number of rows teh query must even consider (e.g. table is partitioned on 0FISCPER - and user filters on 001/2005 - then the query will only look at the 001/2005 partition)

Putting the ODS in a separate tablespace might help, but that really depends so much on your storage configuration.

[Former Member]

hi,

try note 0565725

Regards,

Andrzej

[Former Member]

One more thing: keep the DB Stats upto date for the CBO to determine the most optimal access path.

DB20 - enter ods active data table name - refresh & if stats are not upto date - create stats

Good Practice is to run periodic BRCONNECT job after all the data loads are complete.

Kumar Gudiseva