Basis and others: Note 1013912 - FAQ: Oracle BW performance

Summary

Symptom

1. What does this note cover?

2. Which table types are used in the BW environment?

3. Which temporary objects are relevant to BW performance?

4. How does the index design appear in BW?

5. What is a STAR transformation?

6. When is a STAR transformation not used, despite a STAR_TRANSFORMATION hint?

7. When is a FACT hint but not a STAR_TRANSFORMATION hint generated?

8. What do I have to bear in mind with regard to BW performance?

9. How can I optimize SQL statements in BW?

10. What utilities are available for analyzing BW queries? Other terms

FAQ, frequently asked questions

Solution

1. What does this note cover?

              This note covers database-specific performance in the SAP Business Warehouse environment. The note may therefore be considered as a BW-specific supplement to Note 618868 (FAQ: Oracle Performance).

              The note does not cover subjects such as tuning measures in BW (for example, creating aggregates or compressing requests).

2. Which table types are used in the BW environment?

              The main performance-related tables in the BW environment are:

F Fact tables: /BI0/F<cube>

E Fact tables: /BI0/E<cube>

Fact view (BW 3.5 and lower; UNION ALL view using E and F fact tables): /BI0/V<cube>

Dimension tables: /BI0/D<cube>

SID tables: /BI0/S<characteristic>

SID tables (navigation attribute, time-independent): /BI0/X<characteristic>

SID tables (navigation attribute, time-dependent): /BI0/Y<characteristic>

              In addition to the /BI0 tables delivered by SAP, you also have customer-specific /BIC tables with a naming convention that is otherwise identical.

3. Which temporary objects are relevant to BW performance?

              The following temporary objects are particularly relevant to performance:

Tables with query interim results: /BI0/01...

Tables with hierarchy interim results: /BI0/02...

Tables with query interim results (reused): /BI0/06... (with BW 3.x, this can be controlled using the parameter DBIF_EQSID_THRES, Note 744555)

Query views: /BI0/03...

Materialized partial results from complex queries: /BI0/0P...

           A /BI0/0P table always refers to a particular dimension (and its SID and attribute tables).

           A /BI0/0P table is a special type of /BI0/01 table that is used because of the problems described in Note 811354.

           By default, these tables are created as of 50 tables that are involved in the join; the creation can be controlled using the parameter SPLIT_QUERY_TABL_THRES (for BW 3.x or lower) or SPLT_QRY_TAB_THR (for BW 7.x or higher), or alternatively SPLIT_DATAMART_TABL_THRES (for BW 3.x or lower) or SPLT_DM_TAB_THR (for BW 7.x or higher) (Note 514907). Up to BW 3.5, all dimensions are taken into account. As of BW 7.0 only dimensions with restricting conditions are prematerialized. If this causes problems, you can use the CALLER_ID = D (data mart) to use the earlier behavior. However, in this case, the data mart-specific split parameter takes effect.

              Refer to Note 449891 for more information.

4. How does the index design appear in BW?

              The index design appears as follows, depending on the table type:

F-Fact tables

Normal cubes: Bitmap indexes (non-unique) on each dimension column (KEY_<cube><suffix>, indexes 010, 020, ...) for query support; exception: B tree indexes (non-unique) for "High Cardinality" dimension columns

Transactional cubes: B tree indexes (non-unique) on each dimension column (KEY_<cube><suffix>, indexes 010, 020, ...), B tree type required for better support of parallel read and write accesses

E-Fact tables:

Bitmap indexes (non-unique) on each dimension column (KEY_<cube><suffix>, indexes 010, 020, ...) for query support

B tree index (non-unique) of all dimension columns (index P) to support compression; exception: B tree indexes (non-unique) for "High Cardinality" dimension columns

Dimension tables

B tree index (unique) of DIMID column (index 0)

B tree index (non-unique) of all SID columns (index 010)

BI 7.00 and higher: B tree Index (non-unique) for each individual column (indexes 020, 030, and so on)

SID tables

B tree index (unique) on characteristic column (/BIC/<characteristic>, index 0)

B tree index (unique) on SID column (index 001)

SID tables (navigation attribute)

B tree index (unique) on SID and OBJVERS column (index 0)

Optional: Other indexes on characteristic columns (Notes 402469, 383325)

              For details on the index design of the fact tables, see Note 217397.

5. What is a STAR transformation?

              Many queries use the STAR transformation when accessing InfoCubes. You can thus determine the relevant data records in the fact table relatively quickly from conditions on individual dimension tables using bitmap operations. A STAR transformation appears as follows in the execution plan:

TABLE ACCESS BY LOCAL INDEX ROWID (Fact Table)
  BITMAP CONVERSION TO ROWIDS
    BITMAP AND
      BITMAP MERGE
        BITMAP KEY ITERATION
          BUFFER SORT
            TABLE ACCESS FULL    (Dimension Table)
          BITMAP INDEX RANGE SCAN (Fact Table Index)
      BITMAP MERGE
        BITMAP KEY ITERATION
          BUFFER SORT
            TABLE ACCESS FULL    (Dimension Table)
          BITMAP INDEX RANGE SCAN (Fact Table Index)
      ...

              The individual steps are:

Selection conditions-> Dimension table -> Suitable dimension data records

Suitable dimension data records-> Bitmap index for fact table-> Bitmap with suitable fact table data records (BITMAP KEY ITERATION)

Bitmaps with suitable fact table data records for all dimensions taken into account as part of the STAR transformation -> AND operation-> Bitmap with fact table data records that are suitable for all dimension conditions (BITMAP AND)

BITMAP CONVERSION TO ROWIDS

The suitable data records are read from the fact table (TABLE ACCESS BY LOCAL INDEX ROWID)

              Oracle automatically determines the dimensions used as part of a STAR transformation. During this process, the dimensions with the highest expected selectivity are used to minimize the number of hits on the fact table.

              Once the actual STAR transformation is complete, other joins are executed in the tables referenced in the query.

              In addition to handling the STAR transformation, Oracle determines the generation of temporary tables with larger dimensions (greater than or equal to _TEMP_TRAN_BLOCK_THRESHOLD = 100 blocks). Use of this type of table appears as follows in the access plan:

TEMP TABLE TRANSFORMATION
  RECURSIVE EXECUTION SYS_LE_2_0
    INSERT STATEMENT
      LOAD AS SELECT
        ...

              Within the actual access operation, these temporary tables are referenced with generated indicators such as SYS_TEMP_0FD9E2BA3_30438DC1.

6. When is a STAR transformation not used, despite a STAR_TRANSFORMATION hint?

              A STAR transformation is not carried out in the following situations, despite explicit use of a STAR_TRANSFORMATION hint:

No bitmap indexes on the fact table

           Bitmap indexes on the fact table are an essential prerequisite for a STAR transformation. B-tree indexes may be created instead of bitmap indexes under the following circumstances:

Definition of "High Cardinality" dimensions

                    If dimensions in BW are flagged with the "High Cardinality" indicator, the related index is created as a B-tree index.

Transactional InfoCubes

                    To avoid potential deadlocks when you have parallel updates for transactional InfoCubes, the indexes of the F-Fact tables for transactional cubes are generally created as B-tree indexes. Bitmap indexes are only used on the E-Fact tables. A transactional cube is flagged with TRANSACT = 'X' in the table RSDCUBE.

Use of Oracle events 38088 and/or 38089

           If you use the events 38088 and/or 38089, the CBO decides on a non-STAR transformation despite the STAR_TRANSFORMATION hint, if its costs are significantly lower than the cost of the STAR transformation or under a defined threshold. For more details, see Note 999126.

Use of CURSOR_SHARING = FORCE / SIMILAR

           If you set the parameter CURSOR_SHARING to FORCE or SIMILAR, Oracle replaces literals with bind variables. However, you can only execute STAR transformations in connection with literals, which is why a STAR transformation is no longer possible. For further information, see Note 912389.

UNUSABLE bitmap indexes

           If bitmap indexes are flagged as UNUSABLE, you can no longer use them for the STAR transformation. Check that there are no UNUSABLE indexes, according to Note 618868.

Using fact views (BW 3.5 and lower)

           Up to BW 3. 5, you use a view at various points (for example, read transactions using RSDRI_INFOPROV_READ, data mart, transaction SEM9) that joins the E and F fact tables together using UNION ALL. This fact view follows the naming convention /BIC/V*, which means that a STAR_TRANSFORMATION is not possible.

           In transaction SE11, the definition of this view is not always fully displayed, so it may appear as though it is a project view of just one fact table. However, the UNION ALL definition is fully visible at Oracle level.

           You can control the use of fact views according to Note 561961 (USE_FACTVIEW entry in RSADMIN).

           As of BW 7. 00, the problem no longer exists, as fact views are no longer used.

7. When is a FACT hint but not a STAR_TRANSFORMATION hint generated?

              Using Note 837841, you can ensure that the STAR_TRANSFORMATION hint is only then generated when there are restrictive conditions on at least three dimensions. This behavior is standard for newer releases (as of NetWeaver 2004S and higher).

              As of Release 7. 00 (BW Support Package 20), no more database hints are generated in BW (Note 1247763).

8. What do I have to bear in mind with regard to BW performance?

              Of course, the general checks described in Note 618868 are as useful for BW as for all other Oracle systems in terms of database performance. However, the following points are particularly relevant for BW:

Oracle parameter setting

Check the Oracle parameters as described in Note 632556 (9i), Note 830576 (10g), or Note 1431798 (11g).

SAP parameter setting

Set the SAP parameters as described in the recommendations in Note 192658 or 1044441 (Release 7.x).

Patches

You should use the latest possible patch set (see the relevant recommendation from Note 618868).

Ensure that the relevant Optimizer merge fixes (see Note 354080) have been implemented.

Check (using a notes search, for example) whether there are other performance-relevant fixes in the BW environment. Implement these if available.

Statistics (Note 588668)

           Ensure that the following points are satisfied in relation to CBO statistics:

Statistics must be generated on the table, indexes and all columns.

In the case of partitioned objects, global and local statistics must be created at partition level.

Histogram statistics must also be created for all objects created in Note 428212 (see Note 797629).

According to Note 555030, you can deactivate the generation of statistics initiated in BW in certain appropriate cases (BI 3.50 and lower).

As of BW 7.00, the heuristics from BRCONNECT are added subsequently to BW so that the same statistics are generated in both cases. If you have problems in connection with statistics, see Notes 998319, 1010233 and 1016927.

When you use BI process chains, we recommend that you include a step for the statistics creation. Note the following: New statistics are created only if the change threshold value (STATS_CHANGE_THRESHOLD) for BRCONNECT is reached. If InfoCube tables are changed, this may be problematic in some cases because even minor changes have a strong impact on the CBO behavior. In this case, you may consider taking measures: For example, you can lower STATS_CHANGE_THRESHOLD, include critical tables with ACTIV=U (general statistics creation) in DBSTATC or directly call BRCONNECT using the appropriate force options (for example, "-f collect").

As of BW 7.00, histograms are created for all tables with BW naming convention, for example, for the ODS tables.

Ensure that no unnecessary 'ignore' indicators (ACTIV=I) are set for BW tables in DBSTATC (transaction DB21). To do so, you can use the report SAP_DBSTATC_CLEANUP from Note 129252.

Where possible, use BRCONNECT 7.20 (24) or higher because this prevents the creation of unnecessary global statistics for partitioned tables. Previously, unnecessary global statistics were created when a partition had no statistics and when no indexes were available.

           To check missing columns or histogram statistics, you can use the following query (<table_name> should be replaced in both locations with the name of the table you want to check):

COLUMN PARTITION FORMAT A25
COLUMN COLUMN_NAME FORMAT A20
SELECT
  DECODE(LVL, 1, PARTITION, '  ' || PARTITION) PARTITION,
  DECODE(COL, LAG(COL, 1) OVER (ORDER BY COL, LVL, PARTITION), ' ',
    COL) COLUMN_NAME,
  NUM_DISTINCT,
  NUM_BUCKETS,
  LAST_ANALYZED,
  DECODE(LAST_ANALYZED, NULL, 'No statistics',
    DECODE(SIGN(NUM_DISTINCT - 1), 1,
      DECODE(NUM_BUCKETS, 1, 'No histograms', ''), '')) "COMMENT"
FROM
(SELECT
    1 LVL,
    COLUMN_NAME COL,
    'TABLE' PARTITION,
    NUM_DISTINCT,
    NUM_BUCKETS,
    TO_CHAR(LAST_ANALYZED, 'dd.mm.yyyy hh24:mi:ss') LAST_ANALYZED
  FROM
    DBA_TAB_COLUMNS
  WHERE
    TABLE_NAME = '<table_name>'
  UNION
  (SELECT
    2 LVL,
    COLUMN_NAME COL,
    PARTITION_NAME,
    NUM_DISTINCT,
    NUM_BUCKETS,
    TO_CHAR(LAST_ANALYZED, 'dd.mm.yyyy hh24:mi:ss') LAST_ANALYZED
  FROM
    DBA_PART_COL_STATISTICS
  WHERE
    TABLE_NAME = '<table_name>'
)
ORDER BY
  COL,
  LVL,
  PARTITION;

ANALYZE TABLE vs. DBMS_STATS

           Note the following points when selecting ANALYZE TABLE or DBMS_STATS to generate statistics:

In general, we recommend that you use DBMS_STATS to generate statistics in the BW environment. This applies both to BW-initiated statistics and to BRCONNECT-statistics. If you have problems (for example, with the performance of statistics generation), you can switch to ANALYZE with Oracle versions 9i or lower.

In BW, you can use RSADMIN (ORA_DBMS_STATS parameter) to control the use of DBMS_STATS or ANALYZE (Note 351163). As of BW 7.x, you can no longer use ANALYZE.

As of Oracle 10g, BRCONNECT automatically generates statistics with DBMS_STATS. With Oracle versions 9i and lower, ANALYZE TABLE is used by default. According to Note 408532, you can switch to using DBMS_STATS.

If the runtime for generating statistics is too long, refer to the tips in Note 806554.

Bitmap indexes

STAR transformations can be executed only based on bitmap operations. For this, bitmap indexes must exist in dimension columns of the fact table. In exceptional cases (in particular in connection with transactional InfoCubes), B tree indexes can also be used via a B tree bitmap conversion. You can use the following command to determine the types of indexes created on a table:

SELECT INDEX_NAME, INDEX_TYPE FROM DBA_INDEXES
WHERE TABLE_NAME = '<fact_name>';

TRUNCATE operations

TRUNCATE commands may perform poorly, especially in Oracle 9i or lower (see Note 758989). You can improve performance by upgrading to Oracle 10g or higher, or by using DELETE in some cases (Note 840553).

Tablespace design

Since objects and partitions are frequently created and deleted in BW, and extents are thus allocated and deallocated, you should use Locally Managed Tablespaces (LMTS) in the BW environment wherever possible. In the case of Dictionary Managed Tablespaces, substantial waiting times may occur when you work with space transactions (Note 745639, section "TYPE = ST").

Parallel processing:

At some points, BW accesses an Oracle parallel query (that is, queries are processed in parallel on the database). The CPU-dependent DEFAULT degree of parallelism is used in this process (see Note 651060). It may be helpful to reduce the degree of parallelism (as described in Note 544521) to avoid overloading resources.

PGA and PSAPTEMP configuration

Since numerous hash, bitmap and sort operations are carried out in the BW environment especially, you must pay particular attention to the configuration of the PGA and PSAPTEMP tablespaces. These components are crucial factors in the performance of processing the operations described. You must therefore ensure that PGA_AGGREGATE_TARGET is set to a reasonable size and that PSAPTEMP is in a high-speed disk area. It may be useful to add up to 40 % of the memories available for Oracle to the PGA.

Partitioning

Make sure that the partitioning of the InfoCube tables makes sense. For general technical information about partitioning, see Note 722188. Refer to Note 590370, which contains information about how to reduce the number of partitions.

                    To determine the tables and indexes that have more than 500 partitions, you can use the following selections:

SELECT
  TABLE_NAME,
  PARTITIONING_TYPE,
  PARTITION_COUNT
FROM
  DBA_PART_TABLES
WHERE
  OWNER LIKE 'SAP%' AND
  PARTITION_COUNT > 500;

SELECT
  INDEX_NAME,
  PARTITIONING_TYPE,
  PARTITION_COUNT
FROM
  DBA_PART_INDEXES
WHERE
  OWNER LIKE 'SAP%' AND
  PARTITION_COUNT > 500;

If InfoCubes were originally only partitioned up to a certain month and all entries of later periods are written in the MAXVALUE partition, this may lead to problems. You can use the following SELECT to check whether MAXVALUE partitions with a significant number of records (for example, greater than 1.000.000) exist:

SELECT
  TABLE_NAME,
  NUM_ROWS
FROM
  DBA_TAB_PARTITIONS
WHERE
  PARTITION_NAME LIKE '%MAXVALUE%' AND
  NUM_ROWS > 1000000
ORDER BY
  NUM_ROWS DESC;

                    If this is the case, you may want to repartition the InfoCubes that are affected (refer to Note 1008833).

Distinct Keys

           Using indexes for a STAR transformation that have a large number of distinct keys may result in poor performance and a high CPU consumption due to complex bitmap operations.  Furthermore, it may take a long time to create bitmap indexes with a large number of distinct keys.

           You can use use the following command to check which bitmap indexes contain more than one million distinct keys:

SELECT
  INDEX_NAME,
  DISTINCT_KEYS
FROM
  DBA_INDEXES
WHERE
  INDEX_TYPE = 'BITMAP' AND
  DISTINCT_KEYS > 1000000
ORDER BY
  DISTINCT_KEYS DESC;

           For those indexes that contain more than one million distinct keys, you should check whether the BW definition of the associated dimension as "high cardinality" makes sense. As a result, the index would be created in B*TREE format.

Histograms on SID columns

           By default, the system creates histograms on the SID columns of SID tables. These columns do not have any multiple values, because the SID column values are unique and therefore there is equal distribution. At the same time, there are various reasons why the existing histograms may cause the CBO to calculate the resulting cardinalities incorrectly. To ensure that the histograms on SID columns cause a performance problem, you can delete the histograms as a test. You can use the following command to generate SQL statements. You can use these SQL statements to delete the histograms on the SID columns for all SID tables that are involved in an SQL statement:

SELECT
  DISTINCT(
  'ANALYZE TABLE "' || OBJECT_NAME ||
  '" COMPUTE STATISTICS FOR COLUMNS SID SIZE 1;') COMMAND
FROM
  V$SQL_PLAN
WHERE
  SQL_ID = '<sql_id>' AND
  OBJECT_NAME LIKE '/BI_/S%' AND
  OBJECT_NAME NOT LIKE '%~%';

           For <sql_id> , you must also set the SQL_ID of the SQL statement that is considered here. If the system does not return any data records (despite a correct SQL_ID), you must execute an identical query on DBA_HIST_SQL_PLAN instead of V$SQL_PLAN as a test (this concerns Oracle 10g and above).

           If this measure corrects the problem, the statistics of the relevant SID columns can be frozen in accordance with Note 1374807 (BRCONNECT 7.10 (25) or higher) or may be protected by means of ACTIV=I in DBSTATC from being overwritten by BRCONNECT (Note 106047, BRCONNECT 7.10 (24) or lower).

           If this does not result in any improvement, you can create the standard statistics again:

brconnect -u / -c -f stats -t <sid_table> -f collect

Number range buffering of DIMIDs and SIDs

           If you load data with value combinations that are not yet contained in the relevant dimensions and SID tables, you must create a new entry in these tables. A new DIMID or SID value from the number range table is used for this. If the relevant number ranges are not buffered, this leads to a COMMIT for each value that is used. If there is a large amount of new entries in DIMID or SID tables, this results in a very large amount of COMMITs on NRIV and this may lead to a high proportion of "log file sync" waits. In this case, if you buffer the DIMIDs and SIDs in accordance with Note 857998, this may improve the situation.

Configuration of the input help (F4) for InfoProviders

           In the InfoObject maintenance (transaction RSD1), you can configure the behavior of the input help (F4) for InfoProviders on the "Business Explorer" tab page. The setting "Only Posted Values for Navigation" may lead to processing-intensive database accesses. Therefore, if performance problems occur, check whether you can use one of the alternative settings.

9. How can I optimize SQL statements in BW?

              You can often recognize SQL statements in BW by joins across a large number of tables. Even if the analysis is thus more extensive than in OLTP systems, the analysis steps are similar to the optimization of "normal" SQL statements. For more information, see Note 766349.

              You must also note the following particular features in the BW environment:

If you use a STAR transformation, you should check whether the CBO is actually using the most highly selective dimensions to execute the STAR transformation.

If Oracle decides to create temporary tables, the "TEMP TABLE TRANSFORMATION" section must be taken into account during analysis of the actual access.

If temporary objects from BW are also used in the SQL statement, you must ensure that the relevant information is still available for analysis. This often causes problems since the system automatically drops or reuses the temporary objects. You can use the following approaches for this:

For problems with queries, in transaction RSRT, you can select "Execute + Debug" and you can activate "Display Run Schedule". As a result, the execution of the query stops before every subquery and you can perform tests without the risk of losing temporary tables.

When you create an SQL trace, in many cases, you may receive the concrete instructions for temporary objects that precede the current BW access. By doing this, you can reproduce the accesses later.

As a result of dropping temporary objects, the related SQL statements are also invalidated. These no longer appear in the Oracle Shared Cursor Cache. in the BW environment, performing an analysis on processing-intensive SQL statements in the Shared Cursor Cache is only of limited use.

10. What utilities are available for analyzing BW queries?

              The following are some of the functions provided:

According to Note 378483, you can combine important information for a concrete query in transaction RSRT.

Using "Show SQL/BIA statement", you can graphically display the STAR schema of a query in transaction RSRT as of Release 7.00 (10) (Note 935815).

You can use transaction LISTSCHEMA to display the tables belonging to an InfoCube in hierarchical order.

Header Data


Release Status:	Released for Customer
Released on:	30.04.2012 11:37:19
Master Language:	German
Priority:	Recommendations/additional info
Category:	FAQ
Primary Component:	BC-DB-ORA Oracle
Secondary Components:	BW-SYS-DB-ORA BW ORACLE

Affected Releases

Release-Independent

Related Notes

Note 1013912 - FAQ: Oracle BW performance

1 comment: